Re: Query Two Databases

JSzabo wrote: 
Hi Ekkehard,

Thanks again 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.
[...] 
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 … ? 

[...]
Using 2 copies of nordwind.mdb (c:\temp\nordwind1.mdb, c:\temp\nordwind2.mdb)
the statement

    select   B.[Bestell-Nr]
           , B.[Kunden-Code]
           , K.[Kunden-Code]
           , K.[Firma]
    from   Kunden       as K
         , Bestellungen as B in "c:\temp\nordwind2.mdb"
    where B.[Kunden-Code] = K.[Kunden-Code]

works (for me) as expected when executed from a oledb/jet
connection to c:\temp\nordwind1.mdb.
I admit, the syntax of a IN-clause is hard to get right
(I had to experiment a bit), so YMMV.

I would try to
  (1) create a ODBC data source for the second database and
      use '... as B in "ODBC;DSN=<yourdsn>"'
  (2) tinker with the connection string
      '... as B in "Provider=Microsoft.Jet.OLEDB.4.0;
                    Data Source=c:\temp\nordwind2.mdb;
                    uid=<User ID>;        ? User=<User ID>;
                    pwd=<Strong Password> ? Password=<Strong Password>
                   "'
Perhaps
   http://support.microsoft.com/kb/q245587/
will give you some hints to get started.
.

Relevant Pages

  • Re: Problem with VB6 and Oracle 9i ODBC connection
    ... While" loop from the first SQL string is not matching what the second ... with the connection string to the database via ODBC? ... It is just remotely possible that you have connected to the wrong database. ...
    (comp.lang.basic.visual.database)
  • Re: Change Connection String at runtime.
    ... easily moved to another database, or have versions for each database. ... connection string to the connectionobject before the connection opens. ... *The Server IP number is different for every deployment. ... It appears that the generated code uses the application settings to ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Change Connection String at runtime.
    ... exposes custom objects that are implemented to use the custom data access ... easily moved to another database, or have versions for each database. ... and protocols each data source exposes. ... connection string to the connectionobject before the connection opens. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Please help, linking VB to Access
    ... If you are going against a Jet database and will never change then ... Dim DB as ADODB.Connection ' specifying the library.object format helps ... you can use the Data Environment in VB to build a connection string (don't ... use the data environment in any actual applications, ...
    (microsoft.public.vb.general.discussion)
  • Re: How can I connect this database?
    ... Sorry for writing ISS for IIS. ... I know surely that ASP cannot connect to a database itself. ... have to add to connection string of ASP. ...
    (microsoft.public.inetserver.asp.db)