Newbie questions on ADODB connection

[Frederick Chow]

Hi all,

I am trying to learn Access VBA by myself, though I have some background on
Excel VBA.

I am trying to run the following program, typed directly from a book:

*************************************************

Sub TestRecordCount()

Dim cnCh5 As ADODB.Connection
Dim rsContacts As ADODB.Recordset
Dim strConnection As String

'specify the connection string for connecting to the database
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\Ch5CodeExamples.mdb;"

'create a new connection instance and open it using the connection string
Set cnCh5 = New ADODB.Connection
cnCh5.Open strConnection

'create a new instance of a recordset
Set rsContacts = New ADODB.Recordset
'set various properties of the recordset
With rsContacts
.CursorType = adOpenStatic
'open the recordset based on tblContacts table using the existing
connection
.Open "tblContacts", cnCh5
End With

'print the number of records to the immediate window
Debug.Print "The total number of records is " & rsContacts.RecordCount

'close the database connection
cnCh5.Close

'set the recordset and connection to nothing
Set rsContacts = Nothing
Set cnCh5 = Nothing
End Sub

*************************************************************

Everything is fine until I try to comment out the last two statements, i.e.
"Set objectvariable = nothing". Because I thought that, as local object
variables, they should have been discarded after the procedure call.

Then when I tried to rerun the program, the following error messages
occurred:

"The database has been place in a state by user "Admin" on machine "XXX"
that prevents it from being opened or locked".

Even though subsequently I uncommented the "Set objectvariable = nothing"
will not get rid of that message. The only thing I can do is to manually
close the mdb file and then re-open it.

The next thing I tried was to change the CursorType to "adOpenStatic". When
I re-run the procedure that curious message still occur and I have to close
and open the mdb file in order to get rid of it.

So what does the error message I encountered really means? If I encountered
the error, how can I rectify it other than close and reopen the mdb file?
Please advise.

Frederick Chow
Hong Kong.

 

[Alex Dybenko]

Try to close recordset:

rsContacts.Close

and then close connection
cnCh5.Close