using TransferDatabase action to import

[Slez via AccessMonster.com]

This is going to be my first attempt to import data from one Access database
to another. While it may be a simple and routine task, having never done it
has me unsure of what to do. I really appreciate any guidance anyone has to
offer!

I want to import a recordset based on the field JobNumber and anther field
BidStatus. I have identified a total of 17 fields from 3 different tables in
my source database that I want to import into my destination database. They
are select fields from each table, not the entire table. The field
properties in each database match as I know they need to. My vision is that
I'll have a form in the destination database that allows me to select or
enter the JobNumber, and based on records where BidStatus = "Awarded" (query?)
, when a command button is clicked, the macro would run and import the
related data.

Hopefully that makes sense. With that said, my questions are:
1. Do I have to first create a query in either database, or is there an
action in the macro that takes care of that?
2. I don't want to import an entire table. Do I have to? Or do I use some
actions that would identify each individual field? If so, which action do I
use?
3. Does the BidStatus field need to exist in my destination database? (I
wouldn't need this field once the data is imported.)

Being a rookie at this, I appreciate any step-by-step advice, or even some
similar examples. My hope is that this gives me at least a good start. I
expect to have more specific questions.
Thanks in advance!

 

[Guest]

TransferDatabase will import a subset of a table. It only imports the entire
table. My suggestion would be that rather than importing, you link to the 3
table in the other database that you want the data from. Then you could use
a query or queries to populate the tables in you destination database.
So,
Use TransferDatabase to link to the table.
Use queries to move the data.
Use DeleteObject to remove the links. The DeleteObject does not affect the
table in the source database, it only removes the link.

 

[Slez via AccessMonster.com]

Thanks for the response!
I have some follow up questions:
1. Do I need to run the TransferDatabase action 3 times? (Once for each of
the 3 tables I would like to link.)
2. There doesn'r seem to be a "RunQuery" action. I am assuming OpenQuery is
what I want.(?)
3. What action then moves the data from the query to the destination table?

Thanks once again!
Slez

TransferDatabase will import a subset of a table. It only imports the entire
table. My suggestion would be that rather than importing, you link to the 3
table in the other database that you want the data from. Then you could use
a query or queries to populate the tables in you destination database.
So,
Use TransferDatabase to link to the table.
Use queries to move the data.
Use DeleteObject to remove the links. The DeleteObject does not affect the
table in the source database, it only removes the link.

This is going to be my first attempt to import data from one Access database
to another. While it may be a simple and routine task, having never done it

[quoted text clipped - 24 lines]

expect to have more specific questions.
Thanks in advance!

 

[Guest]

Thanks for the response!
I have some follow up questions:
1. Do I need to run the TransferDatabase action 3 times? (Once for each of
the 3 tables I would like to link.)

Yes, you will need an action for each table you want to link

2. There doesn'r seem to be a "RunQuery" action. I am assuming OpenQuery is
what I want.(?)

The OpenQuery is for Select queries. You want to use Docmd.RunSQL which is
for Action queries.

3. What action then moves the data from the query to the destination table?

The query does it. Action queries include Delete, Update, and Append. Any
query the affects data in a table is an Action query. A Select query is only
a view of the data as it exists. It does not affect the data; however,
Select queries can be used as record sources for forms which will then modify
the data, but it is the action of the form and not the query that modifies
the data.

Thanks once again!
Slez

TransferDatabase will import a subset of a table. It only imports the entire
table. My suggestion would be that rather than importing, you link to the 3
table in the other database that you want the data from. Then you could use
a query or queries to populate the tables in you destination database.
So,
Use TransferDatabase to link to the table.
Use queries to move the data.
Use DeleteObject to remove the links. The DeleteObject does not affect the
table in the source database, it only removes the link.

This is going to be my first attempt to import data from one Access database
to another. While it may be a simple and routine task, having never done it

[quoted text clipped - 24 lines]

expect to have more specific questions.
Thanks in advance!