INSERT INTO another access table from ADP based on field

[AMH]

Hi,

I was hoping someone could help me with inserting a record from an ADP
to a MDB, based on the current record displaying on a form.

eg. I add a new record in an access form, when I click Save, copies
that record to another database.

Previously, both databases were MDB, and this is the SQL I used:

INSERT INTO [MS Access;DATABASE=T:\Databases\Training
Database\training.mdb].training ( [number], type, name, [Yes-No_Fld] )
SELECT documents.number_rev, "Internal" AS Expr2, documents.title, "No"
AS Expr1
FROM documents
WHERE (((documents.number_rev)=Forms!documents_form!number_box));

Now, I need to do the same thing from SQL Server (ADP) to MDB.

I have heard that you can do this using an ADO command, but I have no
idea what that is...?

Any help would be appreciated.

 

[Norman Yuan]

Yes, you can simply use ADO to update data in an external database from
inside ADP app, whether it is MDB file or another SQL Server DB. Here is the
psudo code:

Dim cn As ADODB.Connection
Set cn=New ADODB.Connection
cn.Open connectionString_To_TheDB

Dim strSQL As String
strSQL="INSERT INTO theTable (....) VALUES (...)"
cn.Execute strSQL
cn.Close

When the connection is to a MDB file, the ConnectionString looks like:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\myPath\myDb.mdb"