Table name as parameter in SQL query built with VBA

[laurentc via AccessMonster.com]

Dear all,

I have several tables based on exactly the same fields
(Key/Date/Price1/Price2).
I do some statistics on the prices.

However, as I have many different tables (the tables are different because I
directly import the data from a .csv file), I do not want to create dozens of
queries to be able to get the results of the different tables.


Therefore, I would like to create a query in Access that will ask me the
table name from which to get the data each time I need.


Unfortunately, I find I cannot pass a table name as a Parameter to a query.

I have to have to program a VBA code to construct a SQL string.
However, I cannot manage to build that code, as I am a beginner in VBA.

I would appreciate any help, hints or suggestions.

Thanks,

Laurent

 

[Douglas J. Steele]

Assuming you know how to generate the SQL string and write it to a variable
strSQL, try:

CurrentDb().QueryDefs("NameOfQuery").SQL = strSQL

(Replace "NameOfQuery" with the name of your actual query, in quotes)

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "CreateQueries2.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

I gather from your posting that Douglas' assumption that you know how to
generate a SQL string is unlikely. Another option is for your query (say
called Query1) to interegate a 'dummy' table, called, say, 'NewTable' then
the coding could look like:

DoCmd.RunSQL "SELECT * INTO NewTable FROM " & InputBox("What is the table
name?")

DoCmd.OpenQuery "Query1"

What will happen is that the coding will copy all the data in your new table
into NewTable and then open it using your existing query. You just need to
change your existing query to look at 'NewTable'.

Hope this helps.

Assuming you know how to generate the SQL string and write it to a variable
strSQL, try:

CurrentDb().QueryDefs("NameOfQuery").SQL = strSQL

(Replace "NameOfQuery" with the name of your actual query, in quotes)

 

[laurentc via AccessMonster.com]

Dear Roger, BeWyched and Douglas,

Thanks a lot for your great replies.

As well understood by BeWytched, I fear i am not really able to try Douglas
proposal as soon as now (I hope I will be able in the near future, however...)


BeWytched : your solution seems excellent and well adapted to my VBA level. I
am going to it as you eaxplained it.

Roger, I am also going to check your proposal.


Anyway : thanks a lot your answers really helpful.

Regards,

Laurent

I gather from your posting that Douglas' assumption that you know how to
generate a SQL string is unlikely. Another option is for your query (say
called Query1) to interegate a 'dummy' table, called, say, 'NewTable' then
the coding could look like:

DoCmd.RunSQL "SELECT * INTO NewTable FROM " & InputBox("What is the table
name?")

DoCmd.OpenQuery "Query1"

What will happen is that the coding will copy all the data in your new table
into NewTable and then open it using your existing query. You just need to
change your existing query to look at 'NewTable'.

Hope this helps.

Assuming you know how to generate the SQL string and write it to a variable
strSQL, try:

[quoted text clipped - 29 lines]