multiple database access

[svbs tech]

hi
i am using vb6 and i want to access two database in single query. both the databases are password protected. i want to select all records from first database which are not in the second database..
please help me..

[Rabbit]

You can't do it in one query. You'll have to use two recordsets and then compare both in code.

[svbs tech]

i did it in one single query without password proctected database. but i dont know how to write query for password protected databases.
the code is

Dim AdoConn As ADODB.Connection, AdoRSet As ADODB.Recordset
Dim AdoConn1 As ADODB.Connection, AdoRSet1 As ADODB.Recordset
Dim sCon As String
Dim AdoPassword As String
Dim AdoErr As Byte

AdoDatabasePath = App.Path & "\db.mdb"
AdoPassword = "xyz"
'CONNECTION STRING
sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Password=;" & _
"User ID=Admin;Data Source=" & AdoDatabasePath & ";" & _
"Jet OLEDB:Database Password='" & AdoPassword & "'"
Set AdoConn = New ADODB.Connection
AdoConn.Open sCon

AdoDatabasePath = App.Path & "\db1.mdb"
sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Password=;" & _
"User ID=Admin;Data Source=" & AdoDatabasePath & ";" & _
"Jet OLEDB:Database Password='" & AdoPassword & "'"
Set AdoConn1 = New ADODB.Connection
AdoConn1.Open AdoConn.Execute("select * from test")

qu = "select distinct(a.test) from test a where a.test not in (select * from [;database=" & AdoConn1 & "].test12 ) "

Set AdoRSet = AdoConn.Execute(qu)

[Rabbit]

Please use code tags when posting code.

I stand corrected, you can access other databases from the SQL engine.

However, with a secured Access database, you have to set other options because Access does not use the username and pwd sections of a connection string.

You need to use

' Establish the location of the SYSTEM.MDA files if in another
   ' directory other than the \WINDOWS directory:
   SetDataAccessOption 1, "C:\MYDIR\MYAPP.INI"
   ' Log on to a valid account:
   SetDefaultWorkspace "admin", "<password>"

to set up the needed access.

Source: http://support.microsoft.com/kb/113701

Also, according to the source, the connection only needs to be set up with the path

[;database=C:\ACCESS\DB1.MDB]

[svbs tech]

please tell me how to specify password in [;database=C:\ACCESS\DB1.MDB] statement to connect with the second database.

[Rabbit]

You don't, at least not through the connection string. You set it up using the options I posted before that.