Referencing a field from a table

[Michael Miller]

I have a 'control' table, tblMetricsControl, with one record with some fields
that I need to reference.

I tried ADO and got connected ok, but then I get an error that the 'database
has been placed in a state by admin that prevents it from being opened'.

My database is split and my front end is on my C: drive and my back end is
on the server, and other people -are- using that be from their fe's.

I get the error on the Open of the Connection (code below).
The code is located in the Form Activate and calls a procedure.

Private Function VerifyStarsProject()
On Error GoTo Err_Form_Close

Dim currConn As New ADODB.Connection
Dim rstXMLImport As New ADODB.Recordset
Dim currDB As Database

Set currDB = CurrentDb
Set currConn = New ADODB.Connection

With currConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & currDB.Name
.Open <ERRS here>
End With

Set rstXMLImport = New ADODB.Recordset

With rstXMLImport
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly

.Open "tblMetricsControl", currConn
.MoveFirst

... <additional code to read the field>
If .Fields("XMLInProgress").Value = True Then
... <etc.>

 

[Jonathan]

Hi Michael,

remove your connection object. Instead use the connection property of the
recordset to reference the connection property of the active project...

With rstXMLImport
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.ActiveConnection = CurrentProject.Connection
.Source = "tblMetricsControl"
.Open

 

[Michael Miller]

Thank you. I shall try that.

BTW, do I have to Close that connection and set it to nothing?
If so, is the syntax:

currentproject.connection.close
set currentproject.connection=nothing

type of thing and can I check the state as well?

 

[Michael Miller]

Yes. That works fine.
I hesitate to close the currentdb connection and set it, cuz I think that
affects my current session and will kill it.

Also, regarding ADO, when they give us at least 3 diff ways to open a
connection and rst, I never know which one I am supposed to use.

I am usually am working in the current db, but I also keep an outside log of
who logs in and out and I need to reference a different mdb. Then I can see
referencing everything from scratch.

 

[Jonathan]

Hi Michael,

you do not open or close the CurrentProject.Connection

if you are referencing another database you will have to create a connection
that requires openning and closing.

The method you use is a personal preference thing. I like to have multiple
properties listed so that it is easy to read.

If you are only working within MS Access and especially when only in the
current database you may find DAO an easier object library to use, plus it
has more functionality than ADO.

 

[Michael Miller]

Thanks again for the good info and advice, Jonathan.