×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using SQL from access and VB6
3

Using SQL from access and VB6

Using SQL from access and VB6

(OP)
hello,

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,

thanx
bye

RE: Using SQL from access and VB6

3
This example uses ADO, so you have to set a reference to MS ADO 2.0 or MS ADO 2.1.

Private Sub Form_Load()
Dim adoConn As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim strSQL As String

'Create a connection object.
Set adoConn = New ADODB.Connection

'Connect to the GenTCA DSN (Data Source Name), where UserID and Password are blank.
adoConn.Open "MyDSN", "", ""

'Select the FirstName, LastName, Address, Trans_Num and SectionNum from the Consumer table.
strSQL = "SELECT CON_NUMBER, FIRSTNAME, LASTNAME, ADDRESS, TRANS_NUM, SECTIONNUM FROM CONSUMER ORDER BY LASTNAME"

'Create a recordset object.
Set adoRecordset = New ADODB.Recordset

'Create the selection set so we can now go read the values.
adoRecordset.Open strSQL, adoConn, adOpenDynamic, adLockOptimistic

'While we're not at the end of the recordset, keep reading in data from the table.
Do While Not adoRecordset.EOF

'Print each field from the table that we selected in the SQL Statement.
With adoRecordset
debug.print !CON_NUMBER & ""
debug.print !FIRSTNAME & ""
debug.print !LASTNAME & ""
debug.print !ADDRESS & ""
debug.print !TRANS_NUM & ""
debug.print !SECTIONNUM & ""
End With

'Move to the next record in the recordset.
adoRecordset.MoveNext
Loop

'Close the objects.
adoRecordset.Close
adoConn.Close

'Release the memory our objects are using.
Set adoRecordset = Nothing
Set adoConn = Nothing
End Sub

Steve Meier
sdmeier@jcn1.com

RE: Using SQL from access and VB6

You have to call your SQL statement from a string and open a recordset.

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....

Sub Chart9_Issued()

Dim rstChart9_Issued As Recordset

Set dbs = CurrentDb

strSQL = "SELECT qryExportView.I_PROBLEM_NO, "
strSQL = strSQL + "qryExportView.RSE_ID, qryExportView.ISSUE_DATE "
strSQL = strSQL + "FROM qryExportView "
strSQL = strSQL + "WHERE (((qryExportView.RSE_ID)<> ""ERROR2"") "
strSQL = strSQL + "AND ((qryExportView.ISSUE_DATE)>= #" + strBegin + "# "
strSQL = strSQL + "AND (qryExportView.ISSUE_DATE) < #" + strEnd + "#"
strSQL = strSQL + "));"

Set rstChart9_Issued = dbs.OpenRecordset(strSQL)

With rstChart9_Issued
If .BOF = False Then
.MoveLast
lngChart9_Issued = rstChart9_Issued.RecordCount
Else: lngChart9_Issued = 0
End If

'Debug.Print lngChart9_Issued
rstChart9_Issued.Close
End With

End Sub

RE: Using SQL from access and VB6

are yoy working with VB5 - VB6 ? the different is the jet3.51 or jet4.0 !

To mar :you can better change de + to &

Eric

Eric De Decker
vbg.be@vbgroup.nl
Visual Basic Center

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close