Access: export data to another Access database

Question

Is it possible to export data to another Access file in Access, eg after clicking the button, the records will be entered? As a result, it will be a copy of the data. Thank you in advance for your answer.

Answer 1

To export data from multiple tables you have two options.  One would be to append the result table of a query which joins the tables into a single non-normalized table in the target database.  Using the tables in my ExportToExcel demo as an example the following query would append a row for the current contact in the frmContacts form to a linked table named Contacts_bak with columns ContactID, FirstName, LastName, LastName, City, Region, and Country:

INSERT INTO Contacts_bak (ContactID, FirstName,

LastName, LastName, City, Region, Country)

SELECT Contacts.ContactID, Contacts.FirstName, Contacts.LastName,

Contacts.Address, Cities.City, Regions.Region, Countries.Country

FROM (Regions INNER JOIN (Cities INNER JOIN Contacts

ON Cities.CityID = Contacts.CityID)

ON Regions.RegionID = Cities.RegionID) INNER JOIN Countries

ON Regions.CountryID = Countries.CountryID

WHERE Contacts.ContactID = Forms!frmContacts!ContactID;

The other option would be to export the rows into separate Contacts_bak, Cities_bak, Regions_bak, and Countries_bak linked tables, by executing separate append queries:

INSERT INTO Countries_bak (CountryID, Country)

SELECT Countries.CountryID, Countries.Country

FROM ((Regions INNER JOIN Countries

ON Regions.CountryID = Countries.CountryID)

INNER JOIN Cities ON Regions.RegionID = Cities.RegionID)

INNER JOIN Contacts ON Cities.CityID = Contacts.CityID

WHERE Contacts.ContactID = Forms!frmContacts!ContactID;

INSERT INTO Regions_bak (RegionID, Region, CountryID )

SELECT Regions.RegionID, Regions.Region, Regions.CountryID

FROM (Regions INNER JOIN Cities ON Regions.RegionID = Cities.RegionID)

INNER JOIN Contacts ON Cities.CityID = Contacts.CityID

WHERE Contacts.ContactID = Forms!frmContacts!ContactID;

INSERT INTO Cities_bak (CityID, City, RegionID)

SELECT Regions.RegionID, Regions.Region, Regions.CountryID

FROM Cities INNER JOIN Contacts ON Cities.CityID = Contacts.CityID

WHERE Contacts.ContactID = Forms!frmContacts!ContactID;

INSERT INTO Contacts_bak (ContactID, FirstName,

LastName, LastName, CityID)

SELECT ContactID, FirstName, LastName, Address,CityID

FROM Contacts

WHERE ContactID = Forms!frmContacts!ContactID;

In this example the queries must be executed in the above order, i.e. a row must be inserted into the referenced table in each relationship, before inserting rows into the referencing table.  If the relevant row already exists in any of the referenced tables Countries_bak and/or Regions_bak and/or Cities_bak then the query would fail due to the key violation in each case.  The final query would succeed, however, and would reference the existing rows in the referenced tables.

Answer 2

And how to create a query that references one current row that I want to join? The problem is that I have information about one thing stored in three tables and connected with each other.

Answer 3

However, I am looking for a similar solution as in your ExportToExcel file, where it is possible to transfer one record to another Access database.

The two are not comparable.  That was achieved through file operations, but Access tables are not files.  In any case it would be overcomplicating what can be done very simply.  All you need to do is create a link to the target table, then create an append query which appends data from the form's table to the linked target table, referencing a control, which can be hidden, bound to the primary key of the form's current record as a parameter.  This will restrict the result table of the query to a single row, so executing the query in the form's AfterInsert event procedure will insert the newly inserted row into the target table.

Answer 4

Thank you for your response. However, I am looking for a similar solution as in your ExportToExcel file, where it is possible to transfer one record to another Access database. Maybe you know where I can find such an example, or similar codes?

Answer 5

In the source database create a link to the target table in the external database.  Then create and execute an 'append' query to insert data from the source table to the target table via the link.