Hope this is the proper forum, if not, let me know.
Have a data entry application that uses linked tables to SQL Server. The application will be used on three work stations, so I need to have code that relinks the table whenever the program is used, so users won't have to contend with that. I located the connect string in the description in the forms property sheet and store it in a table. The connect string and the code I use (pinched from the web) are as follows;
tblConnectionsConnectionString"ODBC;DSN=DataServer;Description=Data Server;APP=Microsoft Office 2010;DATABASE=MyData;;TABLE=dbo.ZSSP_Coupons"
' This Code re-links any tables and views
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim indexSQL As String
Dim fld As Field
Dim constr As Variant
Dim HasIndex As Boolean
constr = DLookup("ConnectionString", "tblConnections", "Active = True")
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
HasIndex = False
If tdef.Indexes.Count = 1 Then
' only interested in objects with 1 index
indexSQL = "CREATE INDEX " & tdef.Indexes(0).Name & " ON [" & tdef.Name & "](" & tdef.Indexes(0).Fields & ")"
' convert field list from (+fld1;+fld2) to (fld1,fld2)
indexSQL = Replace(indexSQL, "+", "")
indexSQL = Replace(indexSQL, ";", ",")
HasIndex = True
End If
tdef.Connect = constr
tdef.RefreshLink
If HasIndex And tdef.Indexes.Count = 0 Then
' if index now removed then re-create it
CurrentDb.Execute indexSQL
End If
End If
Next
MsgBox "Re link completed"
The code is in the On Open event, and I have tried it in On Load event. The ap auto loades tha data entry form, set in options.
The problem is that when I load the Application table I get the Select Data Source Window twice, once when the ap opens and again when the form loads. (I don't want that to happen) If I select the data source both times then data entry works ok, and before the form open I get the Relink completed message. I seems to me I don't have things happening at the right time, or am missing something.
Any help would be greatly appreciated.
jpl 458
Have a data entry application that uses linked tables to SQL Server. The application will be used on three work stations, so I need to have code that relinks the table whenever the program is used, so users won't have to contend with that. I located the connect string in the description in the forms property sheet and store it in a table. The connect string and the code I use (pinched from the web) are as follows;
tblConnectionsConnectionString"ODBC;DSN=DataServer;Description=Data Server;APP=Microsoft Office 2010;DATABASE=MyData;;TABLE=dbo.ZSSP_Coupons"
' This Code re-links any tables and views
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim indexSQL As String
Dim fld As Field
Dim constr As Variant
Dim HasIndex As Boolean
constr = DLookup("ConnectionString", "tblConnections", "Active = True")
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
HasIndex = False
If tdef.Indexes.Count = 1 Then
' only interested in objects with 1 index
indexSQL = "CREATE INDEX " & tdef.Indexes(0).Name & " ON [" & tdef.Name & "](" & tdef.Indexes(0).Fields & ")"
' convert field list from (+fld1;+fld2) to (fld1,fld2)
indexSQL = Replace(indexSQL, "+", "")
indexSQL = Replace(indexSQL, ";", ",")
HasIndex = True
End If
tdef.Connect = constr
tdef.RefreshLink
If HasIndex And tdef.Indexes.Count = 0 Then
' if index now removed then re-create it
CurrentDb.Execute indexSQL
End If
End If
Next
MsgBox "Re link completed"
The code is in the On Open event, and I have tried it in On Load event. The ap auto loades tha data entry form, set in options.
The problem is that when I load the Application table I get the Select Data Source Window twice, once when the ap opens and again when the form loads. (I don't want that to happen) If I select the data source both times then data entry works ok, and before the form open I get the Relink completed message. I seems to me I don't have things happening at the right time, or am missing something.
Any help would be greatly appreciated.
jpl 458