Renaming tables

G

GracieLou

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.
 
J

John Spencer

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
'====================================================
 
G

GracieLou

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.

John Spencer said:
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
'====================================================

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.
Click to expand...
Click to expand...
 
J

John Spencer

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.

John Spencer said:
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
'====================================================

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.
Click to expand...
Click to expand...
Click to expand...
 
G

GracieLou

John,
Thanks again. I haven't tried this code but when I read it I got confused.
I don't see where it renames the linked file to Infile. Using the codes as
is, shouldn't Infile be replace with strTableName? Also, because the linked
files names are based on month and week, wouldn't "GLT_01" &
Format(iLoop,"00") only work for the first 4 files.

Trust me, you know much more about this than I do. That's just the way I
read it.

Thanks

John Spencer said:
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.

John Spencer said:
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.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Well, I can only go by what you posted as far as the table names go.

So if your names vary showing a week and month then you need to figure
out how to handle that. Perhaps a loop within a loop.

As far as strTableName, in the query the strTableName is aliased to
InFile. That way you don't need to change the query except for one
reference.

Also if you have more than 28 files as times goes on you will need to
work out that.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,
Thanks again. I haven't tried this code but when I read it I got confused.
I don't see where it renames the linked file to Infile. Using the codes as
is, shouldn't Infile be replace with strTableName? Also, because the linked
files names are based on month and week, wouldn't "GLT_01" &
Format(iLoop,"00") only work for the first 4 files.

Trust me, you know much more about this than I do. That's just the way I
read it.

Thanks

John Spencer said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

GracieLou

Thanks for your help. I'm sure I can get there now.

John Spencer said:
Well, I can only go by what you posted as far as the table names go.

So if your names vary showing a week and month then you need to figure
out how to handle that. Perhaps a loop within a loop.

As far as strTableName, in the query the strTableName is aliased to
InFile. That way you don't need to change the query except for one
reference.

Also if you have more than 28 files as times goes on you will need to
work out that.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,
Thanks again. I haven't tried this code but when I read it I got confused.
I don't see where it renames the linked file to Infile. Using the codes as
is, shouldn't Infile be replace with strTableName? Also, because the linked
files names are based on month and week, wouldn't "GLT_01" &
Format(iLoop,"00") only work for the first 4 files.

Trust me, you know much more about this than I do. That's just the way I
read it.

Thanks

John Spencer said:
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
'====================================================


GracieLou wrote:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Left, Right, Mid in Forms VB Coding 8
Renaming linked tables 4
Automating a rename of a table? 3
Dropping a table from a report 2
ADODB from Excel - Running a Query 1
Link Tables and Select Link 2
close / rename linked .mdb file 20
Help in deleting tables used by queries 4

Top