Creating a recordset of a table in an external database

  • Thread starter Thread starter Shadow
  • Start date Start date
S

Shadow

Hello,

I have two databases and each one contains several
tables, forms.......(database A and database b)
I need to create a recordset of a table in database A while I'm running some
vba codes in a module in database B.
While the following code creates a recordset from a table in current
database, how can I create a recordset from a table in an external database?
set db=currentdb
set rst=db.openrecordset("tblname")

Any kind of help is much appreciated.


Ghalamkari
 
Shadow said:
I have two databases and each one contains several
tables, forms.......(database A and database b)
I need to create a recordset of a table in database A while I'm
running some vba codes in a module in database B.
While the following code creates a recordset from a table in current
database, how can I create a recordset from a table in an external
database? set db=currentdb
set rst=db.openrecordset("tblname")
Click to expand...

set db= Opendatabase(path)
set rst=db.openrecordset("tblname")

see OpenDatabase-method in Help
 
Jörg Ackermann,
Million thanks for your quick response and great help.
Exactly what I was looking for.

Best Regards
Ghalamkari
 
Shadow said:
I have two databases and each one contains several
tables, forms.......(database A and database b)
I need to create a recordset of a table in database A while I'm running some
vba codes in a module in database B.
While the following code creates a recordset from a table in current
database, how can I create a recordset from a table in an external database?
set db=currentdb
set rst=db.openrecordset("tblname")
Click to expand...


A couple of other ways to do this. The easiest is to link
to the other database's table using the File - Get External
Data - Link menu item. Then just open the recordset as if
the table were in the local db:

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblname", dbOpenDynaset)

Another is to open the remote table using an SQL statement
that uses the IN phrase in its FROM clause:

Set db = CurrentDb()
strSQL = "SELECT * FROM tblname IN ""C:\path\B.mdb"""
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

In most cases, you don't really need to use
OpenDatabase("C:\path\B.mdb") unless you require the
recordset to be opened with dbOpenTable.
 
So many ways to do the same task and I wasn't aware of a single one. It
seems I have to study a little bit more.

I just don't know how to thank you for the solutions.
Ghalamkari
 
Back
Top