Re: Make Table Name Parm

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 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,
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" wrote:

> On Mon, 9 May 2005 11:28:01 -0700, "BackinApps"
> <BackinApps@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> >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]
>
.

Relevant Pages

  • Re: how to read dbf file using macro?
    ... The 8 tells it to create Excel version Ofc97 or later. ... The "qryDBFDateForExcel" tells it which query or table to export data from. ... You will then have a spreadsheet with the column titles from the DBF ...
    (comp.databases.ms-access)
  • RE: Help with importing
    ... Dim strFullName As String ... Dim dbf As Database ... The make it an Append Query. ...
    (microsoft.public.access.modulesdaovba)
  • Use data from DBaseIV table in Excel 2007?
    ... Is there a query way to use data from a DBaseIV (.dbf) table in an Excel ... 2007 workbook? ...
    (microsoft.public.excel)
  • DBF linked tables
    ... We have FoxBASE program that would still have helping to run the information ... We linked the DBF table in to the new environments on Ms Access XP. ... The only problem that we have now is the speed when Ms Access query is ... Let say, that we have two DBF tables linked in to the access environment, we ...
    (microsoft.public.access.queries)
  • Re: is it worth creating multiple indexes?
    ... Keep in mind that indexes won't help during retrieval if the query optimizer ... > from a DBF background where one needed indexes on ... > bother with creating anything but a primary key". ... > a split sec when I use these secondary DBF indexes. ...
    (microsoft.public.sqlserver.msde)