how to change query for access xp?

  • Thread starter Thread starter Elsie
  • Start date Start date
E

Elsie

hi all, in my access file, I have some linked tables and these linked tables
are used in all my queries. now I have to change the linked tables, is there
any quick and painless method to change?

e.g.:

SELECT [PUB_po-rcpt].[rcvd-date], [PUB_po-rcpt].[vend-num],
PUB_vendaddr.name, PUB_poitem.item, PUB_item.description,
PUB_item.[product-code], [PUB_po-rcpt].[qty-received]*1 AS [qty-received]
INTO T_Past_Purchase
FROM (([PUB_po-rcpt] INNER JOIN PUB_poitem ON (PUB_poitem.[po-release] =
[PUB_po-rcpt].[po-release]) AND (PUB_poitem.[po-line] =
[PUB_po-rcpt].[po-line]) AND ([PUB_po-rcpt].[po-num] = PUB_poitem.[po-num]))
INNER JOIN PUB_item ON PUB_poitem.item = PUB_item.item) INNER JOIN
PUB_vendaddr ON [PUB_po-rcpt].[vend-num] = PUB_vendaddr.[vend-num]
WHERE ((([PUB_po-rcpt].[rcvd-date])<#6/1/2004#) AND
((PUB_item.[product-code]) Like "*imp"))
ORDER BY [PUB_po-rcpt].[vend-num], PUB_item.description,
PUB_item.[product-code];

I have to change all PUB_<table name> to dbo_<table name> as well as all
hyphens to underscore, I.e., [PUB_po-rcpt] --> [dbo_po_rcpt]. Please help
 
HI Elsie

1. Switch the query to SQL view (View menu).

2. Copy the SQL statement to Notepad or Word.

3. Use Replace (Edit menu) to make the 2 changes.

4. Paste the changed text back into SQL View of the query.
 
hi all, in my access file, I have some linked tables and these linked tables
are used in all my queries. now I have to change the linked tables, is there
any quick and painless method to change?

e.g.:

SELECT [PUB_po-rcpt].[rcvd-date], [PUB_po-rcpt].[vend-num],
PUB_vendaddr.name, PUB_poitem.item, PUB_item.description,
PUB_item.[product-code], [PUB_po-rcpt].[qty-received]*1 AS [qty-received]
INTO T_Past_Purchase
FROM (([PUB_po-rcpt] INNER JOIN PUB_poitem ON (PUB_poitem.[po-release] =
[PUB_po-rcpt].[po-release]) AND (PUB_poitem.[po-line] =
[PUB_po-rcpt].[po-line]) AND ([PUB_po-rcpt].[po-num] = PUB_poitem.[po-num]))
INNER JOIN PUB_item ON PUB_poitem.item = PUB_item.item) INNER JOIN
PUB_vendaddr ON [PUB_po-rcpt].[vend-num] = PUB_vendaddr.[vend-num]
WHERE ((([PUB_po-rcpt].[rcvd-date])<#6/1/2004#) AND
((PUB_item.[product-code]) Like "*imp"))
ORDER BY [PUB_po-rcpt].[vend-num], PUB_item.description,
PUB_item.[product-code];

I have to change all PUB_<table name> to dbo_<table name> as well as all
hyphens to underscore, I.e., [PUB_po-rcpt] --> [dbo_po_rcpt]. Please help

There are some third-party tools to help with this kind of operation.
Since you might very well have references to PUB_<etc> names in Forms,
Reports, and Code, it can be a real hassle tracking all the instances
down!

Try:

Freeware: http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
hi, I think afaik, the PUB_<table name> is appearing in queries only...
lucky me...


John Vinson said:
hi all, in my access file, I have some linked tables and these linked tables
are used in all my queries. now I have to change the linked tables, is there
any quick and painless method to change?

e.g.:

SELECT [PUB_po-rcpt].[rcvd-date], [PUB_po-rcpt].[vend-num],
PUB_vendaddr.name, PUB_poitem.item, PUB_item.description,
PUB_item.[product-code], [PUB_po-rcpt].[qty-received]*1 AS [qty-received]
INTO T_Past_Purchase
FROM (([PUB_po-rcpt] INNER JOIN PUB_poitem ON (PUB_poitem.[po-release] =
[PUB_po-rcpt].[po-release]) AND (PUB_poitem.[po-line] =
[PUB_po-rcpt].[po-line]) AND ([PUB_po-rcpt].[po-num] = PUB_poitem.[po-num]))
INNER JOIN PUB_item ON PUB_poitem.item = PUB_item.item) INNER JOIN
PUB_vendaddr ON [PUB_po-rcpt].[vend-num] = PUB_vendaddr.[vend-num]
WHERE ((([PUB_po-rcpt].[rcvd-date])<#6/1/2004#) AND
((PUB_item.[product-code]) Like "*imp"))
ORDER BY [PUB_po-rcpt].[vend-num], PUB_item.description,
PUB_item.[product-code];

I have to change all PUB_<table name> to dbo_<table name> as well as all
hyphens to underscore, I.e., [PUB_po-rcpt] --> [dbo_po_rcpt]. Please help

There are some third-party tools to help with this kind of operation.
Since you might very well have references to PUB_<etc> names in Forms,
Reports, and Code, it can be a real hassle tracking all the instances
down!

Try:

Freeware: http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top