Using ADO from within access

[Pete Davis]

From a piece of sample VBA code in Access, I've seen that with DAO, you can
do something like:

Dim db As DAO.Database
db = CurrentDb

Is there an equivalent for ADO? I know ADO doesn't have a database object.

Basically, I want to use the current database for my work and I need to use
ADO.

Thanks.

Pete

 

[Gerald Stanley]

I always use ADO with Access. One good thing is that the
Connection object is predefined for you. In A2003, it can
be found as CurrentProject.Connection. If you have an
earlier version, check this first and if it is not here it
will be in the CurrentDb object.

So I just create a new ADO Command Object, set its
ActiveConnection property to this predefined connection and
initialise the CommandText property with the SQL statement
I want to use.

If the SQL is a SELECT query then I set a ADO Recordset
Object to the return from the Execute method.

Hope That Helps
Gerald Stanley MCSD

 

[Peter Hoyle]

Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

Set cnn = CurrentProject.Connection
strSQL = "SELECT * FROM tblMyTable"
rs.Open strSQL, cnn
rs.MoveFirst

etc.

Cheers,
Peter

 

[Pete Davis]

Thank you both very much. That was a great help.

Pete

--
http://www.petedavis.net

Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

Set cnn = CurrentProject.Connection
strSQL = "SELECT * FROM tblMyTable"
rs.Open strSQL, cnn
rs.MoveFirst

etc.

Cheers,
Peter