On an access2007 databse, switch between a Access backend and SQL backend.

Hi

I currently have DAO code to determine a connection path for a Access backend.  I need to connect to a SQL backend I have not created yet. I was wondering if a connection string can be used similarly for a SQL backend (and Access backend) and assume the DAO code for updating links  can be changed to ADODB?  Can anyone please advise?


Public Const DB_String_Dev          As String = "DSN=MS Access Database;DBQ=C:\Users\MYSELF\Desktop\PUDA_DB_be.accdb;DefaultDir=C:\Users\MYSELF\Desktop;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
Public Const RELEASE_STAGE As String = "DEV"


Public Function DBConVer(DB_Con_Ver As String) As String
DBConVer = DB_String_Dev
End Function

'----------------------------DAO ---Update link module
Private Sub UpdateTblLinks()

Dim CurDB As DAO.Database
Dim tdfLinked As DAO.TableDef
Dim TBDef As DAO.TableDef
Dim strPath As String

On Error GoTo UpdateTblLinks_Err

strPath = DBConVer(RELEASE_STAGE)

Set CurDB = CurrentDb
For Each TBDef In CurDB.TableDefs
    If Len(TBDef.Connect) > 0 Then
        TBDef.Connect = ";Database" & _
        Right(Left(strPath, InStr(strPath, "DefaultDir=") - 2), Len(Left(strPath, InStr(strPath, "DefaultDir=") - 2)) - InStr(strPath, "DBQ=") - 2)
        TBDef.RefreshLink
    End If
UpdateTblLinks_Resume:
Next TBDef

'We've reached the end of the tables, so we can gracefully exit
GoTo UpdateTblLinks_Exit

UpdateTblLinks_Err:
If Err.Number = 3011 Then
    'clear the error and
    Err.Clear
    'resume execution of the loop
    GoTo UpdateTblLinks_Resume
Else
    MsgBox Err.Description
End If

UpdateTblLinks_Exit:
Set CurDB = Nothing
Set tdfLinked = Nothing
Set TBDef = Nothing

End Sub