Hi Goth,
Yes there is. This is lengthy and a tad complicated though (Especially if you don't have VBA knowledge). Most of the code is cut and past though (check for my comments where it says you need to change the database name and path). I broke it out into steps to help. MAKE A BACKUP!!!
In the end this will setup the connection without the need for a DSN, and dynamically link via ODBC. Deleting the links is helpful because then if you make any changes to the table structure, you don't have to send out a new front end because every time the user logs in and out they get fresh links (and it "opens the connection", giving a slight performance boost).
Let me know if this works for you.
Note: you must have Microsoft DAO 3.5 object library (or higher version) in your references for this to work. Let me know if you need help checking this.
------------Step 1-------------
In your access front end, create a table called “tblSQLTables”.
The table needs three fields all of type Text:
SQLTable
SQLDatabase
SQLServer
Enter the name of every table you are currently linking to on the back end in field [SQLTable]. Enter the name of the SQL database in field [SQLDatabase], and lastly enter the path in the field [SQLServer] (it will help to just set these as default values in the table structure)
For example:
[SQLTable] [SQLDatabase] [SQLServer]
tbl_Accting_Reps SPCONTACT WH-SQLDEVVS01
tbl_Agency_Codes SPCONTACT WH-SQLDEVVS01
And so on and so forth for every table.
------------Step 2-------------
Then create a module, and save it as “basStartup”. Inside basStartup paste this code:
Function Startup()
DeleteLinks
LinkTables
End Function
------------Step 3-------------
Then create a module, call it “basLinkTables”
Paste the below code into this module:
Public Function DeleteLinks()
' This will erase all the linked tables that you put in the tblSQLTables table you created (they will be recreated with the next function)
Dim db As DAO.Database
Dim RST As DAO.Recordset
Dim tdf As DAO.TableDef
Dim strTable As String
Set db = CurrentDb()
Set RST = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
‘Forms.frmSPLASH.[LblFunction].Caption = "Deleting:" ‘This code is if you have some sort of splash screen that loads on open, I have
‘Forms.frmSPLASH.[lblLoading].Caption = "Expired Table Links" ‘Left it as a comment in case you want to modify it.
‘DoCmd.RepaintObject acForm, "frmSPLASH"
On Error GoTo HandleErr
Do Until RST.EOF
strTable = RST!SQLTable
CurrentDb.Execute "DROP TABLE [" & strTable & "]"
RST.MoveNext
Loop
RST.Close
Set RST = Nothing
Set db = Nothing
ExitHere:
Exit Function
HandleErr:
MsgBox Err & ": " & Err.Description, , "Error in DeleteLinks()"
Resume ExitHere
Resume
End Function
Public Function LinkTables()
‘This function then dynamically re-links the tables you have listed in the tblSQLTables table you created.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim RST As DAO.Recordset
Dim strServer As String
Dim strDB As String
Dim strTable As String
Dim strConnect As String
Dim strMsg As String
On Error GoTo HandleErr
'Build base Authentication strings.
‘You must enter your proper Server path, and Database name here.
strConnect = "ODBC;Driver={SQL Server};Server=WH-SQLDEVVS01;Database=SPContact;Trusted_Connection=Yes"
'Create a recordset to obtain server object names
Set db = CurrentDb()
Set RST = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
If RST.EOF Then
strMsg = "There are no tables listed to link to."
MsgBox strMsg, , "No tables"
GoTo ExitHere
End If
‘Forms.frmSPLASH.[LblFunction].Caption = "Relinking:" This is another part of the splash form feature, left as a comment
Do Until RST.EOF
strServer = RST!SQLServer
strDB = RST!SQLDatabase
strTable = RST!SQLTable
‘Forms.frmSPLASH.[lblLoading].Caption = strTable This is again for a splash form. This cool trick updates a label on the splash form
‘DoCmd.RepaintObject acForm, "frmSPLASH" and shows them the table names that are relinking. I left it as a comment in case you want it.
' Create a new TableDef object.
Set tdf = db.CreateTableDef(strTable)
' Set the Connect property to establish the link
tdf.Connect = strConnect & "Server=" & strServer & ";Database=" & strDB & ";"
tdf.SourceTableName = strTable
' Append to the database's TableDefs colelction.
db.TableDefs.Append tdf
RST.MoveNext
Loop
RST.Close
Set RST = Nothing
Set tdf = Nothing
Set db = Nothing
ExitHere:
Exit Function
HandleErr:
Select Case Err
Case Else
strMsg = Err & ": " & Err.Description
Exit Function
End Select
End Function
------------Step 4-------------
Create a Macro and save it as "autoexec", and use the action "RunCode". In the RunCode argument type “Startup()” (without the “”) A macro saved with this name will automatically run when the database opens.
------------Step 5--------------
That’s it! If you want to use a splash form, then create a form and save it as "frmSplash". In the form, put two labels in it, one named "LblFunction" and one "lblLoading" (put a period, or , or anything inside the label so you can create it.). In the Startup() function add DoCmd.OpenForm “frmSplash”
Example:
Function Startup()
DoCmd.OpenForm
DeleteLinks
LinkTables
End Function
Take off the comments ‘ I put on the frmSplash code I left intact above, and your set.
