CurrentDb in ADP

[Guest]

I have to convert a DbApplication from Access97 to Access 2003 ADP.
The problem is: In Access97 exists the Statements

Dim db As Database
Set db = CurrentDb()

How can I get an object of type Database from CurrentProject or is there
only the way as described in the thread 'ADP equivalent for CurrentDB'?

The CurrentProject.Connection delivers only a string, not a database-object.

Many thanks for replys
Erhard

 

[Brendan Reynolds]

CurrentDb returns a reference to the current Jet database. An ADP doesn't
use Jet, so in an ADP there isn't anything for CurrentDb to point to.

What you should use in place of CurrentDb depends on what you want to do,
but for most of the things you would have done with CurrentDb,
CurrentProject.Connection is it.

CurrentProject.Connection does not return a string, it returns an object of
type ADODB.Connection, though this object 'knows' how to represent itself as
a string.

To execute a SQL statement ...

In an MDB using DAO ...

CurrentDb.Execute "SELECT * FROM SomeTable"

In an ADP using ADO ...

CurrentProject.Connection.Execute "SELECT * FROM SomeTable"

To open a recordset ...

In an MDB using DAO ...

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM SomeTable")

In an ADP using ADO ...

rst.ActiveConnection = CurrentProject.Connection
rst.Source = "SELECT * FROM SomeTable"
rst.Open

 

[Guest]

Thanks Brendan, this is it.

Erhard

CurrentDb returns a reference to the current Jet database. An ADP doesn't
use Jet, so in an ADP there isn't anything for CurrentDb to point to.

What you should use in place of CurrentDb depends on what you want to do,
but for most of the things you would have done with CurrentDb,
CurrentProject.Connection is it.

CurrentProject.Connection does not return a string, it returns an object of
type ADODB.Connection, though this object 'knows' how to represent itself as
a string.

To execute a SQL statement ...

In an MDB using DAO ...

CurrentDb.Execute "SELECT * FROM SomeTable"

In an ADP using ADO ...

CurrentProject.Connection.Execute "SELECT * FROM SomeTable"

To open a recordset ...

In an MDB using DAO ...

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM SomeTable")

In an ADP using ADO ...

rst.ActiveConnection = CurrentProject.Connection
rst.Source = "SELECT * FROM SomeTable"
rst.Open

 

[Robert Morley]

Just a quick note: Brendan has fallen victim to one of the most common
typos and/or little-known facts of ADO in terms of opening a recordset.

In an ADP using ADO ...

rst.ActiveConnection = CurrentProject.Connection
rst.Source = "SELECT * FROM SomeTable"
rst.Open

If you do it this way, ADO will open a separate connection object with the
same properties as the current connection. In essence, it logs into SQL
Server twice (or 10 times if you have 10 different recordsets, etc.)

In order to use the same connection, you need to use the "Set" command when
assigning the connection:

Set rst.ActiveConnection = CurrentProject.Connection
rst.Source = "SELECT * FROM SomeTable"
rst.Open

If I remember correctly, this isn't necessary if you're doing the one-liner
style of opening a recordset, as the connection will properly be evaluated
as a reference...but you might want to double-check on that.

rst.Open "SELECT * FROM SomeTable", CurrentProject.Connection [, ...]



Rob

 

[Brendan Reynolds]

Thanks Robert,

Do you happen to know, is this a general ADO issue or is it specific to
ADPs, e.g. does the same apply when using CurrentProject.Connection in an
MDB?

--
Brendan Reynolds

Just a quick note: Brendan has fallen victim to one of the most common
typos and/or little-known facts of ADO in terms of opening a recordset.

In an ADP using ADO ...

rst.ActiveConnection = CurrentProject.Connection
rst.Source = "SELECT * FROM SomeTable"
rst.Open

If you do it this way, ADO will open a separate connection object with the
same properties as the current connection. In essence, it logs into SQL
Server twice (or 10 times if you have 10 different recordsets, etc.)

In order to use the same connection, you need to use the "Set" command
when assigning the connection:

Set rst.ActiveConnection = CurrentProject.Connection
rst.Source = "SELECT * FROM SomeTable"
rst.Open

If I remember correctly, this isn't necessary if you're doing the
one-liner style of opening a recordset, as the connection will properly be
evaluated as a reference...but you might want to double-check on that.

rst.Open "SELECT * FROM SomeTable", CurrentProject.Connection [, ...]



Rob

 

[Robert Morley]

It applies pretty much anywhere that you use any connection in ADO (though I
think they changed how it works in ADO.NET).



Rob

Thanks Robert,

Do you happen to know, is this a general ADO issue or is it specific to
ADPs, e.g. does the same apply when using CurrentProject.Connection in an
MDB?

--
Brendan Reynolds

Just a quick note: Brendan has fallen victim to one of the most common
typos and/or little-known facts of ADO in terms of opening a recordset.

In an ADP using ADO ...

rst.ActiveConnection = CurrentProject.Connection
rst.Source = "SELECT * FROM SomeTable"
rst.Open

If you do it this way, ADO will open a separate connection object with
the same properties as the current connection. In essence, it logs into
SQL Server twice (or 10 times if you have 10 different recordsets, etc.)

In order to use the same connection, you need to use the "Set" command
when assigning the connection:

Set rst.ActiveConnection = CurrentProject.Connection
rst.Source = "SELECT * FROM SomeTable"
rst.Open

If I remember correctly, this isn't necessary if you're doing the
one-liner style of opening a recordset, as the connection will properly
be evaluated as a reference...but you might want to double-check on that.

rst.Open "SELECT * FROM SomeTable", CurrentProject.Connection [, ...]



Rob

 

[Brendan Reynolds]

Thanks Robert.

--
Brendan Reynolds

It applies pretty much anywhere that you use any connection in ADO (though
I think they changed how it works in ADO.NET).



Rob

Thanks Robert,

Do you happen to know, is this a general ADO issue or is it specific to
ADPs, e.g. does the same apply when using CurrentProject.Connection in an
MDB?

--
Brendan Reynolds

Just a quick note: Brendan has fallen victim to one of the most common
typos and/or little-known facts of ADO in terms of opening a recordset.

In an ADP using ADO ...

rst.ActiveConnection = CurrentProject.Connection
rst.Source = "SELECT * FROM SomeTable"
rst.Open

If you do it this way, ADO will open a separate connection object with
the same properties as the current connection. In essence, it logs into
SQL Server twice (or 10 times if you have 10 different recordsets, etc.)

In order to use the same connection, you need to use the "Set" command
when assigning the connection:

Set rst.ActiveConnection = CurrentProject.Connection
rst.Source = "SELECT * FROM SomeTable"
rst.Open

If I remember correctly, this isn't necessary if you're doing the
one-liner style of opening a recordset, as the connection will properly
be evaluated as a reference...but you might want to double-check on
that.

rst.Open "SELECT * FROM SomeTable", CurrentProject.Connection [, ...]



Rob

 

[ilgar]

I have to convert a DbApplication from Access97 to Access 2003 ADP.
The problem is: In Access97 exists the Statements

Dim db As Database
Set db = CurrentDb()

How can I get an object of type Database from CurrentProject or is there
only the way as described in the thread 'ADP equivalent for CurrentDB'?

The CurrentProject.Connection delivers only a string, not a database-object.

Many thanks for replys
Erhard