Re: Query Two Databases

Hi Ekkehard,

>> My apologies for bringing up the nasty IN clause. <<

Not at all ... I greatly appreciate your ideas. I have learned a lot!

Thanks, too, for the Tek-Tips URL. Apparently this issue cannot be resolved
with a single connection string.

Thanks,

Joe


"ekkehard.horner" wrote:

> JSzabo wrote:
> > Hi Ekkehard,
> >
> > Thanks for these great ideas. (1) relies on a DSN, and I want to avoid
> > that. (2) is more familiar to me, but I’m unsure about this part of it:
> > “uid=<UserID>; ? User=<UserID>; pwd=<Strong Password> ? Password=<Strong
> > Password>” I don’t understand the reason for including both the “uid” and
> > “User” arguments, plus the “pwd” and “Password” arguments. Neither do I
> > understand the purpose of the “?” characters. Still, I like this general
> > approach because it allows me to handle everything dynamically in VB code. I
> > could avoid DSNs and linked tables.
> My fault; I used this peculiar syntax to indicate that you should experiment
> with different keywords. Of course "Database password" didn't come to my mind.
> But as my experiments told me, it's useless anyway: the syntax of connection
> strings isn't valid for IN clauses.
> >
> > Here is the string I’m currently using to open the databases:
> > "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=<DatabaseFileName>; Jet
> > OLEDB:Database Password=<DatabasePassword>” As you can see, there is no
> > user-level security.
> >
> > I checked out the KB article. It contains much good information, and I
> > spent an hour trying the ideas there, but I can’t seem to find the right
> > combination of arguments. I kept getting errors like “ODBC call failed” and
> > “Disk or network error”.
> Same here!
>
> > I’m still considering the linked table approach as an alternative. That is,
> > I could create in the first database a link to the desired table in the
> > second database. I did this as an experiment the other day, and I found the
> I believe this is the best way to get the job done.
>
> > link information (including the second db’s password) in the hidden/system
> > MSysObjects table. If this MSysObjects row could be used to maintain the
> > password, I could use this approach. (But I would have to update the
> > MsysObjects row whenever someone changed the password on the second
> > database.) Unfortunately, I am not really familiar with the MsysObjects
> > table. I don’t know how “stable” or “reliable” its link data would be.
> I would try to mess with the MSysObjects. If the person responsible for
> the second database decides to change the password, it should be this
> person's responsibility to update the ODBC Datasource your programm uses
> to link to this DB.
> [...]
> > "ekkehard.horner" wrote:
> >>JSzabo wrote:
> [...]
> >>>>JSzabo wrote:
> >>>>>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.
> [...]
> But not if nordwind2.mdb is protected by a database password.
> [...]
> If you want to loose all hope, look here:
> http://www.tek-tips.com/viewthread.cfm?qid=124548
>
> My apologies for bringing up the nasty IN clause.
>
> Ekkehard
>
.

Relevant Pages