So you could do this with a function like the following UNTESTED AIR
CODE. Test this out on a copy of your data and see if it works.
Public Function fPopulateData()
Dim strSQL as String
Dim strTableName as String
Dim iLoop as Long
Dim DbAny as DAO.Database
Set DbAny = CurrentDB()
FOR iLoop = 1 to 28
strTableName = "GLT01_" & Format(iLoop,"00")
StrSQL = "SELECT infile.DocNbr, Max(infiile.VndrNbr)" & _
" INTO tblDocVndr From " & strTableName & " as infile" & _
" GROUP BY infile.Docnbr"
DbAny.Execute strSQL, dbFailOnError
strSQL = "SELECT infile.CoCd, Right([Acctnbr,6]) AS AcctNbr" & _
", infile.Period, infile.Amt, infile.VndrNbr " & _
" INTO tblVndrAmts FROM " & strTableName & " as infile"
DbAny.Execute strSQL, dbFailOnError
Next iLoop
'If you want to delete the tables after this then use
For iLoop = 1 to 28
strTableName = "GLT01_" & Format(iLoop,"00")
DbAny.TableDefs.Delete strTableName
Next iLoop
End Function
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
John,
Thanks so much for responding.
My queries are:
qry001DocVndr
SELECT infile.DocNbr, Max(infiile.VndrNbr) INTO tblDocVndr
From infile
GROUP BY infile.Docnbr;
qry002VndrAmts
SELECT infile.CoCd, Right([Acctnbr,6]) AS AcctNbr, infile.Period,
infile.Amt, infile.VndrNbr INTO tblVndrAmts
FROM infile;
My tables are linked as GLT01_01, GLT01_02, etc. The names are for General
Ledger Transactions_Month_Week. I currently have 28.
So, I rename GLT01_01 to Infile and run the queries. Then I delete
Infile(GLT01_01) and rename GLT01_02 to Infile, run the queries, etc. After
I get these to reconcile I append them to them to a perpetual table.
I really appreciate you looking at this.
:
That does sound like the hard way to do this. If you build the query
string in code and execute the query string you should have little problem
Without more detail, the general idea is
Public Function InFileQuery(strTableName as String)
Dim strSQL as String
'strTableName is the name of the table to use in the query
StrSQL = "SELECT InFile.* FROM [" & strTableA & "] as InFile"
'Now use that string to get a recordset, assign as the SQL to a query, etc.
End Function
If you need more help you need to tell us what the two queries you run
do. Copy and paste the SQL of the queries you are currently using.
(Open in design view, select View: SQL from the menu, copy and paste
into your post)
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
GracieLou wrote:
Hi,
I’m using Access 2003.
I have read a lot of the postings out there about renaming tables.
Unfortunately, I do not think any of them address what I am attempting.
I have nearly 30 tables linked into my db. I run 2 queries on each table to
get what I need. I use the same table name in the queries. I rename the
first linked table to Infile. I run the 2 queries, delete the table Infile
and then rename the next table Infile, run my 2 queries, delete and rename
the table, etc.
Is there code I can write that will run the queries, delete the table
Infile, rename the next linked table to Infile and then run the 2 queries
again?
All help is appreciated.
