[AccessD] Testing SQL Server linked FEs

You can either store it as in a table of as harcode it,depending on the circumstances.  You 
can also alternate between Access DBs and SQL DBs if you don't use passthrough queries 
etc:

Const strSQLLive = "ODBC;Description=DoT Policy Information System;DRIVER=SQL 
Server;SERVER=DOTSQL;APP=Microsoft Data Access 
Components;DATABASE=DoTPolicy;Trusted_Connection=Yes"

Function ConnectLive() As Long
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Left$(tdf.Name, 3) = "tbl" Then
         renewlink tdf.Name, strSQLLive, False
    End If
Next
ConnectLive = True
End Function

Function ConnectBELocal() As Boolean
Dim tdf As TableDef
If Dir$(CurrentProject.Path & "\NECDecisions_BE.mdb") < " " Then
     MsgBox "Data file NECDecisions_Be.mdb missing! It must be in the same directory as 
this application file.", vbCritical, "ConnectBELocal Failed!"
     ConnectBELocal = False
     Exit Function
 End If
For Each tdf In CurrentDb.TableDefs
    If Left$(tdf.Name, 3) = "tbl" Then
         renewlink tdf.Name, CurrentProject.Path & "\NECDecisions_BE.mdb", True
    End If
Next
ConnectBELocal = True
End Function


Function renewlink(tablename As String, datafile As String, AccessDb As Boolean) As Long
On Error Resume Next
DoCmd.DeleteObject acTable, tablename
On Error GoTo 0
Select Case AccessDb
Case True
DoCmd.TransferDatabase acLink, "Microsoft Access", datafile, acTable, tablename, 
tablename, False
Case False
DoCmd.TransferDatabase acLink, "ODBC Database", datafile, acTable, tablename, 
tablename, False
End Select
End Function




On 17 Feb 2012 at 12:29, jwcolby wrote:

> To this point I have always worked with MDB data stores.  When I did testing where I needed to 
> modify data (adds/ deletes / edits) I would copy the BE files to a local directory and then map that 
> local directory to the X: drive where all the links pointed to.  Voila, "test mode".
> 
> Obviously that doesn't work with links to SQL Server.  I have seen code for modifying the link info 
> of the tabledef, storing the server / database info in a local table in the FE.  That seems like one 
> solution.  Is anyone doing something like this?
> --