Make Table Name Parm

[Guest]

I have a Make Table query that is used to subset records from one master
table to 40 smaller ones. The problem is that I do not want to have to make
40 copies of this query to get 40 different table names. How can I make the
output table name input as a variable. I want to supply the new table name
when I run the make table query.

Thanks
--
G. Jay Myatt, Jr.
IT Specialist
Cook County Clerks Office
Chicago, ILL

312-603-1123 Phone
312-603-0982 Fax

 

[Guest]

Create a mudule with this function

Function Create_Table(strTableName)
Dim strSQL as string

strSQL="SELECT MyTable.* INTO " & strTableName & " FROM Client_tab "
strSQL=strSQL & "Where MyField='MyValue'" ---> replace it with your filter

docmd.runsql strSQL
end Function

You can copy your SQL statement from your original query and paste it for
strSQL, the only important change will be the location of strTableName, every
time you call this function you can supply the new table name,

 

[Guest]

Ok Thanks I'll give it a try...
--
G. Jay Myatt, Jr.
IT Specialist
Cook County Clerks Office
Chicago, ILL

312-603-1123 Phone
312-603-0982 Fax

 

[Guest]

I am still new to this .. do I replace each strSQL= with a line of the the
SQL statement generated by my origianl query or do I place it after where
your arrows are in the example... ?
--
G. Jay Myatt, Jr.
IT Specialist
Cook County Clerks Office
Chicago, ILL

312-603-1123 Phone
312-603-0982 Fax

 

[John Vinson]

I have a Make Table query that is used to subset records from one master
table to 40 smaller ones. The problem is that I do not want to have to make
40 copies of this query to get 40 different table names. How can I make the
output table name input as a variable. I want to supply the new table name
when I run the make table query.

Thanks

Step back a bit.

Storing your data redundantly in 41 tables would be a possible
solution to a really, really serious performance problem; but it would
be a solution that I would be dragged kicking and screaming before I'd
do it.

If you want to subset records, consider putting an index on the field
or fields which define the subsets (so the queries will be more
efficient) and use a paramter Query (just one query!) to extract the
subsets.

You can edit your tables - causing the data in your main table to be
WRONG and inconsistant; you can edit data in the Query, which edits
the main table and maintains consistancy.
You can base Reports on your tables - or on your queries.
You can sort your tables. You can sort the query too.
You can apply additional criteria to the tables. Same with the query.
You can export your tables. You can export the queries.

Do you have any GOOD reason to do this? If so I'd be very curious to
hear it!

John W. Vinson[MVP]

 

[Guest]

John:
Yes the reason I have to do this is to create 48 different output tables.
The main table is all of the election results for the county. The query is a
maketable query that creates a subset of results for each township in the
county. These smaller tables (in .dbf format) are then useable (in Excel and
Word) by various public officials and political organizations that have
little to no programming skills. Hence all the work on my end to make it
simple for them.
Data is already coded by township but the problem is that Access stores the
name of the table in the make table query and it needs to be unique for each
subsetted file. I don't want to have to create 48 different queries when I
can supply the name of the output file as well as select the township code
that I want.

--
G. Jay Myatt, Jr.
IT Specialist
Cook County Clerks Office
Chicago, ILL

312-603-1123 Phone
312-603-0982 Fax

 

[John Vinson]

John:
Yes the reason I have to do this is to create 48 different output tables.
The main table is all of the election results for the county. The query is a
maketable query that creates a subset of results for each township in the
county. These smaller tables (in .dbf format) are then useable (in Excel and
Word) by various public officials and political organizations that have
little to no programming skills. Hence all the work on my end to make it
simple for them.
Data is already coded by township but the problem is that Access stores the
name of the table in the make table query and it needs to be unique for each
subsetted file. I don't want to have to create 48 different queries when I
can supply the name of the output file as well as select the township code
that I want.

It is NOT NECESSARY TO CREATE 48 TABLES to do this.

You can export to Excel from a Select Query just as easily as from a
table - and it's perfectly easy to include the township code in the
query. This can be a single parameter query.

John W. Vinson[MVP]

 

[Guest]

John:
the problem is not in creating the output formats.. (our standard is *.dbf)
but in naming the output files. You have to name the file when you create the
query. I want one query that will allow me to input the selection criteria
and the name of the output file.
--
G. Jay Myatt, Jr.
IT Specialist
Cook County Clerks Office
Chicago, ILL

312-603-1123 Phone
312-603-0982 Fax

 

[Guest]

jl:
I have created function as you suggested.. syntatically it is ok.. but i
cannot get it to run in either a query or a macro. how can I call this so
that it runs?
--
G. Jay Myatt, Jr.
IT Specialist
Cook County Clerks Office
Chicago, ILL

