Opening TWO secured databases and UPDATING from each other.

[pouggy]

I am getting so frustrated with my quest. Thanks to cmmrfrds, but I cannot update a table in one secured database from a table in another secured database.

I am using the ADODB.Connection; manage to open both databases, but just do not know how to construct the clause for the SQL statement to update one table from the other.

After opening the connections, it is simple to run a query to delete records in a single table.

Dim MUCn , STCn as ADODB.Connection
Dim strCnn, strSQL as String

strCnn = "Provider= etc; Data Source=x:\file1.mdb; System Database=x:\sys1.mdw etc ....

Set STCn = New ADODB.Connection
STCn.Open strCnn

strCnn = "Provider= etc; Data Source=z:\file2.mdb; System Database=z:\sys2.mdw etc ....

Set MUCn = New ADODB.Connection
MUCn.Open StrCnn

strSQL = "DELETE * FROM tbl_Dealers" 'This works
MUCn.Execute strSQL,, adExecuteNoRecords

But I want to do something like this.

strSQL = "INSERT * FROM tbl_Dealers (in database 1 into) tbl_Dealers (in database 2)

or Update one from the other.

strSQl = "UPDATE field1 FROM tbl_Dealers (in database 1 into tbl_Dealers (in database 2) where this equals that etc

I have two connections which I know are correct as I can reference the tables in each, so where am I going wrong or what is the syntax?

HELP PLEASE

Chip on the shoulder means wood higher up

 

[pouggy]

No need to reply anyone. I have been bloody stubborn and have finally worked out how to do it. For those who are interested, set up the ADODB connection; no recordsets required; the SQL statement should read like this:

strSQL = "INSERT INTO tbl_Dealers IN 'C:\Documents and Settings\Admin\My Documents\Customer to MU Update\mudb01be.mdb' SELECT tbl_Dealers.[Dealer ID], tbl_Dealers.[Co Name Suffix] FROM tbl_Dealers IN 'C:\Documents and Settings\Admin\My Documents\Customer to MU Update\stigadb72bexp.mdb'"

(ignore the line breaks above)

MUCn.Execute strSQL, , adExecuteNoRecords



Chip on the shoulder means wood higher up