Using DAO to insert new records into remote table

[accessNator]

I have a CompanyList table that exist on my local database and on a remote SQL Server database

This table has the following columns:
CompListRefID,CompanyName , State

I created a query which compares what records that have been added in the local vs the remote table.

What I want to do is any records that exist locally that do not exist in the remote table, insert those records as a new record in the remote table. For each new record in the remote table, I need to retrieve the AutoNumber ID of the CompListRefID field.

Also in the REMOTE database, I have another table called CompanyType which consists of the following columns:
CompanyTypeRefID, FK_CompListRefID, TypeID

When records are inserted in the REMOTE table CompanyList, I retrieve the CompListRefID and insert that ID as part of new record in the CompanyType table in the column FK_CompListRefID and also pass a numeric variable to the TypeID as well.

All this is working. I am using DAO to do this.

Basically, I am looping through each record locally, then going out to the remote DB and insert a new record remotely, pull the auto id and insert another record in another table.

In my test run, with 25 records, it does take approx 25 seconds. Way to long. I was wondering if anyone had another suggestion on how to do this?

Thanks in advance,
Chuck

 

[Rx_]

In your case, is the "remote SQL" a Linked Table from your Access DB front-end?
Typically, an Update query is efficient more than a DAO cursor . Here is an example of a SQL Linked table update.
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE ED_Plat SET ED_Plat.Built = Null WHERE (((ED_Plat.ID_Well)=" & Me.ID_Well & "));")
DoCmd.SetWarnings True


In this example, the remote Access DB table is deleted then repopulated using SQL statements.

Result = DeleteTableFromBackEnd(DBPath, TblName) ' deletes existing
 
mysql = "SELECT View_GIS_Union.ObjectID, [more SQL not shown] "
mysql = mysql & " INTO Regulatory_GIS IN 'Y:\Admin\STD_GIS.accdb' FROM View_GIS_Union "
mysql = mysql & " ORDER BY View_GIS_Union.St, [More SQL not shown];"
 
CurrentDb.Execute mysql

Private Function DeleteTableFromBackEnd(DBPath As String, TblName As String)
      Dim db    As DAO.Database
      Dim rst   As DAO.Recordset
      Set db = DBEngine.OpenDatabase(DBPath)
10        On Error Resume Next
30        If Err <> 0 Then     'failed to open back end database
40            DeleteTableFromBackEnd = False
50            db.Close
55            rst.Close
60            Set db = Nothing
70            Exit Function
80        Else
90            db.Execute "DROP TABLE [" & TblName & "]"
100           If Not db Is Nothing Then db.Close
110           DeleteTableFromBackEnd = True
120       End If
125       rst.Close
130       db.Close
140       Set db = Nothing
End Function

Instead of inserting an entire table, a append or update query could be used.

 

[accessNator]

Here is my code. The User clicks on the button CmdAction.

Private Sub cmdAction_Click()
        
    Dim db As DAO.Database
    Set db = CurrentDb

    Dim rstSource1 As DAO.Recordset
    Dim Query1 As String
    Query1 = "qryResultsDifferences1"

    ' Initialize Query
    Set rstSource1 = db.OpenRecordset(Query1, dbOpenDynaset)
    
    If rstSource1.RecordCount = 0 Then Exit Sub
    rstSource1.MoveFirst
    
    Dim passCompanyName As String
    Dim passStateId As Integer
    Dim passTypeOfID As Integer
    Dim retreiveRefID As Integer
    
            DoCmd.Beep      
            If MsgBox("Do you wish to ADD NEW RECORD(s) in the REMOTE DATABASE?", vbYesNo, "ADD") = vbYes Then

            Do While Not rstSource1.EOF
                        
            passStateId = rstSource1![state_id]
            passCompanyName = rstSource1![CompanyName]
            passTypeOfID = rstSource1![type_id]
              
                ' Insert Record into CompanyList table in Remote Database
                ' and Retreive last record ID
                retreiveRefID = NewCompanyRecord(passCompanyName,passStateId)

                ' Insert Record into CompanyType table in Remote Database
                Call InsertCompanyType(retreiveRefID, passTypeOfID)
            
            rstSource1.MoveNext
            Loop
            Call ResetForm

            Else
            Exit Sub
            End If
            
    rstSource1.Close
    Set rstSource1 = Nothing
    Set db = Nothing
    
    MessageBoxStatus "Finished Updating records!"
          
    DoCmd.Beep
    
