Recordset and Access 2000 1

[colobill]

I have read examples related to ADO and connecting and opening an Access 2000 database. I have also read in other places about creating a record set object. I have put down a few lines of code but what I am not sure of is putting in and using a recordset object. In the following code what would I have to do to set the sql query to a recordset?

Dim ADOConn As New ADODB.Connection
Dim ADOCom As New ADODB.Command

ADOConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\ELPALS.mdb"

With ADOCom
.ActiveConnection = ADOConn
.CommandText = "SELECT * FROM TeacherID WHERE TID = " & "Text3.Text"
.Execute
End With
ADOConn.Close

 

[nicsin]

Use this:

Dim sql as String
Dim cntr1 as Integer
Dim cntr2 as Integer
Dim ADOConn As New ADODB.Connection
Dim ADORs As New ADODB.Recordset

ADOConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\ELPALS.mdb"

sql = "SELECT * FROM TeacherID WHERE TID = " & "Text3.Text"
ADORs.Open sql, ADOConn
ADOConn.Close


Now the recordset is populated with records from the database. You can access this records by looping through it as:

if not (ADORs.BOF and ADORs.EOF) then
ADORs.MoveFirst
cntr1 = 1
While not ADORs.EOF
for cntr2 = 0 to ADORs.Fields.Count - 1
msgBox "Record: " & cntr1 & " Field: " & cntr2
next
cntr1 = cntr1 + 1
ADORs.MoveNext
wend
else
msgBox "Recordset is empty"
end if

 

[TLowder]

Here's how i do it. I'm not sure what your asking exactly, but I use this all the time.

Dim SQL As String
Dim adoCon As ADODB.Connection
Dim adoRec As ADODB.Recordset

On Error GoTo MyError

Set adoCon = New ADODB.Connection

Set adoRec = New ADODB.Recordset

adocon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\whatever.mdb"

adocon.Open

SQL = "SELECT * FROM databasetable"

adoRec.Open SQL, adoCon, adOpenStatic, adLockOptimistic

DoEvents

adoRec.Close
adocon.Close

Set adoRec = Nothing
Set adoCon = Nothing

Exit Sub

 

[colobill]

thank you very very much. I think that is what I needed!

 

[nicsin]

glad i could help!

 

[colobill]

Thank you very much for your post too. One more question. With the query set to: SQL = "SELECT * FROM TeacherID WHERE TID =" & "Text3.Text" I get an error back that says "No value given for one or more required parameters." Can anyone tell me why I might be getting this error? The field TID is in the table TeacherID.

 

[nicsin]

What is TID? if it is a string then you need to wrap it with single quotes as:

SQL = "SELECT * FROM TeacherID WHERE TID ='" & "Text3.Text" & "'"

 

[nicsin]

Sorry. For string:

SQL = "SELECT * FROM TeacherID WHERE TID ='" & Text3.Text & "'"

For integer:

SQL = "SELECT * FROM TeacherID WHERE TID =" & Text3.Text & "'"

 

[TLowder]

Does teacherid accually exist as a table.
Also be sure to close the record set and connection like i posted otherwise you will have memory leaks

 

[nicsin]

Jesus...I can't get my brain to work today!

For integer:

SQL = "SELECT * FROM TeacherID WHERE TID =" & Text3.Text

 

[colobill]

thank you nicsin and TLowder. I have it up and running. I really appreiate your help. It is amazing that I can go into a VB 6 book that I own and NOT find this simple approach but yet go to a Forum like this and get the needed help within minutes.

 

[TLowder]

yep, I love this messageboard. When I make some money I will donate here.