我有一个快速而肮脏的 VBA 子程序,它可以满足我认为您想要的功能。 (您可以将其翻译成您喜欢的语言之一吗?)您可以使用数据库的副本进行尝试。 (不要尝试使用您想要保留的唯一数据库副本!)
To substitute "tblBar" for "tblFoo" in your queries, you can run it from the VBE Immediate Window (from Access, Ctrl+g will get you there) like this:
call swapTblNamesInQueryDefs("tblFoo", "tblBar")
要实际保存更改的查询定义,请像这样调用它:
call swapTblNamesInQueryDefs("tblFoo", "tblBar", "savechanges")
代码:
Public Sub swapTblNamesInQueryDefs(ByVal pstrFind As String, _
ByVal pstrReplace As String, _
Optional ByVal pstrMode As String = "DisplayOnly")
Dim qd As QueryDef
Dim re As Object
Dim strSql As String
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.IgnoreCase = True
re.Pattern = "\b" & pstrFind & "\b"
For Each qd In CurrentDb.QueryDefs
If Left$(qd.Name, 1) <> "~" Then
Debug.Print qd.Name
Debug.Print "Before: " & qd.SQL
strSql = re.Replace(qd.SQL, pstrReplace)
Debug.Print "After: " & strSql
'only save the modified SQL statement if called
'with SaveChanges parameter
'If pstrMode = "SaveChanges" Then
If StrComp(pstrMode, "SaveChanges", vbTextCompare) = 0 Then
qd.SQL = strSql
End If
Debug.Print String(20, "-")
End If
Next qd
Set re = Nothing
Set qd = Nothing
End Sub
编辑:更改了评估pstrMode
以保证比较不区分大小写(如果模块包含“Option Compare Binary”)。