Table name as parameter in SQL query, with VBA...

[laurentc]

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

[Michael Kintner]

Just a quick note or hint, you want to make sure your field names do not
match key names. Such as Date is a key name which can conflict within a
query unless you rename your field to Contract_Date or enclose the Date
field within [], [Date].
"laurentc" <la*************@gmail.comwrote in message
news:11*********************@i42g2000cwa.googlegro ups.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

[laurentc]

Thanks for your hint Michael.

I do not have an issue with the field "date", as it is written as
"MyDate".

Anyway, anyone who is able to help me beginning the VBA code that
"should" be rather simple would help me a lot...

Regards,

Laurent


Michael Kintner wrote:

Just a quick note or hint, you want to make sure your field names do not
match key names. Such as Date is a key name which can conflict within a
query unless you rename your field to Contract_Date or enclose the Date
field within [], [Date].
"laurentc" <la*************@gmail.comwrote in message
news:11*********************@i42g2000cwa.googlegro ups.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

[pietlinden]

laurentc wrote:

Dear all,

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

Classic denormalized design. What happens if you need to add another
price for a Key/Date pair?

Hwever, 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.

another non-optimal design. Create ONE table containing all the data
and a flag field that tells you where the data came from (what import
file or whatever). I'm being picky, right? Well, consider that you
can create an unbound form that allows you to filter the records any
way you like and then you can base your reports etc on that.

>

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.

you can use RunSQL to create the tables, but why? Seems like a lot of
effort for no reason. What do you do with the data once it has been
processed?

[Kc-Mass]

I agree with those who say you should combine the tables with an ID of their
origin to keep them identifiable. That said you could use code similar to
that below to pick the table to query.

__________________________________________________ ___
Sub setquery()
Dim Db As Database
Dim RS As Recordset
Dim qry As QueryDef
Dim strQryFirstpart As String
Dim strQryWhole As String
Dim strTable As String
Dim strQuote As String
Dim strTableName As String
Set Db = CurrentDb
strQryFirstpart = "Select * from "
strTableName = WhichTable()
strQryWhole = strQryFirstpart & strTableName
Set qry = Db.CreateQueryDef("", strQryWhole)
Set RS = qry.OpenRecordset()
End Sub

Function WhichTable()
Dim strhandback As String
strhandback = InputBox("Which table do you want?", "Table to Query?",
"")
WhichTable = strhandback
End Function
__________________________________________________ ____________
"laurentc" <la*************@gmail.comwrote in message
news:11*********************@i42g2000cwa.googlegro ups.com...

Dear all,

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

[Smartin]

laurentc wrote:

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

Whether you choose to put all these separate data drops into one large
table or keep them separate my suggestion is to keep a table with one
column that enumerates the tables to be processed. You could use this
meta-table as it were as the record source for a combo box to streamline
your analytics, or macro/VBA code that handles appending all the
sub-tables into the master table. In either case you could look to the
meta-table for table names and build specific SQL statements (in code)
from that.

HTH

--
Smartin