Re: Creating a recordset of a table in an external database

From: Marshall Barton (marshbarton_at_wowway.com)
Date: 03/27/05 

Date: Sun, 27 Mar 2005 08:53:33 -0600

Shadow wrote:
>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")

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. 

-- 
Marsh
MVP [MS Access]

Relevant Pages

  • Re: Connection types and speeds
    ... The memory used by holding the connection open is not ... All recordset inserts seen to consequently have 0ms execution time, ... actually written to the database while the code has regained control ... RecordsetClone of a form) when a transaction was rolled back, ...
    (microsoft.public.access.queries)
  • Re: Validate logins with ASP, MS Access and Cookies error
    ... return another column from the database that stores the users 1st name :-) ... 'create connection and recordset objects ... Set cnStr = Server.CreateObject ... ' validate variables against database ...
    (microsoft.public.inetserver.asp.db)
  • Re: Recordset problem
    ... This is why I'm trying to use Recordset. ... Dim strPrice As String ... >> Dim dbs As Database ... >You don't need to open the database object dbs before ...
    (microsoft.public.access.modulesdaovba)
  • Re: Validate logins with ASP, MS Access and Cookies error
    ... return another column from the database that stores the users 1st name ... 'create connection and recordset objects ... Set cnStr = Server.CreateObject ... ' validate variables against database ...
    (microsoft.public.inetserver.asp.db)
  • Re: Validate logins with ASP, MS Access and Cookies error
    ... return another column from the database that stores the users 1st name ... 'create connection and recordset objects ... Set cnStr = Server.CreateObject ... ' validate variables against database ...
    (microsoft.public.inetserver.asp.db)