Change table name in query, design view?

[lorirobn]

Hi,

Is there a way to change the name of a table that's in a Query, in Design view? I have some tables with similar fields, and if I want to change the table that's in there, I always have to add the new table, then delete the old table. It would just be easier if I could somehow just change the name.

Is this possible?

Thanks in advance.

 

[lespaul]

sure, switch to the SQL view of the query and type the correct/new table name.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[lorirobn]

Thanks, Leslie. I've done that, too, changed the table from the sql. But then you have to change each occurrence of the table name (unless there's a 'change all' command, which I haven't been able to figure out).

I was hoping that there'd be a way to change the name of the table that's shown in design view, by right-clicking or something. I've never been able to find a way, though.

Thanks...
Lori

 

[lespaul]

But then you have to change each occurrence of the table name

if you use an alias then you only have to change the table name once:
[tt]
SELECT A.Field1, A.Field2, B.Field3
FROM TableName A
INNER JOIN OtherTable B On A.Field1 = B.Field1
[/tt]
now you only have to change TableName and OtherTable.

Of course if there's only one table, you don't need the table name qualifer for the field, Access is just helping by putting it there. If you currently have:
[tt]
SELECT TableName.Field1, TableName.Field2, TableName.Field3
FROM TableName[/tt]

you can replace that with
[tt]
SELECT Field1, Field2, Field3
FROM TableName[/tt]

and it's perfectly valid SQL and you only have to change TableName once.

you don't say if it's an update query or just a select query. I would also have to guess that your database isn't normalized if you need to run the same query against different tables. Maybe you could consider a UNION query that would combine all the different tables into a single source.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[lorirobn]

Hi Leslie,

I've been using a lot of queries that reference other queries, for some complicated reports. My question basically refers to changing the referenced query, which I am doing frequently to alter test results. I've been doing this manually, as you also suggest, either in the sql, or by adding the additional query then deleting the prior in design view. These queries need the query/table name as prefix.

In the grand scheme of things, this is definitely not a big deal. I was merely wondering if it can be done via design view, whether I was missing something.

I think by now I basically know the answer, though! "Do it manually".

Thanks again for your responses,
Lori

 

[Golom]

You can also cheat as I often do

- Copy the SQL to WordPad
- Do a global replace of the old table name to the new one
- Copy it back to the SQL view in the query

 

[sxschech]

Another alternative is find and replace

http://www.rickworld.com/products.html

 

[lorirobn]

Ahaaa. Two great suggestions. I already have Find&Replace, and I love it and use it all the time. I like the wordpad cheating trick for fast and easy change.
Thanks!

 

[sxschech]

Also, sometimes when creating a lot of similar queries, after creating the original query, save it in sql and that way you can copy and paste it to a new query and quickly make a change. In addition, if you have joined tables or queries and make a change, saving it in sql view will preserve the join if something happens to one of the tables/queries and then you can easily edit and switch back to design and the join is preserved.

 

[TheAceMan1]

How are ya lorirobn . . .

Here's a snippet of code you can build on to replace the tablename in any query:

[blue]Public Sub ReplaceInQuery(qryName As String, tblName As String, rplName As String)
   Dim db As DAO.Database, qdf As DAO.QueryDef
   
   Set db = CurrentDb
   Set qdf = db.QueryDefs(qryName)
   
   qdf.SQL = Replace(qdf.SQL, tblName, rplName)
   
   Set qdf = Nothing
   Set db = Nothing
   
End Sub[/blue]

See Ya! . . . . . .