Import table as query

[strauss.sean]

Hi! I am trying to import a query as a table from a MS
Access database in a specified drive, path, and filename; my filenames
and paths are being stored in a table for easy reconfiguration.
Anybody done this before? Please let me know! Thanks, Sean

BTW: I have learned that if I wanted to import a table, this is how
it's done:
DoCmd.TransferDatabase acImport, "microsoft access",
"C:\Folder\file.mdb", acTable, "sourcetable", "destinationtable"

[Larry Linson]

If you would explain what you are trying to accomplish, rather than a
specific way you are trying to do it, someone might be able to offer a
worthwhile suggestion.

For example, I have usually found that linking to an external table, and
doing something other than creating a new table in my database (e.g., an
append query to append new records, or update query) has been better for me
than creating new tables at runtime.

When I do create temporary tables at runtime, I've found it is more
effective to create a temporary database to contain the temporary tables --
that eliminates the deletion of those tables bloating my database; I can
just delete the temporary database file and the temporary tables vanish
along with it.

Larry Linson
Microsoft Access MVP
<st**********@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Hi! I am trying to import a query as a table from a MS
Access database in a specified drive, path, and filename; my filenames
and paths are being stored in a table for easy reconfiguration.
Anybody done this before? Please let me know! Thanks, Sean

BTW: I have learned that if I wanted to import a table, this is how
it's done:
DoCmd.TransferDatabase acImport, "microsoft access",
"C:\Folder\file.mdb", acTable, "sourcetable", "destinationtable"

[strauss.sean]

Larry:

Thanks for the suggestion. Due to the nature of the situation (numbers
and other data can be updated up to a certain point; beyond that, they
are "written in stone", and updated for their own databases only, not
the one I'm working on, which will then become "historic"), importing
is the way to go, rather than linking. Since the info comes from
multiple tables, I need to import a query as a table -- actually, I
need to do a few of these.

If I could do it another way, I would...
Can you help me with my request? Can anyone? Thanks again,

Sean

[Larry Linson]

Sean,

I just did what you want to do, to refresh my memory on importing tables.

It worked just fine, to obtain a table from an Access 2003 database and
create a new one with the same content but a different name in an Access
2002 database. I used literals in the DoCmd.TransferDatabase, but you can
write code to make it more flexible. What did you need help with,
specifically?

If you execute the DoCmd.TransferDatabase method twice without deleting the
table, the second will create another new table, and append a 1 to the table
name you specify. Thus if you have tblMakeANew, the next one will be
tblMakeANew1, but the DoCmd.TransferDatabase will not fail because of the
existing table.

The path and filename of the source database is a string... it can be stored
in a string variable. That string variable could be populated in a number of
ways... one of which might be DAO (or ADO) code that reads your table -- if
you always want to import every table in the list or if you have criteria
that you can apply to select which tables you want to import; another might
be a ComboBox whose RowSource is a Query that returns the information from
your table and allows the user to select -- your requirements will determine
which would be appropriate.

Clarify what you need and maybe someone can be of help.

Larry Linson
Microsoft Access MVP
<st**********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Larry:

Thanks for the suggestion. Due to the nature of the situation (numbers
and other data can be updated up to a certain point; beyond that, they
are "written in stone", and updated for their own databases only, not
the one I'm working on, which will then become "historic"), importing
is the way to go, rather than linking. Since the info comes from
multiple tables, I need to import a query as a table -- actually, I
need to do a few of these.

If I could do it another way, I would...
Can you help me with my request? Can anyone? Thanks again,

Sean

[strauss.sean]

You bet -- thanks for being willing to help.

I have a table; its fields are filename, filepath, query. I have a
form that lists (in Single Form format) whichever record is selected,
and there is a button on the form which I want to click that will
import that selected query from its specified file and filepath.
(Particularly, I want the query results imported as a table in the
database that's running.) Also, I want to be able to automatically
import several queries as tables; I know that programming that will
come next after I understand how to do it one-at-a-time. All databases
are MS Access 2000.

It seems simple enough -- I'm new to VB, however, and I'm not sure how
to write the lines that will do the importing. I understand defining
strings as variables, and I know the line of code that would import a
specific file. I don't know how to code to specify the value of an
existing record as a string; in other words, how do I say variable1 =
thisrecord of suchandsuch field. Also, I don't know the code for
importing query results as a table; I only know how to import a table.
Thanks,

Sean

[strauss.sean]

You bet -- thanks for being willing to help.

I have a table; its fields are filename, filepath, query. I have a
form that lists (in Single Form format) whichever record is selected,
and there is a button on the form which I want to click that will
import that selected query from its specified file and filepath.
(Particularly, I want the query results imported as a table in the
database that's running.) Also, I want to be able to automatically
import several queries as tables; I know that programming that will
come next after I understand how to do it one-at-a-time. All databases
are MS Access 2000.

It seems simple enough -- I'm new to VB, however, and I'm not sure how
to write the lines that will do the importing. I understand defining
strings as variables, and I know the line of code that would import a
specific file. I don't know how to code to specify the value of an
existing record as a string; in other words, how do I say variable1 =
thisrecord of suchandsuch field. Also, I don't know the code for
importing query results as a table; I only know how to import a table.
Thanks,

Sean