Change backend through code

[Djblois]

I was wondering it if is possible to change The backend through code? I want an easy way to switch between a test backend and the real one? To help prevent data mistakes when testing. Is this possible?

 

[dkinley]

Yes .. you can search around on the terms 'table relinking'. There is some code that is published out there and you can call it from a command button.

I am not sure about full automation because I never took it that far - just used it to navigate through directories to connect to the back end I wanted to.

-dk

 

[boblarson]

Check out this code here (courtesy of MVP Armen Stein):
http://www.jstreettech.com/files/JStreetAccessRelinker.zip

 

[Djblois]

Wow bob that is a lot of code for something I thought that should be so simple. lol

 

[allan57]

Below is the code I use to switch from a live area and a development area, in this example the FE Db connects to two diff live backend databases. The code simply looks for the live backend location and changes its to the development backend area.
Obviously you need to create the opposite Function to return to the live area before creating a users MDE Db

Public Function SwapTablesToDevelopmentArea()

Dim dbs As Database

Dim tdf As TableDef
    
On Error Resume Next
    
    Set dbs = CurrentDb
    
    dbs.TableDefs.Refresh
    
    For Each tdf In dbs.TableDefs
        
        With tdf
            
            If Len(.Connect) > 0 Then
                                
                If .Connect = ";DATABASE=N:\Access_Tables\Sensitising_Dry_Chem_Store\Sensitising_Dry_Chem_Store_be.mdb" Then
                
                    .Connect = ";DATABASE=N:\Access_Development\1_ACCESS_Program_Development_Testing_AREA\Dry_Chem_Store\Sensitising_Dry_Chem_Store_be.mdb"
                
                    .RefreshLink
                
                End If
        
                If .Connect = ";DATABASE=N:\Access_Tables\Sensitising_Solutions\Sensitising_Solutions_be.mdb" Then
                
                    .Connect = ";DATABASE=N:\Access_Development\1_ACCESS_Program_Development_Testing_AREA\Solutions\Sensitising_Solutions_be.mdb"
                
                    .RefreshLink
                
                End If
        
            End If
        
        End With
    
    Next
    
    Set tdf = Nothing
    
    dbs.Close
    Set dbs = Nothing
    
End Function