Changing the name of the created Table in a Make-Table Query

[ions]

Dear Access Expert

I wanted to know if it is possible to change the name of the Table which is going to be created using a Make-Table Query via code (VBA).

For example if my Make-Table query currently creates a table with the name "Table1" I want to change it to name "Table2" and then change it Back to "Table1" or "Table3" etc.... depending on the users selection.

 

[Guus2005]

Not sure why you want to do this but here's a way:

public sub MakeTable(strTablename as string)

dim strSql as string
strSql = "SELECT Account.AccountID, Account.Description INTO " & strTablename & "FROM Account;"

currentdb.execute strsql

end sub

 

[ions]

Hi again Guus

Can I do it without using SQL. Can I change the query def somehow? There must be a way to set the queries property that way.

 

[Guus2005]

Yes, you can change the querydef:

dim strSql as string
strsql=currentdb.querydefs("YourQuery").sql
strsql = replace(strsql,"Old","New)"
currentdb.querydefs("YourQuery").sql = strsql

 

[ions]

Guus2005 Thanks... You are still using SQL but this will do. Thanks Alot

 

[ions]

Actually this only works if you know the name of the Previous INTO Table. This will not do. Hmm....

 

[Guus2005]

Or you can rename it after it's been made