以编程方式重命名Access查询中的表 [英] Programmatically rename tables in Access queries

问题描述

我有一个带有数百个查询的Access 2003数据库文件.我想根据条件重命名查询中引用的所有表

I have an access 2003 database file with hundreds of queries. I would like to rename all tables referenced from within my queries based on a condition

If tableNameInQuery = "tableName" Then

    tableNameInQuery = "newTableName"

End If

在C#或VB.NET中使用PS作为示例，将不胜感激

PS an example in either C# or VB.NET would be appreciated

推荐答案

我很快就&肮脏的VBA子做我想你想要的. (您可以将其翻译成您的首选语言吗?)您可以尝试使用数据库副本进行尝试. (不要尝试使用您要保留的数据库的唯一副本！)

I have a quick & dirty VBA sub which does what I think you want. (Can you translate it to one of your preferred languages?) You could try it with a copy of your database. (DO NOT try it with the only copy of a database you want to keep!)

要在查询中用"tblBar" 替换"tblFoo" ，可以在VBE立即窗口中运行它(通过Access， Ctrl + g 会到达您的位置)，如下所示:

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")

要真正保存更改后的查询定义，请像这样调用它:

To actually save the changed query definitions, call it like so:

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").

Changed evaluation of pstrMode to guarantee case insensitive comparison (in case module includes "Option Compare Binary").

这篇关于以编程方式重命名Access查询中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！