CurrentDb in ADP

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Thanks Brendan, this is it.

Erhard

Brendan Reynolds said:
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
Click to expand...
 
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
Click to expand...

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
 
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

Robert Morley said:
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
Click to expand...

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
Click to expand...
 
It applies pretty much anywhere that you use any connection in ADO (though I
think they changed how it works in ADO.NET).



Rob

Brendan Reynolds said:
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

Robert Morley said:
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
Click to expand...

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
Click to expand...
Click to expand...
 
Thanks Robert.

--
Brendan Reynolds


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



Rob

Brendan Reynolds said:
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

Robert Morley said:
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
Click to expand...
Click to expand...
Click to expand...
 
Erhard said:
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
Click to expand...
 
Back
Top