Access dropping links to MS SQL

[wanderir]

Morning All,
I have an MS SQL 7.0 database with an Access front end. The front end is used by about 7 people. There are two views that I have linked via ODBC to Access. There are several queries which reference them, that are used in forms.

The problem:
When starting Access some users are not linked to the views.

Any idea why Access is dropping these two views?

Thanks,

Ed Colbeth
Northeastern University
e.colbeth@neu.edu

 

[snyderj]

I've seen this happen too. There may be a simple solution using security or multi-user settings, but I ended up creating running code each time the database was opened to refresh the ODBC. This may not work for your situation, though. Here's what I did:

Sub RefreshODBC()
Dim dbs As Database, tdfLinked As TableDef
' Set array size as needed
Dim TblArray(10, 2) As String, t As Integer
Set dbs = CurrentDb
Dim tblDSN As String

tblDSN = "your DSN"

'Enter table names and key fields below with the following syntax:
' TblArray(n, 0) = "table name w/o prefix"
' TblArray(n, 1) = "key field (if applicable)"
' TblArray(n, 2) = ", another key field (if applicable)"

'Note: The dbo_ prefix shown below is as viewed in Access (if applicable)
For t = 1 To 10
On Error Resume Next
dbs.TableDefs.Delete "dbo_" & TblArray(t, 0)
Next t

For t = 1 To 10
Set tdfLinked = dbs.CreateTableDef("dbo_" & TblArray(t, 0))
tdfLinked.Connect = "ODBC;DSN=" & tblDSN
tdfLinked.SourceTableName = "dbo." & TblArray(t, 0)
dbs.TableDefs.Append tdfLinked
dbs.Execute "CREATE INDEX NewIndex ON dbo_" & TblArray(t, 0) & _
"(" & TblArray(t, 1) & TblArray(t, 2) & ""
Next t

End Sub