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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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 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
420
welshgasman
welshgasman
J
  • Solved
Having a problem in the expression builder, very new to Access. It thinks I'm trying to use Automation objects?
2
Replies
15
Views
911
Micron
M
C
  • Question Question
VBA code for Next button to move to each record in Textbox1
Replies
2
Views
485
welshgasman
welshgasman
N
  • Solved
MS Query and Access Union Query
2 3
Replies
28
Views
2K
mdmilner
M
S
  • Question Question
ACCESS, VBA: Fetch data from sql server and paste it into local table.
Replies
3
Views
2K
Micron
M

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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