CODEWITHSUNDEEP
ms-accessvba
YvetteLee
YvetteLee

Reputation: 1090

MS Access export and import data

I have two backend databases. BE-Main and BE-User.

I want to export the data from one table in BE-User (save as file in disk) and import it in BE-Main using VBA. Since the BE-Main is the main one and the BE-User is secondary, I need to fill up data if ID is existing and add new ID (row) if ID doesn't exist.

Export part

DoCmd.TransferDatabase acExport, "Microsoft Access", ExtDb, acTable, "tblHealth", "tblHealth1", False

If I use the same code with import, access is making a new table because it exists.

Any idea how to Update/Refresh data in the table? Tables has a primary key (heathID) which I might can use with SQL.

Upvotes: 1

Views: 830

Answers (1)

Chris
Chris

Reputation: 254

An easier route might be to link the table and run your queries from there. They might be the same name to the coding below will allow you to change the name of the linked table. 

Public Sub ExportImportData()
    'Create the link table
    DoCmd.TransferDatabase _
            TransferType:=acLink, _
            DatabaseType:="Microsoft Access", _
            DatabaseName:="YourDatabaseLocationAndName.accdb", _
            ObjectType:=acTable, _
            Source:="TargetTableName", _
            Destination:="LinkTableName"
    'Updates data
    CurrentDb.Execute "UPDATE YourUpdateTableName " & _
                      "SET YourUpdateTableName.FieldName = LinkTableName.FieldName " & _
                      "WHERE(((YouUpdateTableName.ID) = LinkTableName.ID));"
                      'Add more fields if updating multiple fields
    'Appends new data
    CurrentDb.Execute "INSERT INTO YourUpdateTableName ( List out all fields ) " & _
                      "SELECT List Out all fields " & _
                      "FROM LinkTableName;"
    'Deletes link table
    DoCmd.DeleteObject acTable, "LinkTableName"
    End Sub

YourDatabaseLocationAndName = example: "C:\Users\username\desktop\MyDatabase.accdb"

targetTableName = The name of the table in the backend you are linking from

LinkTableName = a name given to your new linked table

Upvotes: 2

Related Questions