[AccessD] RE: AccessD Digest, Vol 14, Issue 31

Dear Susan 

My 2 eurocents, after Jonh Colby's two dollars:

Assuming the following procedure:
For each period:
a. dump all files in a standard period directory
b. get access to read the directory until there are no files left
(probably it is usefull to read all the filenames in a table and use this
thable for further processing)
For each file in directory
c. attach each table to the database
d. run the append
Next file
Next period

Following the code for the append

'+*define vars
dim strInsertTable as string
dim strFromTable as string
dim strSQL, strSQLinsert, strSQLselect, strSQLfrom, strSQLcriteria as string
'-*define vars
'+*Initialise vars
strFromTable = "tblFromData"
strInsertTable = "tblInsertedData"
strSQL =""
strSQLinsert = ""
strSQLselect = ""
strSQLfrom = ""
strSQLcriteria = ""
'-*Initialise vars

'create query string
strSQLinsert = strSQLinsert & " INSERT INTO [" & strInsertTable & "] (
SourceTableName, CustID, CustName ) "
strSQLselect = strSQLselect & " SELECT "
strSQLselect = strSQLselect & "'" & strFromTable & "' AS FromTable, "
strSQLselect = strSQLselect & "[" & strFromTable & "].CustID, " 
strSQLselect = strSQLselect & "[" & strFromTable & "].CustName "
strSQLfrom = strSQLfrom & " FROM " & strFromTable

strSQL = strSQLinsert & strSQLselect & strSQLfrom & strSQLcriteria & ";"

'attach string strSQL to a querydef and run it
' Note: I usually use a hidden tmpQuery for this and set its SQL property to

' CurrentDb.QueryDefs("tmpQuery").SQL = strSQL
' CurrentDb.QueryDefs("tmpQuery").Execute
' Or shorter:
DoCmd.SetWarnings False 'suppress warnings about action queries
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True 'switch warning on again

Notes:
- the barckets [] are included to allow names with spaces and other special
characters
- the apostrophe between quotes "'" followed by the tablename and closed
again with "' should return the tablename between quotes.
- As noted by another listmember, you probably have to do some checking on
data quality. Personally I would push that to the delivering agents, e.g. by
spending some time developing consistency checking in the Excel templates
they use for delivery.


Regards

Harry Coenen


>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
>John W. Colby
>Sent: Tuesday, April 20, 2004 4:12 AM
>To: Access Developers discussion and problem solving
>Subject: RE: [AccessD] RE: AccessD Digest, Vol 14, Issue 31
>
>
>Susan,
>
>The way that I deal with this is that I COPY the file to a common name,
>perhaps CountyData.XLS.  Then I link that file to the FE.  
>Build my queries
>to suck the data out.  Run them.  Then there is just a copy of 
>whatever the
>next file name is to CountyData.XLS and run the queries over 
>again.  You
>need a means of interpreting some part of the original file 
>name so that you
>can set the "county field".  Do that 71 times and you are 
>done.  One table
>name, one set of queries, one function for pulling the county 
>name into an
>aliased query field, one routine that copies the file to a 
>common name /
>location.
>
>Is this a manual process?  Is there a naming convention in the 
>excel files?
>
>If not just throw all of the excel files in a directory, copy 
>each one out
>to an archive directory, adding the yyyymmdd string to the 
>name, either at
>the beginning or the end (helps sort the files in date order). 
> Also do the
>copy to a common location / name.  Now write an iterator that 
>finds every
>file in the directory and copies it to archive / common name - 
>location,
>deleting the file in the original directory.  When no files 
>remain, you are
>done.  In order to do this you must have some identifying 
>string in the file
>name however, perhaps the county name as the first N characters.  That
>allows the code to build the "missing" field that is the county in the
>destination table.
>
>
>John W. Colby
>www.ColbyConsulting.com
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Klos, Susan
>Sent: Monday, April 19, 2004 3:40 PM
>To: 'accessd at databaseadvisors.com'
>Subject: [AccessD] RE: AccessD Digest, Vol 14, Issue 31
>
>
>Robert, I need 71 identical tables because I am getting the 
>same structure
>Excel file from 71 districts.  The structure is the same but 
>the data is
>different.  I want to be able to loop through the tables and 
>append their
>records to one table.
>
>Susan Klos
>Senior Database Analyst
>Evaluation and Reporting
>Florida Department of Education
>
>
>
>-- 
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>