Relinking tables from to ACCESS SQL Server (1 Viewer)

[jpl458]

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

 

[GregRun]

So you're saying you have it in BOTH the OnOpen and OnLoad Events?

I recommend creating a blank form and having that open on startup. Then put your code in the OnOpen of that form followed by code to open your current form and then close your startup form. This way it will only run on startup.

Your other option is to put this function in a separate module and then create a Macro to run your function. Name the macro "AutoExec" and it will run when you open the database.