Make Table Query with path Parameter

A

aireq

I have an access database that has a number of nested queries that process
the tables exported from a piece of software we use in my office through
ODBC. At the most these queries are nested 7 queries deep, so including any
more queries to filter and sort the information to be included in a report I
start getting really slow performance at best, but usualy i just get "can not
connect to anymore database" errors.

So I've broken the project up into two databases. One to do the initial raw
processing, and the second which does any filtering, sorting for the final
reports.

I've used a "make table" query in the processing database to create/update
tables in the reporting database. For example here's an example of one of
those queries

SELECT
[Building IDs].*
INTO [Building IDs]
IN 'C:\Documents and Settings\eric.anastas\My Documents\Revit Master
Planning Tool\VPM Reports.accdb'
FROM [Building IDs];

However, there are a number of of these "make table" queries that need to
run. So I created a form with a button with the following VB script. Which
runs all my make table queries.

Private Sub exportTblsBtn_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Building IDs (Export Table)"
DoCmd.OpenQuery "Building Types (Export Table)"
DoCmd.OpenQuery "Gross Square Footage (Export Table)"
DoCmd.OpenQuery "Net Square Footage (Export Table)"
DoCmd.OpenQuery "Parking Spaces (Export Table)"
DoCmd.OpenQuery "Unit Counts (Export Table)"
DoCmd.OpenQuery "Building Phases (Export Table)"
DoCmd.SetWarnings True

End Sub

This works but the path to the reporting database to make the tables in is
still hard coded into each query. I would really like the user to be able to
enter a path into a text box next to the button.

I've tried referencing the text box on the form from the query like this

SELECT [Building IDs].* INTO [Building IDs] IN Forms!ExportFRM!exportPathBox
FROM [Building IDs];


But that gives me the error:

Could not find file 'C:\Documents and Settings\eric.anastas\My
Documents\Forms!ExportFRM!exportPathBox'


Does anyone know how I can make the database a make table query is creating
a table in a user controlled parameter?


Eric
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top