I am familiar with recordsets in DAO, but I would like to start using ADO. My first attempt is not good. Here is my code, which is coming straight out of an Access 2002 book with my questions.

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Set rst.ActiveConnection = CurrentProject.AccessConnection

The book says it should be "CurrentProject.ActiveConnection", but when I type in CurrentProject ActiveConnection does not come up as a choice. So I am not sure if my choice is causing the problems.

rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "Main Table"

When I try and run this I get a runtime error with the above line highlighted.
Invalid SQL statement; Expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'.

If Not rst.BOF And Not rst.EOF Then
    Response = MsgBox("Not in there", vbDefaultButton1, "yes")
    Exit Sub
Else
    rst.MoveFirst
    Do While rst.EOF <> True 'Continue if last find succeeded.
        If IsNull(CCNnum) = False Then
        Response = MsgBox("There is already a record with this CCN number", vbInformation, "CCN Found")
        End If
    Loop
End If

rst.Close
cnn.Close

Any help would be very much appreciated.

Scott

__________________
ScottP

Hi Scott,

"CurrentProject.ActiveConnection" is an errata in your book. It doesn’t exist in the Access object model.

CurrentProject.AccessConnection is fine but you should usually use simply CurrentProject.Connection with a jet (.mdb) database. CurrentProject.AccessConnection and CurrentProject.Connection return the same ADO connection in a .adp database (the connection used by Access for the currently open database). In a jet database, however, they return two different conncetions. CurrentProject.Connection allows you to use more ADO features in jet, like ADOX, and code that needs index support, like the recodset Seek method.

So I’d use:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = CurrentProject.Connection (or CurrentProject.AccessConnection)

rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Open "MainTable", , , , adCmdTable

Your error is caused by the white space character in your table name. The recordset Open method generates an internal SQL statement, which in your case would have a FROM clause of:

FROM Main Table

This is an invalid SQL FROM clause (no white space allowed). Using “MainTable” will generate a valid internal FROM clause (never use spaces in Access object names!). adCmdTable is optional. It tells the internally generated SQL statement to evaluate your command text as a table and return all of its columns. Specifying adCmdTable when your Source argument is a table (as opposed to say a SQL statement) simply improves performance.

HTH,

Bob

Thanks Bob, your help is much appreciated. I will fix my code on Monday.

Scott

ScottP

Bob, the code works great. Thanks again for your help.

Scott

You're welcome Scott. Enjoy learning ADO!

Bob