If NewRecord in Tblone then append that record to tbltwo

[Vulton]

I have 3rd party application that uses a access database to keep track of "daily work". I can link to the tables in this aplications database and would like to append the information in the linked tables to my SQL tables as the aplication adds records to its tables.

So...
For each new record added to TblAccess then append that record to TblSQL.
Without user intervention (no buttons to push, forms to fill out)

Any suggestions?
Thanks in advance

 

[IanAble]

The Form_AfterUpdate Event will be helpful if you want to do it in "real time". Assuming there's a field with a unique value (which is the same in tblAccess and tblSQL) you can search for the record in tblSQL and if it's not there append a new record, if not update the old one.

Use the following to find the Access record on the form and cycle through the Fields to so you can add update the SQL record.

Dim cnnCurrent as ADODB.Connection
Dim rstForm as DAO.Recordset
Dim rstSQL as ADODB.Recordset
Dim fldCurrent as DAO.Field

Set cnnCurrent = CurrentProject.Connection
set rstSQL = New ADODB.Recordset
rstSQL.Open strSQL, cnnCurrent
Set rstForm = Me.RecordsetClone
With rstForm
.FindFirst "[PrimaryField]=" & Me![Primary]
.If Not .EOF then
rstSQL.Find "[PrimaryField]=" & Me![Primary]
if rstSQL.EOF then
.AddNew
For each fldCurrent in .Fields
...map fields, validate and update!
rstSQL.Fields(SQLFieldName) = fldCurrent
Next fldCurrent
rstSQL.Update
Else
...Repeat mapping and update as in AddNew
rstSQL.Update
End If
End if
End With

Of course it'll get trickier where's there's more than one table involved but BeginTrans, RollBack, and CommitTrans will get you out of that one.

Alternatively, you could search for New/Updated records when you open the Access App and do a batch update with queries... Creating a Form_Timer procedure within the splash form will do this seamlessly!

Good Luck


Ian