Accessing a table in MDB file

[Jeff Trate]

It's a very basic question and I had no problem in the past but ADO is throwing
me a curve. How do I set up during run time which MDB file to use and how
to point my query to a particular table. I tried setting the connection
and recordsource but neither yielded the desired results.

[Tim Martin]

Jeff-
I've done something similar in Access 2000 VBA. My procedure was in one .mdb
but the data for my report was in another. The second database was already
set up in the system DSN.
Hope this is not too different from what you are coding up.
Here are some of the lines of code that I used:

Option Compare Database
Option Explicit
Option Base 1

Dim DBConn(2) As ADODB.Connection ' (1) = Local Database, (2) = History
Database

Set DBConn(1) = CurrentProject.Connection
' Point to Historical Data Database
Set DBConn(2) = New ADODB.Connection
DBConn(2).ConnectionString = "dsn=historydb"
DBConn(2).Open

Tim

"Jeff Trate" <jtrate@agere.com> wrote:
>
>It's a very basic question and I had no problem in the past but ADO is throwing
>me a curve. How do I set up during run time which MDB file to use and how
>to point my query to a particular table. I tried setting the connection
>and recordsource but neither yielded the desired results.

[Tim Faircloth]

"Jeff Trate" <jtrate@agere.com> wrote:
>
>It's a very basic question and I had no problem in the past but ADO is throwing
>me a curve. How do I set up during run time which MDB file to use and how
>to point my query to a particular table. I tried setting the connection
>and recordsource but neither yielded the desired results.
Set up you connection object:
Set dbR2 = New ADODB.Connection
Set rsR2 = New ADODB.Recordset
Set rsMult = New ADODB.Recordset
dbR2.Provider = "Microsoft.Jet.OLEDB.3.51"
'dbR2.ConnectionString = "f:\rbsschedule\tables\rbssched.mdb"
dbR2.ConnectionString = "c:\archive\rbssched2000\tables\rbssched.mdb"
dbR2.Open
recLockedBy = ""

[Tim Faircloth]

"Jeff Trate" <jtrate@agere.com> wrote:
>
>It's a very basic question and I had no problem in the past but ADO is throwing
>me a curve. How do I set up during run time which MDB file to use and how
>to point my query to a particular table. I tried setting the connection
>and recordsource but neither yielded the desired results.
Set up you connection object:
Set dbR2 = New ADODB.Connection
Set rsR2 = New ADODB.Recordset
str_with_db_Location = "c:\MSAccess.mdb"
' Access97
dbR2.Provider = "Microsoft.Jet.OLEDB.3.51"
dbR2.Provider = "Microsoft.Jet.OLEDB.4.0"
dbR2.ConnectionString = str_with_db_Location
dbR2.Open
Open you table with your recordset object
rsr2.activeconnection = dbr2
rsr2.source = "Select * from Tablename"
rsr2.cursortype = adopenstatic
rsr2.locktype = adopenbatchoptimistic
rsr2.open
hope this helps