By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,233 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

exporting records in multiple table databases

P: 1

I use access 2003 and attempting to export/copy records between two access databases (almost identical) with multiple tables (both databases having the same relations between the tables).
I have no problems exporting records with an addition query for a single table into a singel table of the other database.
However, i would like to create a function to export the records in one go from the multiple tables in the first database to the multiple tables in the second.
I tried combining SQL of the single addition queries as well as creating a macro, but it doesn't work.

This exporting of records has to be done repeatedly an manual because of privacy affairs.

Tnx in advance
Apr 22 '09 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 489
Here is an example of how you could step through the tables in your database and using the table name run a specific sql statement.

Expand|Select|Wrap|Line Numbers
  1. Dim tbl As TableDef, stSQL as string
  2. Dim db As DAO.Database
  3. Set db = CurrentDb()
  4. For Each tbl In db.TableDefs
  5.     SelectCase
  6.     Case is = "Table1"
  7.         stSQL = "Enter Your SQL Here"
  8.     Case is = "Table2"
  9.         stSQL = "Enter Your SQL Here"
  11.     Continue Cases with all your table names here
  13.     End Select
  14.     DoCmd.RunSql stSQL
  15. Next tbl
Apr 22 '09 #2

Expert Mod 15k+
P: 31,277
You could set up a FrontEnd (FE) database that used both of the other databases as BackEnds (BEs).

This FE would copy from one to the other using simple SQL (QueryDefs whatever). You could even link a whole bunch of them together in sequence very similarly to how Don has already illustrated.

Welcome to Bytes!
Apr 23 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.