How can I quickly change the table name in a query?

S

sduraybito

I'd like to use a parameter value box to rewrite the table name for a
query:

Exsiting query:

SELECT [TABLE1].FirstName, [TABLE1].LastName, [TABLE1].Address1,
[TABLE1].Address2, [TABLE1].City, [TABLE1].State, [TABLE1].Zip,
FROM TABLE1

I'd like to change it to:

SELECT [TABLE2].FirstName, [TABLE2].LastName, [TABLE2].Address1,
[TABLE2].Address2, [TABLE2].City, [TABLE2].State, [TABLE2].Zip,
FROM TABLE2

without copy/pasting SQL into Notepad and replacing TABLE1 with
TABLE2.

Can I do this through Access?

TIA

Siegfried
 
J

John W. Vinson

I'd like to use a parameter value box to rewrite the table name for a
query:

Exsiting query:

SELECT [TABLE1].FirstName, [TABLE1].LastName, [TABLE1].Address1,
[TABLE1].Address2, [TABLE1].City, [TABLE1].State, [TABLE1].Zip,
FROM TABLE1

I'd like to change it to:

SELECT [TABLE2].FirstName, [TABLE2].LastName, [TABLE2].Address1,
[TABLE2].Address2, [TABLE2].City, [TABLE2].State, [TABLE2].Zip,
FROM TABLE2

without copy/pasting SQL into Notepad and replacing TABLE1 with
TABLE2.

Can I do this through Access?

Only by constructing the SQL text in VBA code. You can't pass it as a
parameter.

If you have multiple tables with the same fields... your database design needs
to be reconsidered. What are TABLE1 and TABLE2, and why do they both exist?
 
C

Clifford Bass

Hi Siegfried,

Besides what Steve and John wrote, there is a little trick I use to
rename a table manually inside of a query. This may not be what you are
looking for, but it sort of is implied by your mention of
copy/pasting/replacing.

While in query SQL mode just before the table name add "othertablename
AS ", without the quotes. So in your example it would now show:

SELECT [TABLE1].FirstName, [TABLE1].LastName, [TABLE1].Address1,
[TABLE1].Address2, [TABLE1].City, [TABLE1].State, [TABLE1].Zip,
FROM TABLE2 AS TABLE1

Then go to design mode, right-click on the table and choose Properties.
In the Alias field change the old table name to the new table name. Then
press <Enter>. It will change the values in the entire query, except for
references in subqueries.

Hope this is useful,

Clifford Bass
 
S

sduraybito

Look at QueryDef in the Help file.

Steve
(e-mail address removed)

Thanks. Is the example in the Help file supposed to work in the
Northwind database?

I inserted the sample code into a Module in Northwind and received
this error:

"Run-time error '3265':

Item not found in this collection."

What am I doing wrong?

TIA
 
S

sduraybito

We need to see your code and which line is highlighted when you get the
error.

Steve

I inserted

Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Employee List")
qryTest.SQL = "SELECT * FROM Employees;"

between Sub Test() and End Sub

Sub Test()

Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Employee List")
qryTest.SQL = "SELECT * FROM Employees;"

End Sub

Set qryTest = dbsCurrent.QueryDefs("Employee List") is the highlighted
line.

I don't see "Employee List" as a query in Northwind. Why would Help
have this example if it doesn't relate to Northwind?
 
S

sduraybito

I'd like to use a parameter value box to rewrite the table name for a
query:
Exsiting query:
SELECT [TABLE1].FirstName, [TABLE1].LastName, [TABLE1].Address1,
[TABLE1].Address2, [TABLE1].City, [TABLE1].State, [TABLE1].Zip,
FROM TABLE1
I'd like to change it to:
SELECT [TABLE2].FirstName, [TABLE2].LastName, [TABLE2].Address1,
[TABLE2].Address2, [TABLE2].City, [TABLE2].State, [TABLE2].Zip,
FROM TABLE2
without copy/pasting SQL into Notepad and replacing TABLE1 with
TABLE2.
Can I do this through Access?

Only by constructing the SQL text in VBA code. You can't pass it as a
parameter.

If you have multiple tables with the same fields... your database design needs
to be reconsidered. What are TABLE1 and TABLE2, and why do they both exist?

TABLE1 and TABLE2 are just used for this question. What I'm doing is
regression testing tables sourced from a mainframe app with a common
query.
 
S

sduraybito

OK I have the following working:

Sub Test()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
strTable = InputBox("Enter table to be updated:")


Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Query1")
qryTest.SQL = "SELECT * FROM " & [strTable] & ";"

End Sub

Instead of rendering the InputBox, can I have it show a list box from
which I can select one of the tables in the database?

TIA
 
J

John W. Vinson

<regression testing>

Ah. Makes sense.
OK I have the following working:

Sub Test()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
strTable = InputBox("Enter table to be updated:")


Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Query1")
qryTest.SQL = "SELECT * FROM " & [strTable] & ";"

End Sub

Instead of rendering the InputBox, can I have it show a list box from
which I can select one of the tables in the database?

Sure. You can either build your own table of tablenames, or if you want to get
them from the system table, the following A2003 code will work (not certain
about 2007):

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;

Use it as the rowsource for a multiselect listbox and loop through the
listbox's ItemsSelected collection to run your queries.
 
S

sduraybito

<regression testing>

