Rename database names in query expressions

B

brockinl

New Member
Joined
Jun 8, 2004
Messages
3
Is there a way to do a Find/Replace all occurrences of a db name in all query expressions across an entire Access database? Specifically, I have several queries with expressions referencing LAWAPP8DB and want to replace them all with LAWAPP9DB.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Hi

You could use this with a simple VBA procedure, like below. Please make a backup of your database before running this code though. (Tested in Access 2003).

Code: 
Sub FindReplaceAllQueries()

'Loops through all queries in this Access file and does a find/replace
'based on the variables sFindWhat and sReplaceWith

Dim sFindWhat As String, sReplaceWith As String
Dim qryDef As QueryDef


sFindWhat = "LAWAPP8DB"
sReplaceWith = "LAWAPP9DB"


For Each qryDef In CurrentDb.QueryDefs

    qryDef.SQL = Replace(qryDef.SQL, sFindWhat, sReplaceWith)


Next

MsgBox "All Done"


End Sub

HTH
DK
 
Upvote 0
HI

Microsoft Access is the most popular Windows database program. A very nice feature of Microsoft Access queries is the ability to rename fields. By using a query expression, you can change the field name the user sees.


Thanks











<table x:str="" style="border-collapse: collapse; width: 503pt;" width="671" border="0" cellpadding="0" cellspacing="0"><col style="width: 503pt;" width="671"><tr style="height: 25.5pt;" height="34"> <td class="xl25" style="height: 25.5pt; width: 503pt;" width="671" height="34">tumeur bénigne - aqua-biking</td> </tr></table>
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Access - How do you change a shared drive path name to not include individual user so others can run the queries?
Replies
2
Views
194
welshgasman
welshgasman
C
  • Question
VBA code for Next button to move to each record in Textbox1
Replies
2
Views
254
welshgasman
welshgasman
N
  • Solved
MS Query and Access Union Query
2 3
Replies
28
Views
1K
mdmilner
M
S
  • Question
ACCESS, VBA: Fetch data from sql server and paste it into local table.
Replies
3
Views
985
Micron
M
D
  • Solved
Export To Excel .XLSX file
Replies
3
Views
326
Micron
M

Forum statistics

Threads
1,216,030
Messages
6,128,405
Members
449,448
Latest member
Andrew Slatter

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top