Re: Query Two Databases

Hi MGFoster,

Thanks for the idea. Unfortunately, the password on the external db causes
problems. I got the following error message when I ran a test query: “Not a
valid password”.

I will tinker around some with this idea to see if I can specify the
password in the SQL string.

Joe


"MGFoster" wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Depends on the databases. If the 2 DBs are MS SQL Server DBs on the
> same server you can do a query like this:
>
> SELECT A.*, B.*
> FROM databaseA..table1 AS A INNER JOIN dtabaseB..table1 AS B
> ON A.<some ID column> = B.<some ID column>
> .... etc. ...
>
> If the DBs are both Oracle, I believe you can use the same syntax, since
> it is an SQL standard naming convention.
>
> The syntax for indicating a table is
>
> <database name>.<owner name>.<table name>
>
> If the DBs are Access DBs, you can use DAO like this:
>
> SELECT A.*, B.*
> FROM table1 IN "C:\My Documents\DatabaseA.mdb" As A
> INNER JOIN table2 IN "C:\My Documents\DatabaseB.mdb" As B
> ON A.<some ID column> = B.<some ID column>
>
> Perhaps it would work in in ADO, also.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQ7HMwIechKqOuFEgEQKKtACg5n+L2lEg+R0kI3tfPzfL4FR6yS4AoIAG
> i86oTFK2wJ6kkgmPwkPrjxbU
> =APdA
> -----END PGP SIGNATURE-----
>
> JSzabo wrote:
> > Hi,
> >
> > I am (still) developing with VB 6.0 and MS Access 2000 on Win98-R2.
> >
> > Is it possible to easily/reliably query two tables that each exist in a
> > different database, then return the results in a single recordset? Or must I
> > create a linked-table reference in one of the databases? Or must I do
> > something else … ?
> >
> > An app I am working on uses two separate databases. The first is a large
> > set of “lookup” tables, and data stored there doesn’t change frequently. The
> > second database is used/updated much more frequently. Here I need to create
> > a new table that stores keys to a “lookup” table in the first database.
> >
> > I really don’t want to combine the two databases. And since the dbs are
> > password-protected, I don’t want to use the table-link approach if I can
> > avoid it.
> >
> > Is it possible in MS Access to use the same ADO connection/query to access
> > tables in two separate dbs?
>
.

Relevant Pages

  • Re: Concatenating the same field from multiple records
    ... Dim dbs As DAO.Database ... dim utblSQLPartInsert as String ... each of the sql steps is different so i would need to set the variable each ... it be better to execute the query, or can i use docmd.sql " my sql here" ...
    (microsoft.public.access.queries)
  • Re: merge multiple databases
    ... IE make the query in the new Access file, ... As YYZ commented the SQL version was way ... Test applied to merging 8 databases with three tables each. ... The record looping method had more status updates via a label but the ...
    (microsoft.public.vb.database.dao)
  • Re: merge multiple databases
    ... IE make the query in the new Access file, ... As YYZ commented the SQL version was way ... Test applied to merging 8 databases with three tables each. ... The record looping method had more status updates via a label but the ...
    (microsoft.public.vb.general.discussion)
  • Re: merge multiple databases
    ... IE make the query in the new Access file, ... As YYZ commented the SQL version was way ... Test applied to merging 8 databases with three tables each. ... The record looping method had more status updates via a label but the ...
    (microsoft.public.access.queries)
  • Re: sql 2000 - slow response times after creating a new database
    ... >>databases due to them being different collations, ... > scans to match your query results. ... > For simple fix, script off the old indexes and re-create them on the new ... > Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.server)