You can insert new records into a remote database table by opening a linked table and inserting the records with the AddNew and Update methods, as you would with any other Microsoft Jet table. Whenever you insert records using DAO operations, you should use the dbAppendOnly constant in the options argument of the OpenRecordset method. This causes the engine to optimize its operations for adding data.
For example, the BookSales and BookSalesOptimized sample applications include a bulk data loader that creates the sample data used by the rest of the application. You can find this code in the DataAlterSales procedure of the Dataload.bas module. Using the Recordset approach, the code to add a single record is as follows. In this example, dbs
is a Database object representing the remote data source, rst
is a Recordset object, and conSalesID
is a constant numeric value:
Set rst = dbs.OpenRecordset("Sales", dbOpenDynaset, dbAppendOnly) With rst .AddNew !stor_id = "6380" ' All linked to the first store. !ord_num = conSalesID & Format(intC, "0000") !ord_Date = Now !qty = 5 !payterms = "Net 60" !title_id = "BU2075" .Update .Close End With
Although this is a simple method, using a pass-through query with an SQL INSERT statement can be much faster in the client/server environment. The same code, implemented using a pass-through query, becomes:
Dim strInsert As String strInsert = "INSERT INTO Sales VALUES ('6380', '" _ & conSalesID & Format(intC, "0000") _ & "', '" & Now & "', 5, 'NET 60', 'BU2075')" dbs.Execute strInsert, dbSQLPassThrough
Building an INSERT statement from scratch like this can be tricky if your data has embedded quotation marks ("). If you’re only doing single updates, the time saved is generally not worth the effort, especially if the update is tied to a form.