|
Home > Archive > MS Access project with SQL Server > October 2005 > CurrentDb in ADP
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Erhard 2005-10-27, 8:32 am |
| 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 2005-10-27, 8:32 am |
| 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
--
Brendan Reynolds
"Erhard" <erhard@nomail.nomail> wrote in message
news:E56E721A-3EC9-4491-ADE1- 7532E38C5387@microso
ft.com...
>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
>
| |
| Erhard 2005-10-27, 8:32 am |
| Thanks Brendan, this is it.
Erhard
"Brendan Reynolds" wrote:
> 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
>
> --
> Brendan Reynolds
>
>
> "Erhard" <erhard@nomail.nomail> wrote in message
> news:E56E721A-3EC9-4491-ADE1- 7532E38C5387@microso
ft.com...
>
>
>
| |
| Robert Morley 2005-10-27, 8:32 am |
| 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 2005-10-27, 8:32 am |
| 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" <rmorley@magma.ca.no.freakin.spam> wrote in message
news:OM59JQC0FHA.3588@tk2msftngp13.phx.gbl...
> 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.
>
>
> 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 2005-10-27, 8:32 am |
| 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" < brenreyn@discussions
.microsoft.com> wrote in message
news:uQAxdYC0FHA.3588@tk2msftngp13.phx.gbl...
> 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" <rmorley@magma.ca.no.freakin.spam> wrote in message
> news:OM59JQC0FHA.3588@tk2msftngp13.phx.gbl...
>
>
| |
| Brendan Reynolds 2005-10-27, 8:32 am |
| Thanks Robert.
--
Brendan Reynolds
"Robert Morley" <rmorley@magma.ca.no.freakin.spam> wrote in message
news:e3gyJkD0FHA.3812@TK2MSFTNGP09.phx.gbl...
> 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" < brenreyn@discussions
.microsoft.com> wrote in message
> news:uQAxdYC0FHA.3588@tk2msftngp13.phx.gbl...
>
>
| |
|
|
"Erhard" <erhard@nomail.nomail> сообщил/сообщила в новостях следующее:
news:E56E721A-3EC9-4491-ADE1- 7532E38C5387@microso
ft.com...
> 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
>
|
|
|
|
|