MS Access – VBA – Append/Insert Record into Another External Database

So how can one take data from one database and insert it into another database?

There are 2 methods that come to mind and both are quick and easy to implement.

 

Specifying the Database to Append to in Your SQL Statement

The first method is to modify your query to include the external database as a reference. A basic Insert query would look something along the lines of:

INSERT INTO TableName ( Field1, Field2, ...)
VALUES ( Value1, Value2, ...);

but did you know you can also include, as part of your SQL statement, the database to append the data into?! So we could easily modify the SQL statement like:

INSERT INTO TableName ( Field1, Field2, ...) In 'FullPathAndDbNameWithExtension'
VALUES ( Value1, Value2, ...);

Here’s a concrete example:

INSERT INTO Temp1( FirstName, LastName) In 'C:\Databases\Clients.mdb'
VALUES ( "Don", "Hilman");

 

Using Linked Tables

Another approach is to merely create a linked table to your secondary database and then run a normal append query on your linked table!

Either way, as you can clearly see for yourself, it is not a hard thing to insert data into a table contained within another external database!

6 responses on “MS Access – VBA – Append/Insert Record into Another External Database”

  2. theodoros Pappas

    thanks for the source code : Append/Insert Record into Another External Database”

    We can to use this code with on change when the database there is in the some pc on the lan

    with this code (select statement):
    1. fill the listbox
    Me.listbox.RowSource = “SELECT * FROM [\\nameofpcinthenetwork\directory\databasefil.accdb].tablename condition

    1. example
    Me.listnames.RowSource = “SELECT * FROM [\\Allnow\database\DATA.accdb].PARAMETROI WHERE ID=1”

    2. with this code (insert, top,randomize record for each time and the select statement):
    DoCmd.RunSQL (“INSERT INTO tablername (field1,field2,field3) ” _
    & ” SELECT TOP 5 field1,field2,field3 FROM[\\nameofpcinthenetwork\directory\databasefil.accdb].tablename ORDER BY rnd(INT(NOW*ID)-NOW*ID)”)

    2. example
    DoCmd.RunSQL (“INSERT INTO exam1 (IDEROT,EROTHSH,AP1,AP2,AP3,AP4,SOSTH) ” _
    & ” SELECT TOP ” & Me.AREROT & ” IDEROT,EROTHSH,AP1,AP2,AP3,AP4,SOSTH FROM [\\Allnow\database\DATA.accdb].Erothseis ORDER BY rnd(INT(NOW*IDEROT)-NOW*IDEROT)”)

    comment:
    the Me.AREROT is the number of the randomize select records – you can put your number of records as you want – you can put this unbound textbox on your form

  3. Phil

    If using this method, is it possible to then perform a call to find the ID of the last record written?
    ie: intRowID = .OpenRecordset(“SELECT @@IDENTITY”)(0)
    If not, how can we do this, WITHOUT using linked tables?

  4. Josef Vesely

    Tested 5/7/2021 in Office365
    strSql = “INSERT INTO [C:\Users\…..\DATA.mdb].tbl_sometable ([Seller-Name]) VALUES (‘Smith’)”
    DoCmd.RunSQL strSql

  5. leschallier

    Hi, how do we insert into each table of a database other tables for several databases please?
    Is it possible to do it in a unique single sql query, or how with VBA?

    For example i have table1.db1, table1.db2, table1.db3. I want to insert table1.db1, table1.db2 into table1.db3.
    And i have 10 tables for each db.

    I am not a developer and it is quite difficult to figure out where to start.

    1. Daniel Pineault Post author

      It really depends on what you are doing, but why not simply add the tables as normal Linked Tables within your database and then you create interact with them normally, create queries, use VBA, …? Keep things simple, if you can, is always the best approach.