Open method with ADO 1

[lifter10]

I am new to using ADO for data access and I'm having some problems. I'm using VBA in Access 2002 to try and access some data that I have stored in a table. I'm trying to build a connection to the database that I have currently open and I'm not having any luck. Here is my code.

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=T:\Investments\Mgmtnli\Stat\2004 statbv\LE Recon\Recon Files\2Q\TestTCP.mdb;"

rst.Open "SELECT * FROM DP3_IMP", conn, adOpenForwardOnly, adLockReadOnly

The error comes with the conn.Open statement telling me that the file is already open and it's not allowed to be opened or locked. I could use DAO, which I'm familiar with, but I want to learn ADO. Also, I really don't want to have a string for where my file is located. I would rather just put in something equivalent to CurrentDB as the Data Source.

Thanks in advance,

Chris

 

[RoyVidar]

Are you accessing the current database, i e linked or native tables within the same database where the code is run?

If so, currentproject.connection can be used.

For more connection strings, you may have a look at ADO connection strings

Roy-Vidar

 

[lifter10]

That is correct, I am trying to access tables from within the same database where the code is run. I've tried using currentproject.connection, but get the same error. It really shouldn't be this difficult to open a table in the current database I'm working in. Anymore suggestions?

Thanks,

Chris

 

[lifter10]

In other words I put in the following code into a private function in a module:

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset

conn.Open CurrentProject.Connection

The error says:

Run-time error - '2147467259 (80004005)':

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

 

[RoyVidar]

No more suggestions, just exemplifying this one:

[tt]set conn = currentproject.connection
set rs=new adodb.recordset
rs.open strsql, conn, adopenkeyset, adlockoptimistic, adcmdtext[/tt]

or (readonly forwardonly)

[tt]set rs=conn.execute(strSql,,adcmdtext)[/tt]

- assuming your sql is assigned to the variable strSql

Roy-Vidar

 

[lifter10]

Alright, that worked! Thanks Roy. Do you know the reason it wouldn't work when I used the New keyword in my declaration? In Microsoft's documentation (Porting DAO Code to ADO with the Microsoft Jet Provider), it shows it being done the way I first tried it.

Thanks for the help,

Chris

 

[RoyVidar]

It is my impression that using the New keyword in a declaration, makes an implicit instantiation, making the app have to resolve the object type each time it hits it, whilst when "using two lines" it is explicit, and the app does not have to resolve it per each time (I'm probably not the best in the world to explain that, but I think it's roughly correct).

But it should normally work, perhaps just a bit slower (and I wouldn't try it with late binding). I think the error relates more to that you are working/designing in the same database as you try to create and "external" connection to - 1 - you have unsaved design changes or 2 you've opened the database in exclusive mode (probably both;-))

You're welcome!

Roy-Vidar

 

[cmmrfrds]

To answer the last question.

The New on the declaration if fine.
Dim rst As New ADODB.Recordset
But this connection is already open.
conn.Open CurrentProject.Connection
So, as Roy showed, this is the syntax needed.
set conn = currentproject.connection