[AccessD] How to test with SQL Server data

Here's the sort of thing  I use (I generally use rely on WIndows Authentication so I don't need 
to worry about logon credentials in the connection string.  Lets me connect to a local Access 
or remote SQL Server BE. Could just as easily use two different Connection strings:

Const strBEName = "\NECDecision_BE.mdb"

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


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

Function ConnectBELocal() As Boolean
Dim tdf As TableDef
If Dir$(CurrentProject.Path & strBEName < " " Then
     MsgBox "Data file " & mid$(strBEName,1) & " is 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 & strBEName, 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 12 Aug 2011 at 14:50, jwcolby wrote:

> When I use an Access BE I use batch files to perform drive mapping to
> cause X: to map to a shared drive on my local system (MapLocal) and to
> map to the live data location (MapLive).  Once I have the map correct
> I simply link the FE to the BE through drive X and I am in business.
> 
> I use
> 
> NetUse /Delete X:
> NetUse X: \\DiscoSvr\DiscoProd
> 
> to map to the live and I use
> 
> NetUse /Delete X:
> NetUse X: \\DisabilityINS\Dev
> 
> to map local to my workstation.
> 
> I can then keep the live FE / BE and libraries and copy them to my
> workstation, MapLocal and voila I am working on local data on my
> system.  Make a change to the live BE means I have to copy that to my
> workstation.  Make a change to the FE, send it to test and then they
> eventually push it to LIVE.
> 
> Works well.
> 
> I am about to migrate them to SQL Server and then I will (initially)
> be linked to a specific SQL Server / database / set of tables.  I can
> no longer just change the mapping of drive X to switch between live
> and local data.
> 
> So how do I achieve the same effect, have a live data store and a
> local data store so that I can test on my "local" data without fear of
> destroying "live" data?
> 
> Am I going to have to run a program that edits the link data?  I know
> that the Tabledef has the server / username stuff it in at K can if
> necessary edit that to point to the right server IP, or perhaps a
> different database on that server.  OTOH that seems to be the easy
> part.  How do I cause changes in the table to be reflected in the
> "local" database?  How do I get the most recent data? Do I backup /
> restore the database to a new (local) name?
> 
> I am just beginning to address these issues in preparation for the
> migration.  Any wisdom will be appreciated.
> 
> -- 
> John W. Colby
> www.ColbyConsulting.com
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>