Import data for all tables

  • Thread starter Thread starter Frank Situmorang
  • Start date Start date
F

Frank Situmorang

Hello,

If I want to import data from excel/access for consolidation of data, how
can we import all tables at once. I need it because of the following:

In members table there is a forein key for address table primary key. FK in
member is a required field and we need to import all data at once, otherwise
it will be rejected.

Anyone can help me how can we do it?.

Thanks in advance for any help provided.
 
Sorry, Frank, you are out of luck. There is no way to import multiple tables
"all at once".

However, what you need to do is relatively easy, once you do a thorough
review of your database structure. I would create a single proceedure (sub
or function), in put multiple lines of the docmd.Transferspreadsheet.

1. Identify all of your lookup tables (those tables which have a primary
key, but don't contain any foreign keys). Import these tables first.

2. Then identify those tables that only have foreign keys that relate to
the tables imported during step #1, and import them.

3. Lastly, identify the other tables, and import them in sequence, making
sure that all of the tables with PKs that are referenced as FKs have already
been imported.

This last step is generally the most difficult, but unless you have tables
with 5 or 6 FK fields, it should not be too bad. If you step through your
code, and go into debug mode when you encounter an error message, then you
can shuffle the lines of code until it works properly.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Frank Situmorang said:
Hello,

If I want to import data from excel/access for consolidation of data, how
can we import all tables at once. I need it because of the following:

In members table there is a forein key for address table primary key. FK in
member is a required field and we need to import all data at once, otherwise
it will be rejected.

Anyone can help me how can we do it?.

Thanks in advance for any help provided.
Click to expand...

Why not create one large query with all the fields from all the tables you
want. You may have to experiment with the relationship arrows to get the
desired records to display. Then you can use the built-in Office Link command
on the Access Tools menu to export to Excel.

-Brad
 
Frank said:
Hello,

If I want to import data from excel/access for consolidation of data,
how can we import all tables at once. I need it because of the
following:

In members table there is a forein key for address table primary key.
FK in member is a required field and we need to import all data at
once, otherwise it will be rejected.
Click to expand...

1. If you are importing then the information should already be in the tables
to be imported. If the foreign key is not there there is no way for anything
but a human to fill it in.
2. Tables are imported in a sequential manner, you can't do them all at the
same time.
 
Dale,

Your elaboration below is very useful and important for me. However since I
am not too good in Access, could you please help me by giving the sample of
your database?

When you said: create a single proceedure (sub
or function), in put multiple lines of the docmd.Transferspreadsheet. How I
would do it.

How if I asked all churchses that used my database send their backend
database which is in Access. Is it better to use Append?.

Thanks in advance for your help

Frank
 
Back
Top