End Sub

Function NewCompanyRecord(passCompanyName As String,passStateId As Integer)

' Insert Record into CompanyList table in Remote Database
' and Retreive last record ID

Dim db As DAO.Database
Set db = CurrentDb

Dim rst As DAO.Recordset
Dim Table As String
Table = "dbo_CompanyList"

    Set rst = db.OpenRecordset(Table, dbOpenDynaset, dbSeeChanges)
        With rst
            .AddNew
            ![CompanyName] = passCompanyName
            ![State] = passStateId
            .Update
            .Bookmark = .LastModified
            NewCompanyRecord = ![CompListRefID] ' Return Last Record ID
        End With

    rst.Close
    Set rst = Nothing
    Set db = Nothing
    
End Function

Private Sub InsertCompanyType(passRefID As Integer, passTypeOfID As Integer)

Dim db As DAO.Database
Set db = CurrentDb

Dim rst As DAO.Recordset
Dim Table As String
Table = "dbo_CompanyType"
    
    Set rst = db.OpenRecordset(Table, dbOpenDynaset, dbSeeChanges)
        With rst
            .AddNew
            ![FK_CompListRefID] = passRefID
            ![Type_ID] = passTypeOfID
            .Update
        End With
        
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    
End Sub

 

[accessNator]

Maybe this will help you with some ideas to get started.
In this example, the remote table is deleted then repopulated with SQL:

Thanks for your reply. But I am a bit lost on whats going on here. Can you provide a better understanding on what you are doing? Even if I dont use your example, I may need it for future use or still in my case, may still use your logic.

 

[accessNator]

Is the "remote SQL" a Linked Table from your Access DB front-end?

Yes it is.

 

[Rx_]

Only had a minute to review. Assuming your remote sql server is a linked table.
The loop with DAO uses a Cursor that goes record to record. Without going into too much detail, in general, the larger the number of records, the longer this can take. Each time through the loop is a network command issued.
This might be something I use for data sets that are small. e.g. the States, County, Township table. A couple of hundred records that will not grow to three hundred records over the lifecycle. It is the MoveNext and Loop that cost time.
Cursors provide a lot of rule management control. Lots of details. But, more traffic.
In an application that can result in many records, issuing a SQL statement is generally more efficient.
If the Move Next and Loop must be used, then limit your recordsets to only the records that must be updated.
As a typical contractor, the answer is "it depends".
That said, by removing a DAO cursor (I am a big fan of DAO) and spending time to design a SQL Statement is worth the time in many cases. 30 seconds is a long time if you have multiple users updating data while it is going on.

 

[Rx_]

Can you identify the new record(s) on your local database you want to add to the linked SQL Table and just write an Append SQL statement?
Identify with a query the last changed records, and append them to the linked table.
If all you are doing is appending new records from one table to another table, executing SQL will be the fastest.
I just recentlly converted over from a Access 2010 back end database to a SQL Server Database. Some process using cursors to loop and evaluate were a dozen times faster on Access than SQL. No problem, just updated the method on the slow ones.

 

[accessNator]

Can you identify the new record(s) on your local database you want to add to the linked SQL Table and just write an Append SQL statement?
Identify with a query the last changed records, and append them to the linked table.

Yes, I can identify the new records local, but my problem is that I need the new record ID that I append to in the linked remote table and use it for another linked remote table from the SQL Server.

Is there an example you may have that I can get a grasp? Thoughts?