Using SQL from access and VB6 3

[stallion]

hello,<br>
<br>
i need some help, how would you put an Access SQL statement into visual basic code?, i have tried cut and paste butit wont work for some reason,<br>
<br>
thanx<br>
bye<br>

 

[Snaggs]

This example uses ADO, so you have to set a reference to MS ADO 2.0 or MS ADO 2.1.<br>
<br>
Private Sub Form_Load()<br>
Dim adoConn As ADODB.Connection<br>
Dim adoRecordset As ADODB.Recordset<br>
Dim strSQL As String<br>
<br>
'Create a connection object.<br>
Set adoConn = New ADODB.Connection<br>
<br>
'Connect to the GenTCA DSN (Data Source Name), where UserID and Password are blank.<br>
adoConn.Open &quot;MyDSN&quot;, &quot;&quot;, &quot;&quot;<br>
<br>
'Select the FirstName, LastName, Address, Trans_Num and SectionNum from the Consumer table.<br>
strSQL = &quot;SELECT CON_NUMBER, FIRSTNAME, LASTNAME, ADDRESS, TRANS_NUM, SECTIONNUM FROM CONSUMER ORDER BY LASTNAME&quot;<br>
<br>
'Create a recordset object.<br>
Set adoRecordset = New ADODB.Recordset<br>
<br>
'Create the selection set so we can now go read the values.<br>
adoRecordset.Open strSQL, adoConn, adOpenDynamic, adLockOptimistic<br>
<br>
'While we're not at the end of the recordset, keep reading in data from the table.<br>
Do While Not adoRecordset.EOF<br>
<br>
'Print each field from the table that we selected in the SQL Statement.<br>
With adoRecordset<br>
debug.print !CON_NUMBER & &quot;&quot;<br>
debug.print !FIRSTNAME & &quot;&quot;<br>
debug.print !LASTNAME & &quot;&quot;<br>
debug.print !ADDRESS & &quot;&quot;<br>
debug.print !TRANS_NUM & &quot;&quot;<br>
debug.print !SECTIONNUM & &quot;&quot;<br>
End With<br>
<br>
'Move to the next record in the recordset.<br>
adoRecordset.MoveNext<br>
Loop<br>
<br>
'Close the objects.<br>
adoRecordset.Close<br>
adoConn.Close<br>
<br>
'Release the memory our objects are using.<br>
Set adoRecordset = Nothing<br>
Set adoConn = Nothing<br>
End Sub <p>Steve Meier<br><a href=mailto:sdmeier@jcn1.com>sdmeier@jcn1.com</a><br><a href= > </a><br>

 

[mar]

You have to call your SQL statement from a string and open a recordset.<br>
<br>
Here is a piece of code where I ran a SQL query and returned the number of resulting records. Of course, you will have to substitute your own SQL and you can use a continuation character at the end of lines instead of concatenating strings....<br>
<br>
Sub Chart9_Issued()<br>
<br>
Dim rstChart9_Issued As Recordset<br>
<br>
Set dbs = CurrentDb<br>
<br>
strSQL = &quot;SELECT qryExportView.I_PROBLEM_NO, &quot;<br>
strSQL = strSQL + &quot;qryExportView.RSE_ID, qryExportView.ISSUE_DATE &quot;<br>
strSQL = strSQL + &quot;FROM qryExportView &quot;<br>
strSQL = strSQL + &quot;WHERE (((qryExportView.RSE_ID)&lt;&gt; &quot;&quot;ERROR2&quot;&quot &quot;<br>
strSQL = strSQL + &quot;AND ((qryExportView.ISSUE_DATE)&gt;= #&quot; + strBegin + &quot;# &quot;<br>
strSQL = strSQL + &quot;AND (qryExportView.ISSUE_DATE) &lt; #&quot; + strEnd + &quot;#&quot;<br>
strSQL = strSQL + &quot);&quot;<br>
<br>
Set rstChart9_Issued = dbs.OpenRecordset(strSQL)<br>
<br>
With rstChart9_Issued<br>
If .BOF = False Then<br>
.MoveLast<br>
lngChart9_Issued = rstChart9_Issued.RecordCount<br>
Else: lngChart9_Issued = 0<br>
End If<br>
<br>
'Debug.Print lngChart9_Issued<br>
rstChart9_Issued.Close<br>
End With<br>
<br>
End Sub

 

[edderic]

are yoy working with VB5 - VB6 ? the different is the jet3.51 or jet4.0 !<br>
<br>
To mar :you can better change de + to & <br>
<br>
Eric<br>
<p>Eric De Decker<br><a href=mailto:vbg.be@vbgroup.nl>vbg.be@vbgroup.nl</a><br><a href=

http://members.tripod.lycos.nl/VisualBasicCenter/>

Visual Basic Center</a><br>