we have an issue with an access database we are upgrading to use SQL Server as its data store.
我们有一个访问数据库的问题,我们正在升级以使用SQL Server作为其数据存储。
This particular database links to 2 sql databases, so I thought to simplify things, we have a view in the main database that linked to each table in the secondary database. That way access would only need to talk directly with one SQL database.
这个特定的数据库链接到2个sql数据库,所以我想简化一些事情,我们在主数据库中有一个链接到辅助数据库中每个表的视图。这样访问只需要直接与一个SQL数据库对话。
When we linked access to the database views we choose which fields were the primary keys so the views were not readonly. We have standard code that refreshes all links when a database opens to pickup any changes and the linked views become readonly because the primary key information is lost.
当我们链接访问数据库视图时,我们选择哪些字段是主键,因此视图不是只读的。我们有标准代码,当数据库打开以拾取任何更改时,刷新所有链接,并且链接视图变为只读,因为主键信息丢失。
Is there a way of refreshing the links to views while retaining the primary key information?
有没有一种方法可以在保留主键信息的同时刷新视图链接?
John
3 个解决方案
#1
I have included my entire ODBC Reconnect function below. This function is predicated with the idea that I have a table called rtblODBC which stores all of the information I need to do the reconnecting. If you implement this function, you will NOT need to worry about connecting to multiple SQL databases, as that is handled smoothly with each table to be reconnected having its own connection string.
我在下面包含了我的整个ODBC重新连接功能。这个函数的前提是我有一个名为rtblODBC的表,它存储了重新连接所需的所有信息。如果实现此功能,则无需担心连接到多个SQL数据库,因为可以平滑处理每个要重新连接的表并具有自己的连接字符串。
When you get towards the end you will see that I use DAO to recreate the primary keys with db.Execute "CREATE INDEX " & sPrimaryKeyName & " ON " & sLocalTableName & "(" & sPrimaryKeyField & ")WITH PRIMARY;"
当你走到最后,你会看到我使用DAO来重新创建主键,db.Execute“CREATE INDEX”&sPrimaryKeyName&“ON”&sLocalTableName&“(”&sPrimaryKeyField&“)WITH PRIMARY;”
If you have any questions, please ask.
如果您有任何问题,请询问。
Public Function fnReconnectODBC( _
Optional bForceReconnect As Boolean _
) As Boolean
' Comments :
' Parameters: bForceReconnect -
' Returns : Boolean -
' Modified :
' --------------------------------------------------'
On Error GoTo Err_fnReconnectODBC
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim sPrimaryKeyName As String
Dim sPrimaryKeyField As String
Dim sLocalTableName As String
Dim strConnect As String
Dim varRet As Variant
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String
If IsMissing(bForceReconnect) Then
bForceReconnect = False
End If
sSQL = "SELECT rtblODBC.LocalTableName, MSysObjects.Name, MSysObjects.ForeignName, rtblODBC.SourceTableName, MSysObjects.Connect, rtblODBC.ConnectString " _
& "FROM MSysObjects RIGHT JOIN rtblODBC ON MSysObjects.Name = rtblODBC.LocalTableName " _
& "WHERE (((rtblODBC.ConnectString)<>'ODBC;' & [Connect]));"
Set con = Access.CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open sSQL, con, adOpenDynamic, adLockOptimistic
'Test the recordset to see if any tables in rtblODBC (needed tables) are missing from the MSysObjects (actual tables)
If rst.BOF And rst.EOF And bForceReconnect = False Then
'No missing tables identified
fnReconnectODBC = True
Else
'Table returned information, we don't have a perfect match, time to relink
Set db = CurrentDb
Set rs = db.OpenRecordset("rtblODBC", dbOpenSnapshot)
'For each table definition in the database collection of tables
For Each tdf In db.TableDefs
'Set strConnect variable to table connection string
strConnect = tdf.Connect
If Len(strConnect) > 0 And Left(tdf.Name, 1) <> "~" Then
If Left(strConnect, 4) = "ODBC" Then
'If there is a connection string, and it's not a temp table, and it IS an odbc table
'Delete the table
DoCmd.DeleteObject acTable, tdf.Name
End If
End If
Next
'Relink tables from rtblODBC
With rs
.MoveFirst
Do While Not .EOF
Set tdf = db.CreateTableDef(!localtablename, dbAttachSavePWD, !SourceTableName, !ConnectString)
varRet = SysCmd(acSysCmdSetStatus, "Relinking '" & !SourceTableName & "'")
db.TableDefs.Append tdf
db.TableDefs.Refresh
If Len(!PrimaryKeyName & "") > 0 And Len(!PrimaryKeyField & "") > 0 Then
sPrimaryKeyName = !PrimaryKeyName
sPrimaryKeyField = !PrimaryKeyField
sLocalTableName = !localtablename
db.Execute "CREATE INDEX " & sPrimaryKeyName & " ON " & sLocalTableName & "(" & sPrimaryKeyField & ")WITH PRIMARY;"
End If
db.TableDefs.Refresh
.MoveNext
Loop
End With
subTurnOffSubDataSheets
fnReconnectODBC = True
End If
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
Exit_fnReconnectODBC:
Set tdf = Nothing
Set rs = Nothing
Set db = Nothing
varRet = SysCmd(acSysCmdClearStatus)
Exit Function
Err_fnReconnectODBC:
fnReconnectODBC = False
sPrompt = "Press OK to continue."
vbMsg = MsgBox(sPrompt, vbOKOnly, "Error Reconnecting")
If vbMsg = vbOK Then
Resume Exit_fnReconnectODBC
End If
End Function
#2
A good deal of DSN less code that re-links access tables to SQL server often deletes the links first, then recreates the link. The code then sets up the connection string. Thus, it is the deleting that causes you to lose what the primary key was/is.
将访问表重新链接到SQL服务器的大量DSN代码通常首先删除链接,然后重新创建链接。然后代码设置连接字符串。因此,删除会导致您丢失主键是什么。
I actually recommend that you modify your re-link code as to not delete the table links.
我实际上建议您修改重新链接代码,以便不删除表链接。
Try something like:
尝试类似的东西:
For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 Then
If Left$(tdfCurrent.Connect, 5) = "ODBC;" Then
strCon = "ODBC;DRIVER={sql server};" & _
"SERVER=" & ServerName & ";" & _
"DATABASE=" & DatabaseName & ";" & _
"UID=" & UserID & ";" & _
"PWD=" & USERpw & ";" & _
"APP=Microsoft Office 2003;" & _
"WSID=" & WSID & ";"
End If
End If
tdfCurrent.Connect = strCon
tdfCurrent.RefreshLink
End If
Next tdfCurrent
#3
This works a litte better for me (note the moved end if's):
这对我来说更合适(请注意移动的结尾如果):
Dim dbCurrent As Database
Set dbCurrent = CurrentDb()
StatusList.SetFocus
StatusList.AddItem ("starting... ")
I = DoEvents()
Dim tdfCurrent As DAO.TableDef
For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 Then
If Left$(tdfCurrent.Connect, 5) = "ODBC;" Then
strCon = "ODBC;DRIVER={sql server};" & _
"SERVER=" & ServerName & ";" & _
"DATABASE=" & DatabaseName & ";" & _
"UID=" & UserID & ";" & _
"PWD=" & USERpw & ";" & _
"APP=Microsoft Office 2003;" & _
"WSID=" & WSID & ";"
StatusList.AddItem ("fixing " & tdfCurrent.Name)
tdfCurrent.Connect = strCon
tdfCurrent.RefreshLink
End If
End If
I = DoEvents()
Next tdfCurrent
StatusList.AddItem ("----Done.")
The ODBC check is correct, even though the "ODBC;" part doesn't show in the MSysObjects view.
ODBC检查是正确的,即使“ODBC;”部分未显示在MSysObjects视图中。
#1
I have included my entire ODBC Reconnect function below. This function is predicated with the idea that I have a table called rtblODBC which stores all of the information I need to do the reconnecting. If you implement this function, you will NOT need to worry about connecting to multiple SQL databases, as that is handled smoothly with each table to be reconnected having its own connection string.
我在下面包含了我的整个ODBC重新连接功能。这个函数的前提是我有一个名为rtblODBC的表,它存储了重新连接所需的所有信息。如果实现此功能,则无需担心连接到多个SQL数据库,因为可以平滑处理每个要重新连接的表并具有自己的连接字符串。
When you get towards the end you will see that I use DAO to recreate the primary keys with db.Execute "CREATE INDEX " & sPrimaryKeyName & " ON " & sLocalTableName & "(" & sPrimaryKeyField & ")WITH PRIMARY;"
当你走到最后,你会看到我使用DAO来重新创建主键,db.Execute“CREATE INDEX”&sPrimaryKeyName&“ON”&sLocalTableName&“(”&sPrimaryKeyField&“)WITH PRIMARY;”
If you have any questions, please ask.
如果您有任何问题,请询问。
Public Function fnReconnectODBC( _
Optional bForceReconnect As Boolean _
) As Boolean
' Comments :
' Parameters: bForceReconnect -
' Returns : Boolean -
' Modified :
' --------------------------------------------------'
On Error GoTo Err_fnReconnectODBC
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim sPrimaryKeyName As String
Dim sPrimaryKeyField As String
Dim sLocalTableName As String
Dim strConnect As String
Dim varRet As Variant
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String
If IsMissing(bForceReconnect) Then
bForceReconnect = False
End If
sSQL = "SELECT rtblODBC.LocalTableName, MSysObjects.Name, MSysObjects.ForeignName, rtblODBC.SourceTableName, MSysObjects.Connect, rtblODBC.ConnectString " _
& "FROM MSysObjects RIGHT JOIN rtblODBC ON MSysObjects.Name = rtblODBC.LocalTableName " _
& "WHERE (((rtblODBC.ConnectString)<>'ODBC;' & [Connect]));"
Set con = Access.CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open sSQL, con, adOpenDynamic, adLockOptimistic
'Test the recordset to see if any tables in rtblODBC (needed tables) are missing from the MSysObjects (actual tables)
If rst.BOF And rst.EOF And bForceReconnect = False Then
'No missing tables identified
fnReconnectODBC = True
Else
'Table returned information, we don't have a perfect match, time to relink
Set db = CurrentDb
Set rs = db.OpenRecordset("rtblODBC", dbOpenSnapshot)
'For each table definition in the database collection of tables
For Each tdf In db.TableDefs
'Set strConnect variable to table connection string
strConnect = tdf.Connect
If Len(strConnect) > 0 And Left(tdf.Name, 1) <> "~" Then
If Left(strConnect, 4) = "ODBC" Then
'If there is a connection string, and it's not a temp table, and it IS an odbc table
'Delete the table
DoCmd.DeleteObject acTable, tdf.Name
End If
End If
Next
'Relink tables from rtblODBC
With rs
.MoveFirst
Do While Not .EOF
Set tdf = db.CreateTableDef(!localtablename, dbAttachSavePWD, !SourceTableName, !ConnectString)
varRet = SysCmd(acSysCmdSetStatus, "Relinking '" & !SourceTableName & "'")
db.TableDefs.Append tdf
db.TableDefs.Refresh
If Len(!PrimaryKeyName & "") > 0 And Len(!PrimaryKeyField & "") > 0 Then
sPrimaryKeyName = !PrimaryKeyName
sPrimaryKeyField = !PrimaryKeyField
sLocalTableName = !localtablename
db.Execute "CREATE INDEX " & sPrimaryKeyName & " ON " & sLocalTableName & "(" & sPrimaryKeyField & ")WITH PRIMARY;"
End If
db.TableDefs.Refresh
.MoveNext
Loop
End With
subTurnOffSubDataSheets
fnReconnectODBC = True
End If
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
Exit_fnReconnectODBC:
Set tdf = Nothing
Set rs = Nothing
Set db = Nothing
varRet = SysCmd(acSysCmdClearStatus)
Exit Function
Err_fnReconnectODBC:
fnReconnectODBC = False
sPrompt = "Press OK to continue."
vbMsg = MsgBox(sPrompt, vbOKOnly, "Error Reconnecting")
If vbMsg = vbOK Then
Resume Exit_fnReconnectODBC
End If
End Function
#2
A good deal of DSN less code that re-links access tables to SQL server often deletes the links first, then recreates the link. The code then sets up the connection string. Thus, it is the deleting that causes you to lose what the primary key was/is.
将访问表重新链接到SQL服务器的大量DSN代码通常首先删除链接,然后重新创建链接。然后代码设置连接字符串。因此,删除会导致您丢失主键是什么。
I actually recommend that you modify your re-link code as to not delete the table links.
我实际上建议您修改重新链接代码,以便不删除表链接。
Try something like:
尝试类似的东西:
For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 Then
If Left$(tdfCurrent.Connect, 5) = "ODBC;" Then
strCon = "ODBC;DRIVER={sql server};" & _
"SERVER=" & ServerName & ";" & _
"DATABASE=" & DatabaseName & ";" & _
"UID=" & UserID & ";" & _
"PWD=" & USERpw & ";" & _
"APP=Microsoft Office 2003;" & _
"WSID=" & WSID & ";"
End If
End If
tdfCurrent.Connect = strCon
tdfCurrent.RefreshLink
End If
Next tdfCurrent
#3
This works a litte better for me (note the moved end if's):
这对我来说更合适(请注意移动的结尾如果):
Dim dbCurrent As Database
Set dbCurrent = CurrentDb()
StatusList.SetFocus
StatusList.AddItem ("starting... ")
I = DoEvents()
Dim tdfCurrent As DAO.TableDef
For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 Then
If Left$(tdfCurrent.Connect, 5) = "ODBC;" Then
strCon = "ODBC;DRIVER={sql server};" & _
"SERVER=" & ServerName & ";" & _
"DATABASE=" & DatabaseName & ";" & _
"UID=" & UserID & ";" & _
"PWD=" & USERpw & ";" & _
"APP=Microsoft Office 2003;" & _
"WSID=" & WSID & ";"
StatusList.AddItem ("fixing " & tdfCurrent.Name)
tdfCurrent.Connect = strCon
tdfCurrent.RefreshLink
End If
End If
I = DoEvents()
Next tdfCurrent
StatusList.AddItem ("----Done.")
The ODBC check is correct, even though the "ODBC;" part doesn't show in the MSysObjects view.
ODBC检查是正确的,即使“ODBC;”部分未显示在MSysObjects视图中。