Re: Make Table Name Parm
- From: "BackinApps" <BackinApps@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 10 May 2005 10:00:02 -0700
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
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 8-) ]
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,
strSQL = strSQL & " Sos0205_M.WARD, Sos0205_M.PCT , Sos0205_M.RD_MM,
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,
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,
strSQL = strSQL & "Sos0205_M.feb03, Sos0205_M.apr03, Sos0205_M.mar02,
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]; "
I called it with this query code...
SELECT Create_Table([«strTableName»],[«strTWS»]) AS Expr1
but get error message that debug indicates my DoCmd.RunSQL with strSQL won't
work. so close yet so far....
G. Jay Myatt, Jr.
Cook County Clerks Office
"John Vinson" wrote:
> On Mon, 9 May 2005 11:28:01 -0700, "BackinApps"
> <BackinApps@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> >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
> 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]
- Re: Make Table Name Parm
- From: John Vinson
- Re: Make Table Name Parm
- Prev by Date: RE: don't want to save the query
- Next by Date: Re: I have some questions on calculations,IIF,etc.
- Previous by thread: Re: Make Table Name Parm
- Next by thread: Re: Make Table Name Parm