MS Access Associated with SQL Server Views

Question

We have a problem with the access database that we are updating to use SQL Server as a data warehouse.

This particular database refers to 2 sql databases, so I decided to simplify the situation, we have a view in the main database, which is associated with each table in the secondary database. Thus, access will be needed only for direct communication with one SQL database.

When we linked access to database views, we choose which fields were primary keys so that views are not read only. We have standard code that updates all links when the database is opened to receive any changes, and the associated views become read because the primary key information is lost.

Is there a way to update view links while storing primary key information?

John

Answer 1

I have included all my ODBC reconnect function below. This function is based on the idea that I have a table called rtblODBC that stores all the information I need to reconnect. If you implement this function, you do not need to worry about connecting to multiple SQL databases, as this is done with smooth access to each table that needs to be reconnected with its own connection string.

When you get to the end, you'll see that I use DAO to recreate the primary keys with db.Execute "CREATE INDEX" and sPrimaryKeyName and "ON" and sLocalTableName and "(" and sPrimaryKeyField and ") 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 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 

Answer 2

Much of the DSN code that reassigns SQL server access tables often deletes links first and then re-creates the link. The code then sets up the connection string. Thus, it is a deletion that makes you lose what was / was the main key.

I really recommend that you change your repeat link code so that you don’t delete links in the table.

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 

Answer 3

This works better for me (note the moved end of 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.") 

ODBC validation is correct even if "ODBC"; part is not displayed in MSysObjects view.