Import Query as Table

[Ed Bitzer]

I would appreciate some direction in the necessary VBA code to import a
query as a table. I can import the query as a query using
CreateQueryDef but have no idea where to start looking to import the
results of that query as a table.

Appreciate,

Ed

 

[Allen Browne]

If you are trying to do this through the interface under File | Get External
| Import, there is an option group where you can specify to import queries
as tables.

If you are trying to do this programmatically, turn the query into a Make
Table query. The query design grid can help you get the query statement:
Make Table on query menu, and you can specify the database to export to. The
switch the query to SQL View, and copy the statement to Execute.

 

[Ed Bitzer]

Allen,

I have been doing it through the interface and using the "import query
as table option" but would like to "automate." My tunnel vision was
looking for the VBA to do that operation programmatically but you make
me realize that I can create a Make Table that exports its output to
another database and of course Execute that Make Table. I understand up
to that point but not sure how to programmatically Execute from the
second database but assume the event must not only reference the query
but also include the complete filespec for the database. I have
Executed Word documents so suspect the syntax will be similar.

Thanks,

Ed

 

[Ed Bitzer]

Allen,

I'm sure my reference to Executing a Word document raised an eyebrow.
When I checked, I used a simply hyperlink to display the Word document.
I then remembered using the Call Shell (stAppName) but found that only
opened another database. I want to run a query. A little more digging
and I found "Setting a Reference in a VB Project" and this looks like I
should be able to call a subroutine in the database with my new Make
Table from the database where I want to use the data and thus kick the
new table over. Am I heading in the right direction?

Ed

 

[Ed Bitzer]

Allen,

I am very close - need another push. Make Table SQL Executes fine when
called from its home in database-1 creating table in database-2. Fails
indicating the Execute statement needs 2 parameters when called from
database-2 which has a reference set for database-1. The sub follows -
can you give me any more guidance.

Sub ExportPhoneLstToDialer()
Dim strSQL As String
Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb
strSQL = "SELECT tblMain.Last, tblMain.First, tblMain.Phone INTO
RivieraPhoneListforDialer IN 'E:\Docs-Mine\North
Valentine\MyPhoneDialer.mdb' FROM tblMain WHERE (((tblMain.Phone) Is Not
Null))"
' Create new QueryDef.
Set qdf = dbs.CreateQueryDef("", strSQL)
' Execute QueryDef.
qdf.Execute
Set dbs = Nothing
End Sub


Ed

 

[Dirk Goldgar]

I would appreciate some direction in the necessary VBA code to import
a query as a table. I can import the query as a query using
CreateQueryDef but have no idea where to start looking to import the
results of that query as a table.

Are you aware that the DoCmd.TransferDatabase method allows you to
export (and, I expect, import) a query as a table? IIRC, all you have
to do is specify acTable as the object type, but specify a stored query
as the object name.

 

[Allen Browne]

If you are doing this from the source database, you can just
dbEngine(0)(0).Execute "SELECT ... INTO ... IN ...

If you need to do this from the target database (not the source),
OpenDatabase() to get at the source, or just attach the table.

Dirk's suggestion of TransferDatabase should also work.

 

[Ed Bitzer]

Dick,

Was not aware and TransferDatabase did do the import of a select query
into a table with name of choice. That of course is what I was doing
manually but a pain.

I have not been able to get the Make Table in the "remote" database to
run from my "main" although as I indicated to Allen is does work if
executed in the "remote." "Registration" appears an interesting tool -
I just now have to find a use for it. In this particular example I
probably would be just as well off linking but always try to look at the
alternative. Now understand I am retired and therefore have the time.

Thanks guys,

Ed