我使用 Microsoft SQL Server 数据库迁移助手 (SSMA) 软件将任务关键型 Access 2003 数据库从本地 MDB 更改为 MDB 前端,后端位于 MS SQL Server 2005 上。
现在,我需要永久更改表链接到的服务器从 IP 地址(很快就会改变)到主机名指向同一个服务器。服务器本身没有改变,只是连接字符串发生了变化。
它是无 DSN 连接,因此 ODBC 信息包含在 Access MDB 文件中。如果我尝试刷新 Access 中的表链接,它会提示我输入 DSN(我不想使用它)。
我做了一些谷歌搜索,发现了几段代码,可以让它在每次程序启动时自行更新。但是,我担心这可能会给用户带来问题或延迟。这是我的最佳选择,还是有一些技巧可以永久更改存储在 MDB 中的连接字符串?
以下代码多年来一直对我很有用:
Function LinkTable(DbName As String, SrcTblName As String, _
Optional TblName As String = "", _
Optional ServerName As String = DEFAULT_SERVER_NAME, _
Optional DbFormat As String = "ODBC") As Boolean
Dim db As dao.Database
Dim TName As String, td As TableDef
On Error GoTo Err_LinkTable
If Len(TblName) = 0 Then
TName = SrcTblName
Else
TName = TblName
End If
'Do not overwrite local tables.'
If DCount("*", "msysObjects", "Type=1 AND Name=" & Qt(TName)) > 0 Then
MsgBox "There is already a local table named " & TName
Exit Function
End If
Set db = CurrentDb
'Drop any linked tables with this name'
If DCount("*", "msysObjects", "Type In (4,6,8) AND Name=" & Qt(TName)) > 0 Then
db.TableDefs.Delete TName
End If
With db
Set td = .CreateTableDef(TName)
td.Connect = BuildConnectString(DbFormat, ServerName, DbName)
td.SourceTableName = SrcTblName
.TableDefs.Append td
.TableDefs.Refresh
LinkTable = True
End With
Exit_LinkTable:
Exit Function
Err_LinkTable:
'Replace following line with call to error logging function'
MsgBox Err.Description
Resume Exit_LinkTable
End Function
Private Function BuildConnectString(DbFormat As String, _
ServerName As String, _
DbName As String, _
Optional SQLServerLogin As String = "", _
Optional SQLServerPassword As String = "") As String
Select Case DbFormat
Case "NativeClient10"
BuildConnectString = "ODBC;" & _
"Driver={SQL Server Native Client 10.0};" & _
"Server=" & ServerName & ";" & _
"Database=" & DbName & ";"
If Len(SQLServerLogin) > 0 Then
BuildConnectString = BuildConnectString & _
"Uid=" & SQLServerLogin & ";" & _
"Pwd=" & SQLServerPassword & ";"
Else
BuildConnectString = BuildConnectString & _
"Trusted_Connection=Yes;"
End If
Case "ADO"
If Len(ServerName) = 0 Then
BuildConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DbName & ";"
Else
BuildConnectString = "Provider=sqloledb;" & _
"Server=" & ServerName & ";" & _
"Database=" & DbName & ";"
If Len(SQLServerLogin) > 0 Then
BuildConnectString = BuildConnectString & _
"UserID=" & SQLServerLogin & ";" & _
"Password=" & SQLServerPassword & ";"
Else
BuildConnectString = BuildConnectString & _
"Integrated Security=SSPI;"
End If
End If
Case "ODBC"
BuildConnectString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=" & ServerName & ";" & _
"Database=" & DbName & ";"
If Len(SQLServerLogin) > 0 Then
BuildConnectString = BuildConnectString & _
"Uid=" & SQLServerLogin & ";" & _
"Pwd=" & SQLServerPassword & ";"
Else
BuildConnectString = BuildConnectString & _
"Trusted_Connection=Yes;"
End If
Case "MDB"
BuildConnectString = ";Database=" & DbName
End Select
End Function
Function Qt(Text As Variant) As String
Const QtMark As String = """"
If IsNull(Text) Or IsEmpty(Text) Then
Qt = "Null"
Else
Qt = QtMark & Replace(Text, QtMark, """""") & QtMark
End If
End Function
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)