[AccessD] Testing SQL Server linked FEs

<<Is anyone doing something like this?>>

  Yup.  Have done it two different ways:

1. With DSN's
2. Without DSN's

 With either, it's really no different then linking to a new JET DB; you're
just modifying the tabledefs connect string by either swapping a server name
or a DSN name.

  What I have is a table that has the DS for each company and just use
different company ID's when I need do to testing or a play area.

 Only wrinkle is that I have some tables in a common DB that are shared
across companies (ie. country or currency tables), so I have a flag if the
table entry should be switch or not on a company switched.

  Code below is swapping a DSN.

Jim.

Function ReattachODBCTables(strCompID As String) As Boolean

    ' Using usys_tblODBCDataSources, refresh ODBC connect string
    ' strings for all ODBC connections.
    ' If a Company ID is passed in, only refresh those tables
    ' indicated to be switched.
    ' If one is not passed in, refresh all links.

    Dim strTblName As String
    Dim strConn As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim tbl As DAO.TableDef
    Dim pb As New Form_frmProgBar

    Dim strDSN As String
    Dim intTableCount As Integer
    Dim intNumberOfTables As Integer
    Dim intProcess As Integer
    Dim varRet As Variant

10  On Error Resume Next

20  Set db = CurrentDb
30  Set rs = db.OpenRecordset("usys_tblODBCDataSources")
40  intNumberOfTables = rs.RecordCount
50  intTableCount = 0
60  pb.SetMessage "Reattaching ODBC tables"
70  pb.SetBarVisible True

80  With rs
90      While Not .EOF
100         If strCompID <> "" Then
110             If rs("CompanySwitch") = True Then
120                 intProcess = True
130                 strDSN = strCompID
140             Else
150                 intProcess = False
160             End If
170         Else
180             intProcess = True
190             strDSN = rs("DSN")
200         End If

210         If intProcess = True Then
                ' ---------------------------------------------
                ' Link table
                ' ---------------------------------------------
220             strTblName = rs("LocalTableName")
230             strConn = "ODBC;" & _
                          "DSN=" & strDSN & ";" & _
                          "APP=MicrosoftR Access"

240             If (DoesTblExist(rs("LocalTableName")) = False) Then
250                 Err = 0
260                 Set tbl = db.CreateTableDef(rs("LocalTableName"), _
                                                rs("ODBCTableName"), _
                                                strConn)
270                 db.TableDefs.Append tbl
280             Else
290                 Err = 0
300                 Set tbl = db.TableDefs(rs("LocalTableName"))
310                 tbl.Connect = strConn
320                 tbl.RefreshLink
330             End If

340             If Err <> 0 Then
350                 MsgBox "Error reattaching ODBC table " & strTblName, 16,
"Can't run the " & AppName()
360                 GoTo ReattachODBCTables_Err
370             End If


380         End If

390         intTableCount = intTableCount + 1
400         pb.SetBarPercent (intTableCount / intNumberOfTables) * 100

410         rs.MoveNext
420     Wend
430 End With

440 ReattachODBCTables = True

ReattachODBCTables_End:
450 On Error Resume Next

460 Set pb = Nothing

470 Set tbl = Nothing

480 rs.Close
490 Set rs = Nothing

500 Set db = Nothing

510 Exit Function

ReattachODBCTables_Err:
520 ReattachODBCTables = False
530 GoTo ReattachODBCTables_End

End Function

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, February 17, 2012 12:29 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Testing SQL Server linked FEs

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?
-- 
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com