312-603-1123 Phone
312-603-0982 Fax

 

[John Vinson]

John:
the problem is not in creating the output formats.. (our standard is *.dbf)
but in naming the output files. You have to name the file when you create the
query. I want one query that will allow me to input the selection criteria
and the name of the output file.

AFAIK you'll need to use VBA code to do this, since a Query *BY
ITSELF* knows nothing about Excel or exporting. The query (or a table
for that matter) is simply a recordset object, which can be exported -
but the name of the exported object must be supplied from outside the
query.

I'm confused though; you say you're exporting the data to .dbf (dBase)
files in order to use them in Excel. Why not cut out the middleman?
Excel can use DAO or ODBC to connect directly to an Access query; the
data can be stored in Access and used in Excel, without the added step
of exporting to dBase or to an Access native table.

What you'll need to do, if you are committed to this approach, is to
create a flexible VBA function which can receive the desired .dbf file
name (and perhaps the criteria to select the records) as paramters,
which would then use the TransferDatabase method to export the query
to .dbf, or (perhaps better) the TransferSpreadsheet method to export
directly to an Excel workbook. See the VBA help for these two methods
for examples, and post back if you need more help!

John W. Vinson[MVP]

 

[Guest]

John:
we create the 48 subset files for each township and export them to .dbf
format for each township. When 'clients' request voter registration records
we then burn the .dbf files along with others to a cdrom and provide them
with the data. Since .dbf is a common denominator file structure we do not
have to worry about what software the 'clients' are using on the other end.
They are able to import it into whatever they want... including excel or back
into access.

All I want to do is without writting 48 different queries break the large
table I created from the comma delimited file into 48 different tables (one
for each towhship) so that I can then export them into 48 different .dbf
files that can be given to our 'clients', who may or maynot have (most of the
time don't) any comfortablity with the magic box. [Most of the time have to
walk them through finding the files on the CD-ROM ]

I have this code in a module but have not figured out how to get it to run.

Function Create_Table(strTableName As String, strTWS As String)
Dim strSQL As String

strSQL = "SELECT Sos0205_M.ID, Sos0205_M.CONG, Sos0205_M.LEG,
Sos0205_M.REP, Sos0205_M.TOWNSHIP,"
strSQL = strSQL & " Sos0205_M.WARD, Sos0205_M.PCT , Sos0205_M.RD_MM,
Sos0205_M.RD_DD,"
strSQL = strSQL & " Sos0205_M.RD_YY, Sos0205_M.LAST, [first] & "" "" &
[suf] AS FIRST_NAME,"
strSQL = strSQL & " Sos0205_M.ADDRESS, Sos0205_M.CITY, Sos0205_M.ZIP,
Sos0205_M.SEX, "
strSQL = strSQL & "Sos0205_M.BD_MM, Sos0205_M.BD_DD, [bd_cc] & [bd_yy]
AS BD_YR, "
strSQL = strSQL & "Sos0205_M.PHYSIMP, Sos0205_M.feb05, Sos0205_M.mar04,
Sos0205_M.nov04, "
strSQL = strSQL & "Sos0205_M.feb03, Sos0205_M.apr03, Sos0205_M.mar02,
Sos0205_M.nov02, "
strSQL = strSQL & "Sos0205_M.feb01, Sos0205_M.apr01 "
strSQL = strSQL & "INTO " & strTableName & " FROM Sos0205 "
strSQL = strSQL & "WHERE Sos0205_M.TOWNSHIP = " & strTWS
strSQL = strSQL & "ORDER BY Sos0205_M.PCT, Sos0205_M.LAST,
Sos0205_M.ADDRESS, [first] & " ' '" & [suf]; "

DoCmd.RunSQL strSQL
End Function

I called it with this query code...

SELECT Create_Table([«strTableName»],[«strTWS»]) AS Expr1
FROM Sos0205_M;

but get error message that debug indicates my DoCmd.RunSQL with strSQL won't
work. so close yet so far....


--
G. Jay Myatt, Jr.
IT Specialist
Cook County Clerks Office
Chicago, ILL

312-603-1123 Phone
312-603-0982 Fax

 

[John Vinson]

I called it with this query code...

SELECT Create_Table([«strTableName»],[«strTWS»]) AS Expr1
FROM Sos0205_M;

but get error message that debug indicates my DoCmd.RunSQL with strSQL won't
work. so close yet so far....

The function looks like it should work - but I would not try running
it *from a Query*. Can you perhaps put two unbound textboxes on a Form
for strTableName and strTWS, and run the function from a command
button on the form?

