Relinking ODBC tables to Test DB

[flipflaps]

Hi,

first timer in the forum, so be gentle!

I have an Access2007 front end connected to SQL Server 2008 with several linked tables. On the server there is the live DB (LLOM) and every night the server creates a duplicate DB (LLOM_TEST) for testing purposes.

I can of course manually re-link the tables to the test DB, but what I would like to do is when certain users log in to the front end, present them with the option to use the Live or Test data - the form to do this etc is not an issue but it is the code to re-map or re-link the linked tables to the correct DB that I haven't got a clue where to start.

I have searched the forums for similar threrads but cannot find anything - any help will be most gratefully received!

 

[VincilLabs]

Does the name of the test DB change every time or does it remain the same; do the table names stay the same? Are you updating records or are you just selecting?

I was thinking that you may be able to use passthrough queries.

 

[flipflaps]

Hi VincilLabs,

The DB names will always be the same. The table and field names will remain identical between the Live & Test systems.

Data I am not worried about in the Test DB - the overnight script basically just makes a complete copy of the Live DB (It is backed up separately also) so nothing needs to happen to the data at all.

Hope that helps.

 

[sxschech]

We're using Oracle ODBC, so perhaps this would work. Kind of a lazy way, essentially there are two copies of the pass through query and depending on what the user selects on the form using option buttons, it copies the query and that has the connection string to the appropriate schema. You could use tables instead of query I imagine.

Private Sub Frame1_AfterUpdate()
    'Choose which schema to use to pull data
    'from Oracle.
    '05-JUN-2011
    
    Select Case Me.Frame1
        Case 1 'Live Schema
            DoCmd.CopyObject , "qryDB", acQuery, "qryLIVE"
        Case 2 'Test Schema
            DoCmd.CopyObject , "qryDB", acQuery, "qryTEST"
    End Select
End Sub

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?t=204413
Look at this and see if it is closer to what you are looking for.
If it is, there is a For Each Table loop that can be used.
DCrake code further down on the link is worth reviewing

Since you asked about ODBC
http://www.access-programmers.co.uk/forums/showthread.php?t=143180
Post number 7 shows an ODBC example.

 

[flipflaps]

Thanks to all for the suggestions and I have now got it working using the following code so that I can call it when required and make sure a positive result is returned at the end of the process.


Public Function ODBCmapping(dbname As String) As Boolean
On Error GoTo err_ODBCmapping

DoCmd.Echo True, "Relinking Tables - please wait"

Dim tdf As DAO.TableDef
Dim tblname As String

'Loop through all tables
For Each tdf In CurrentDb.TableDefs
'Only look at linked tables
If Len(tdf.Connect) > 0 Then
tblname = tdf.Name 'Pop into variable in case of error
DoCmd.Echo True, "Relinking Table " & tblname
tdf.Connect = "ODBC;Description=***;DRIVER=SQL Server;SERVER=***;UID=***;PWD=***;DATABASE=" & dbname
tdf.RefreshLink 'Relink table

End If
Next tdf

DoCmd.Echo True, "Finished"

ODBCmapping = True

exit_ODBCmapping:
ODBCmapping = False
Exit Function

err_ODBCmapping:
Call LogError(Err.Number, Err.Description, "ODBCmapping", "Table Name: " & tblname, False)
Resume exit_ODBCmapping

End Function


Again - thanks to everyone for your help!!

 

[Rx_]

Thanks for posting a solution, it will help others