Ah. Makes sense.




OK I have the following working:
Sub Test()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
strTable = InputBox("Enter table to be updated:")
Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Query1")
qryTest.SQL = "SELECT * FROM " & [strTable] & ";"
Instead of rendering the InputBox, can I have it show a list box from
which I can select one of the tables in the database?

I put this together:

Sub Test()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim strTable As String
Dim objAO As AccessObject
Dim objCP As Object
Dim strTableList As String

Set objCP = Application.CurrentData

For Each objAO In objCP.AllTables
If Not Left(objAO.Name, 4) = "msys" Then
strTableList = strTableList & objAO.Name & " "
End If
Next objAO

AllTables.RowSourceType = "Value List"
AllTables.RowSource = strValues

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Query1")
qryTest.SQL = "SELECT * FROM " & [strTable] & ";"

End Sub

But it hangs on AllTables.RowSourceType = "Value List" with

"Run time error '424':

Object required"

I can't get it to display a listbox.
 
D

Douglas J. Steele

John was assuming that your combo box or list box was named AllTables.

Incidentally, you should change

qryTest.SQL = "SELECT * FROM " & [strTable] & ";"

to

qryTest.SQL = "SELECT * FROM [" & strTable & "];"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


@gmail.com>
wrote:

<regression testing>

Ah. Makes sense.




OK I have the following working:
Sub Test()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
strTable = InputBox("Enter table to be updated:")
Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Query1")
qryTest.SQL = "SELECT * FROM " & [strTable] & ";"
Instead of rendering the InputBox, can I have it show a list box from
which I can select one of the tables in the database?

I put this together:

Sub Test()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim strTable As String
Dim objAO As AccessObject
Dim objCP As Object
Dim strTableList As String

Set objCP = Application.CurrentData

For Each objAO In objCP.AllTables
If Not Left(objAO.Name, 4) = "msys" Then
strTableList = strTableList & objAO.Name & " "
End If
Next objAO

AllTables.RowSourceType = "Value List"
AllTables.RowSource = strValues

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Query1")
qryTest.SQL = "SELECT * FROM " & [strTable] & ";"

End Sub

But it hangs on AllTables.RowSourceType = "Value List" with

"Run time error '424':

Object required"

I can't get it to display a listbox.
 
S

sduraybito

Whew, long day.

Here's my current code:

Sub Test()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim strTable As String
Dim objAO As AccessObject
Dim objCP As Object
Dim strTableList As String

Set objCP = Application.CurrentData

For Each objAO In objCP.AllTables
If Not Left(objAO.Name, 4) = "msys" Then
strTableList = strTableList & objAO.Name & " "
End If
Next objAO

AllTables.RowSourceType = "Value List"
AllTables.RowSource = strTableList

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Query1")
qryTest.SQL = "SELECT * FROM [" & strTable & "];"

End Sub

I can't get my Module to run past without a 424 error

AllTables.RowSourceType = "Value List"

When I hover over strTableList, I see strTableList = "Table 1Table2"

My Form is named AllTables

But I can't get the Listbox to show up when run from the module.
 
D

Douglas J. Steele

You can only use the RowSource and RowSourceType properties with combo boxes
or list boxes, not with forms.

Add a combo box cboTables to your form, and change

AllTables.RowSourceType = "Value List"
AllTables.RowSource = strTableList

to

Forms!AllTables!cboTables.RowSourceType = "Value List"
Forms!AllTables!cboTables.RowSource = strTableList
 
S

sduraybito

I'm getting closer (I think).

My code now:

Sub Test()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim strTable As String
Dim objAO As AccessObject
Dim objCP As Object
Dim strTableList As String

Set objCP = Application.CurrentData

For Each objAO In objCP.AllTables
If Not Left(objAO.Name, 4) = "msys" Then
strTableList = strTableList & objAO.Name & " "
End If
Next objAO

Forms!AllTables!cboTables.RowSourceType = "Value List"
Forms!AllTables!cboTables.RowSource = strTableList

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Query1")
qryTest.SQL = "SELECT * FROM [" & strTable & "];"

End Sub

Fails at

Forms!AllTables!cboTables.RowSourceType = "Value List"

with

Run-time error '2450':
Microsoft Office Access can't find the form 'AllTables' referred to in
a macro expression or Visual Basic code.
 
J

John Spencer

Is the form AllTables open? It must be open for it to be part of the
Forms collection.

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

sduraybito

Is the form AllTables open?  It must be open for it to be part of the
Forms collection.

Thanks, when I leave the form open, the script fails at

qryTest.SQL = "SELECT * FROM [" & strTable & "];"

with

Run-time error '3131':
Syntax error in FROM clause.

When I hover over qryTest:

qryTest.SQL = "SELECT * FROM Table1;"
 
C

Clifford Bass

Hi,

I would suspect the presence of stray non-displaying characters in your
code. Delete the current line and completely retype it. Then try again.

Clifford Bass

sduraybito said:
Is the form AllTables open? It must be open for it to be part of the
Forms collection.

Thanks, when I leave the form open, the script fails at

qryTest.SQL = "SELECT * FROM [" & strTable & "];"

with

Run-time error '3131':
Syntax error in FROM clause.

When I hover over qryTest:

qryTest.SQL = "SELECT * ÂÂFROM Table1;ÂÂ"
 

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

Top