Private Sub cmdRunQuery_Click()
Call CreateTable(Me!txtStrTableName, Me!txtStrTWS)
End Sub

It may be valuable to open the function in the VBA editor and put a
"breakpoint" in it before you start defining strSQL (click the mouse
in the grey bar to the left of the code window, a brown dot will mark
the breakpoint). Run the function; the code will stop at that point,
and you can hit F8 (or use the menu Debug options) to step through the
code line by line. See if strSQL actually contains what you expect.


John W. Vinson[MVP]

 

[Guest]

John:
Making progress

I created form as you suggested and added the code you gave me on the
command button - on click

Private Sub Command4_Click()
Call Create_Table(Me!txtStrTableName, Me!txtStrTWS)
End Sub


Then I added break point as you suggested included watches on the data
values and stepped thru the code. I am now getting Data Type Mismatch in
Criteria Experssion - run time error 3464:

Here is the code now in my function Create_Table -
Function Create_Table(strTableName As String, strTWS As String)
Dim strSQL As String

strSQL = "SELECT Sos0205_M.ID, Sos0205_M.CONG, Sos0205_M.LEG,
Sos0205_M.REP, Sos0205_M.TOWNSHIP,"
strSQL = strSQL & " Sos0205_M.WARD, Sos0205_M.PCT , Sos0205_M.RD_MM,
Sos0205_M.RD_DD,"
strSQL = strSQL & " Sos0205_M.RD_YY, Sos0205_M.LAST, [first] & ' ' &
[SUF] as First_Name,"
strSQL = strSQL & " Sos0205_M.ADDRESS, Sos0205_M.CITY, Sos0205_M.ZIP,
Sos0205_M.SEX, "
strSQL = strSQL & "Sos0205_M.BD_MM, Sos0205_M.BD_DD, [bd_cc] & [bd_yy]
AS BD_YR, "
strSQL = strSQL & "Sos0205_M.PHYSIMP, Sos0205_M.feb05, Sos0205_M.mar04,
Sos0205_M.nov04, "
strSQL = strSQL & "Sos0205_M.feb03, Sos0205_M.apr03, Sos0205_M.mar02,
Sos0205_M.nov02, "
strSQL = strSQL & "Sos0205_M.feb01, Sos0205_M.apr01 "
strSQL = strSQL & "INTO " & strTableName & " FROM Sos0205_M "
strSQL = strSQL & "WHERE Sos0205_M.TOWNSHIP = " & strTWS & " "
strSQL = strSQL & "ORDER BY Sos0205_M.PCT, Sos0205_M.LAST,
Sos0205_M.ADDRESS;"

DoCmd.RunSQL strSQL
End Function


Can you think of why I am getting this error...

--
G. Jay Myatt, Jr.
IT Specialist
Cook County Clerks Office
Chicago, ILL

312-603-1123 Phone
312-603-0982 Fax

I called it with this query code...

SELECT Create_Table([«strTableName»],[«strTWS»]) AS Expr1
FROM Sos0205_M;

but get error message that debug indicates my DoCmd.RunSQL with strSQL won't
work. so close yet so far....

The function looks like it should work - but I would not try running
it *from a Query*. Can you perhaps put two unbound textboxes on a Form
for strTableName and strTWS, and run the function from a command
button on the form?

Private Sub cmdRunQuery_Click()
Call CreateTable(Me!txtStrTableName, Me!txtStrTWS)
End Sub

It may be valuable to open the function in the VBA editor and put a
"breakpoint" in it before you start defining strSQL (click the mouse
in the grey bar to the left of the code window, a brown dot will mark
the breakpoint). Run the function; the code will stop at that point,
and you can hit F8 (or use the menu Debug options) to step through the
code line by line. See if strSQL actually contains what you expect.


John W. Vinson[MVP]

 

[John Vinson]

strSQL = strSQL & "WHERE Sos0205_M.TOWNSHIP = " & strTWS & " "

This line is the source of the error.

Since TOWNSHIP is a Text field (apparently), you need the
syntactically required quotemarks. Just in case there might be a
township with an apostrophe in its name, use the " character, ASCII
code 34, as the delimiter:

strSQL = strSQL & "WHERE Sos0205_M.TOWNSHIP = " & Chr(34) & strTWS
& Chr(34) & " "

(all on one line of course).

John W. Vinson[MVP]

 

[Guest]

John:
That was the Answer!!! Thanks... it runs great... this will allow me to put
together a great system... and thanks for putting up with me and my lack of
knowledge...

--
G. Jay Myatt, Jr.
IT Specialist
Cook County Clerks Office
Chicago, ILL

312-603-1123 Phone
312-603-0982 Fax