Re: Query Two Databases

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.

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”.

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
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’m still thinking about all this. Thanks very much for your ideas!

Joe


"ekkehard.horner" wrote:

> 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: Query Two Databases
    ... relies on a DSN, and I want to avoid that. ... I could avoid DSNs and linked tables. ... I could create in the first database a link to the desired table in the second database. ... If this MSysObjects row could be used to maintain the password, ...
    (microsoft.public.data.ado)
  • Re: how secure is Querystring authentication using uniqueidentifier
    ... Someone who reads this mail knows the internal user id of the user in the ... internal details (for example it could be the guid of a registration action ... You could also crypt but generally I would say that it's still best to avoid ... > uniqueidenfier in the sql server database. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: connecting to two adp projects error-
    ... We have an ADP which is linked to one database but which has to ... make a call on startup to a second database for extended user ... Your point of handling action on the server is taken. ... T-SQL code in your stored procedure. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Avoiding the use of CACHE in SQL2000
    ... Note, though that PINTABLE does not give us the option to *avoid* caching read pages, it is exactly the ... it will not leave the cache. ... >> Do exists the possibility to avoid the use of cache for certain Database ... >> I have a production Database and a historical database. ...
    (microsoft.public.sqlserver.programming)
  • Re: connecting to two adp projects error-
    ... T-SQL code in your stored procedure. ... database "Requests" on server A. Also on server A is a second database ... The problem I am experiencing is that the second connection seems to work ... using the fully-qualified name of the resource in the second database: ...
    (microsoft.public.access.adp.sqlserver)