Opening TWO secured databases and Updating tables from each other

[pouggy]

I have two secured databases, both of which use different MDW files. I need to open both of them and then update the tables in one from the other. I am using the ADODB.connection where I parse the database file names, user IDs and passwords etc. This works fine, but I just don't know how to reference the tables within each database. For example and in particularly, I want to run an UPDATE where the data from one table in the FIRST database will update the data in another table in the SECOND database.

I am getting so confused about the methods needed to accomplish this and I cannot find any examples.

Please help

 

[markphsd]

Do some sort of loop, where you loop through the table of db1 and insert you can insert the record into db2.

like having a recordset from db1 as rst1, and a rst2 from db2

rst1.movefirst

do while not rst1.eof

rst2.add
rst2(fieldName) = rst1(fieldName)
rst.update

rst1.movenext
loop





Mark P.

Bleh

 

[cmmrfrds]

Here is an example.

Dim cn As New Connection, cn2 As New Connection
Dim rs As New Recordset, rs2 As New Recordset
Dim connString As String, connString2 As String
Dim sql1 As String, sql2 As String
'-- string with password, default "User ID=Admin"
'Provider=Microsoft.Jet.OLEDB.4.0;Password=sam;
'Data Source=C:\ATestDir\Atest.mdb;Persist Security Info=True
'Provider=SQLOLEDB.1;Persist Security Info=False;
'User ID=sa;Initial Catalog=Northwind;Data Source=localhost

connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AEmptyDir\employee.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString
connString2 = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AEmptyDir\employee2.mdb;" & _
"Persist Security Info=False"

cn.ConnectionString = connString2
cn.Open connString2

sql1 = "select * from cn...employees as A inner join cn2...employees as B" & _
"ON A.employeeID = B.employeeID"

rs.Open sql1, CurrentProject.Connection, adOpenStatic, adLockOptimistic
Debug.Print "rs = "; rs(0); " "; rs(1)

 

[cmmrfrds]

You can also use a fully qualified file name.

Dim sql1 As String, exterDB As String
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Set cn = CurrentProject.Connection

exterDB = "C:\AEmptyDir\employee.mdb"

sql1 = "select * from employees IN '" & exterDB & "';"

rs.Open sql1, cn, adOpenStatic, adLockOptimistic

 

[pouggy]

Thanks everyone.

Markphsd. Your solution was the one I have used so far, but it is very cumbersome. I wanted to use an SQL statement.

Cmmrfrds. Your answer gives me what I want, but I was so sure that I tried this. It was the actual syntax that was giving me the problem, that is the reference to each connection and table.

I shall give this a try.

Lewis Hamilton

Chip on the shoulder means wood higher up