Need code that can re point linked access tables to localdb or sql server depending on user choice

I have an access application that uses a sql server express database.  The application works well but i am wanting to have it work with a localdb or a sql server express

When the application is open i will read the ini file to determine what database it will use,

Right now i have all the tables as linked tables to the sql server database, i would like to be able to have each of those tables re-mapped to the localdb with an attachedbfile= and the path of the localDB.  

If it is run and the sql server is chosen it would remap those linked tables using an dnsless connection to the sql server.

Is this possible.  I have some code that remaps the sql server part to a new DSN and it seems to work ok, but i can't seem to modify it to get it to work for a localdb

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

There is no way to automatically swap ODBC to Jet/ACE.  I've attached a database with a form I use in one of my applications.  It is out of context so I hope I didn't break anything important when I pulled this together.  The idea is to Keep a table with a list of all the tables and their source and target names because I assume you want to get rid of the dbo_ prefix so the code works regardless of which BE is linked.

To use the app
1. Delete all the linked tables.
2. Populate tblODBCDataSources with your table names

Give it a whorl.
Gustav BrockCIOCommented:
I have some code that remaps the sql server part to a new DSN and it seems to work ok, but i can't seem to modify it to get it to work for a localdb.

It should pose only a single problem to link to LocalDB.
A DSN file pointing to this looks like:

DRIVER=SQL Server Native Client 11.0
APP=Microsoft Office 2016

Open in new window

The tricky part is the SERVER entry, which can be hard to figure out.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
Is this for users, or for a developer?

I have a article about an improved Linked Table Manager for Access and SQL Server which is an Access add-in and is designed for developers.  This might meet your needs if you are looking for a developer application.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Fabrice LambertFabrice LambertCommented:

What does your INI file looks like ?

It can be done by browsing the tabledefs collection, and update the connect property.
mitaiAuthor Commented:
so i tried the code i have (below) and modified it to use the ODBC connection string provided by Gustav, when i do this i get an installable isam error message.  If i create an odbc entry using the (localdb)\MSSQLLocalDB, and then relink it with the DSN it works fine.  I am just trying to see if there is a way that i don't need to setup the odbc setting first and can do it right within access.

Here are my settings

    strConnectionString = "Driver={SQL Server Native Client 11.0};Server=(localdb)\MSSQLLocalDB;Trusted_Connection=Yes;AttachDBFile=G:\z\mydata.mdf"

This is the code i have been using to switch between different SQL servers and it works well, just when i try to do this and add a local server with an attachDBFile it doesn't seem to work

Sub FixConnections( _
    ServerName As String, _
    DatabaseName As String, _
    Optional UID As String, _
    Optional PWD As String _
' This code was originally written by
' Doug Steele, MVP
' Modifications suggested by
' George Hepworth, MVP
' You are free to use it in any application
' provided the copyright notice is left unchanged.
' Description:  This subroutine looks for any TableDef objects in the
'               database which have a connection string, and changes the
'               Connect property of those TableDef objects to use a
'               DSN-less connection.
'               It then looks for any QueryDef objects in the database
'               which have a connection string, and changes the Connect
'               property of those pass-through queries to use the same
'               DSN-less connection.
'               This specific routine connects to the specified SQL Server
'               database on a specified server.
'               If a user ID and password are provided, it assumes
'               SQL Server Security is being used.
'               If no user ID and password are provided, it assumes
'               trusted connection (Windows Security).
' Inputs:   ServerName:     Name of the SQL Server server (string)
'           DatabaseName:   Name of the database on that server (string)
'           UID:            User ID if using SQL Server Security (string)
'           PWD:            Password if using SQL Server Security (string)

On Error GoTo Err_FixConnections

Dim dbCurrent As DAO.Database
Dim prpCurrent As DAO.Property
Dim tdfCurrent As DAO.TableDef
Dim qdfCurrent As DAO.QueryDef
Dim intLoop As Integer
Dim intToChange As Integer
Dim strConnectionString As String
Dim strDescription As String
Dim strQdfConnect As String
Dim typNewTables() As TableDetails

' Start by checking whether using Trusted Connection or SQL Server Security

  If (Len(UID) > 0 And Len(PWD) = 0) Or (Len(UID) = 0 And Len(PWD) > 0) Then
    MsgBox "Must supply both User ID and Password to use SQL Server Security.", _
      vbCritical + vbOKOnly, "Security Information Incorrect."
    Exit Sub
    If Len(UID) > 0 And Len(PWD) > 0 Then

' Use SQL Server Security

      strConnectionString = "ODBC;DRIVER={SQL Server Native Client 11.0};" & _
        "DATABASE=" & DatabaseName & ";" & _
        "SERVER=" & ServerName & ";" & _
        "UID=" & UID & ";" & _
        "PWD=" & PWD & ";"

' Use Trusted Connection

      strConnectionString = "ODBC;DRIVER={SQL Server Native Client 11.0};" & _
        "DATABASE=" & DatabaseName & ";" & _
        "SERVER=" & ServerName & ";" & _
    End If
  End If

  intToChange = 0

  Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

' Build a list of all of the connected TableDefs and
' the tables to which they're connected.

  For Each tdfCurrent In dbCurrent.TableDefs
    If Len(tdfCurrent.Connect) > 0 Then
     ' If UCase$(Left$(tdfCurrent.Connect, 5)) = "ODBC;" Then
        ReDim Preserve typNewTables(0 To intToChange)
        typNewTables(intToChange).Attributes = tdfCurrent.Attributes
        typNewTables(intToChange).TableName = tdfCurrent.Name
        typNewTables(intToChange).SourceTableName = tdfCurrent.SourceTableName
        typNewTables(intToChange).IndexSQL = GenerateIndexSQL(tdfCurrent.Name)
        typNewTables(intToChange).Description = Null
        typNewTables(intToChange).Description = tdfCurrent.Properties("Description")
        intToChange = intToChange + 1
     ' End If
    End If

' Loop through all of the linked tables we found

  For intLoop = 0 To (intToChange - 1)

' Delete the existing TableDef object

    dbCurrent.TableDefs.Delete typNewTables(intLoop).TableName

' Create a new TableDef object, using the DSN-less connection

    Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
    tdfCurrent.Connect = strConnectionString

' Unfortunately, I'm current unable to test this code,
' but I've been told trying this line of code is failing for most people...
' If it doesn't work for you, just leave it out.
    tdfCurrent.Attributes = DB_ATTACHSAVEPWD

    tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
    dbCurrent.TableDefs.Append tdfCurrent

' Where it existed, add the Description property to the new table.

    If IsNull(typNewTables(intLoop).Description) = False Then
      strDescription = CStr(typNewTables(intLoop).Description)
      Set prpCurrent = tdfCurrent.CreateProperty("Description", dbText, strDescription)
      tdfCurrent.Properties.Append prpCurrent
    End If

' Where it existed, create the __UniqueIndex index on the new table.

'    If Len(typNewTables(intLoop).IndexSQL) > 0 Then
'      dbCurrent.Execute typNewTables(intLoop).IndexSQL, dbFailOnError
'    End If
' Loop through all the QueryDef objects looked for pass-through queries to change.
' Note that, unlike TableDef objects, you do not have to delete and re-add the
' QueryDef objects: it's sufficient simply to change the Connect property.
' The reason for the changes to the error trapping are because of the scenario
' described in Addendum 6 below.

  For Each qdfCurrent In dbCurrent.QueryDefs
    On Error Resume Next
    strQdfConnect = qdfCurrent.Connect
    On Error GoTo Err_FixConnections
    If Len(strQdfConnect) > 0 Then
      If UCase$(Left$(qdfCurrent.Connect, 5)) = "ODBC;" Then
        qdfCurrent.Connect = strConnectionString
      End If
    End If
    strQdfConnect = vbNullString
  Next qdfCurrent

  Set tdfCurrent = Nothing
  Set dbCurrent = Nothing
  Exit Sub

' Specific error trapping added for Error 3291
' (Syntax error in CREATE INDEX statement.), since that's what many
' people were encountering with the old code.
' Also added error trapping for Error 3270 (Property Not Found.)
' to handle tables which don't have a description.

  Select Case Err.Number
    Case 3270
      Resume Next
    Case 3291
      MsgBox "Problem creating the Index using" & vbCrLf & _
        typNewTables(intLoop).IndexSQL, _
        vbOKOnly + vbCritical, "Fix Connections"
      Resume End_FixConnections
    Case 18456
      MsgBox "Wrong User ID or Password.", _
        vbOKOnly + vbCritical, "Fix Connections"
      Resume End_FixConnections
    Case Else
      MsgBox Err.Description & " (" & Err.Number & ") encountered", _
        vbOKOnly + vbCritical, "Fix Connections"
      Resume End_FixConnections
  End Select

End Sub

Function GenerateIndexSQL(TableName As String) As String
' This code was originally written by
' Doug Steele, MVP
' Modifications suggested by
' George Hepworth, MVP
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
' Description: Linked Tables should have an index __uniqueindex.
'              This function looks for that index in a given
'              table and creates an SQL statement which can
'              recreate that index.
'              (There appears to be no other way to do this!)
'              If no such index exists, the function returns an
'              empty string ("").
' Inputs:   TableDefObject: Reference to a Table (TableDef object)
' Returns:  An SQL string (or an empty string)

On Error GoTo Err_GenerateIndexSQL

Dim dbCurr As DAO.Database
Dim idxCurr As DAO.Index
Dim fldCurr As DAO.Field
Dim strSQL As String
Dim tdfCurr As DAO.TableDef

  Set dbCurr = CurrentDb()
  Set tdfCurr = dbCurr.TableDefs(TableName)

  If tdfCurr.Indexes.Count > 0 Then

' Ensure that there's actually an index named
' "__UnigueIndex" in the table

    On Error Resume Next
    Set idxCurr = tdfCurr.Indexes(0)
    If Err.Number = 0 Then
      On Error GoTo Err_GenerateIndexSQL

' Loop through all of the fields in the index,
' adding them to the SQL statement

      If idxCurr.Fields.Count > 0 Then
        strSQL = "CREATE INDEX __UniqueIndex ON [" & TableName & "] ("
        For Each fldCurr In idxCurr.Fields
          strSQL = strSQL & "[" & fldCurr.Name & "], "

' Remove the trailing comma and space

        strSQL = Left$(strSQL, Len(strSQL) - 2) & ")"
      End If
    End If
  End If

  Set fldCurr = Nothing
  Set tdfCurr = Nothing
  Set dbCurr = Nothing
  GenerateIndexSQL = strSQL
  Exit Function

' Error number 3265 is "Not found in this collection
' (in other words, either the tablename is invalid, or
' it doesn't have an index named __uniqueindex)
  If Err.Number <> 3265 Then
    MsgBox Err.Description & " (" & Err.Number & ") encountered", _
      vbOKOnly + vbCritical, "Generate Index SQL"
  End If
  Resume End_GenerateIndexSQL

End Function

Open in new window

I gave you a working sample.  Did you not bother with it?  Was it too easy?
Dale FyeOwner, Developing Solutions LLCCommented:
Likewise, my add-in is extremely easy to install, and removes all of the guess work.
mitaiAuthor Commented:
Hi Pat, i did take a look at your code and it works well for JET but when i am trying to use if for the SQL LocalDB it does not work.  You need to use the microsoft SQL Native Driver 11.0 and the AttachDBFile to attach to the .mdf database.  I can do this by creating an ODBC connection in the ODBC manager, but can't seem to get it to work in code.  

Sorry if i didn't mention that earlier,
mitaiAuthor Commented:
Hi Dale, i did get a chance to look at yours finally, but how do you add a localDB  (sql server) database to your list.  IE how do you attach the .mdf file?
mitaiAuthor Commented:
I think some of the confusion is when i said localdb i didn't mean a local access database, i meant the sql server distributed version or localDB version.  This is the one that is super light and can be distributed free.  It runs on the desktop and will start when it receives the first connection, shuts down when the last connection closes.

Sorry if i confused people.
Yes.  That did muddy the waters.  The code I provided allows you to swap SQL Server for Jet/ACE and vice versa which is quite a different thing.

I don't have any code for attaching a .dbf
Gustav BrockCIOCommented:
This simple code works for my LocalDB with a database named Test:

Public Function ConnectionString( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal Password As String) _
    As String

' Create ODBC connection string from its variable elements.
' 2016-04-24. Cactus Data ApS, CPH.

    Const AzureDomain   As String = ""
    Const OdbcConnect   As String = _
        "ODBC;" & _
        "DRIVER=SQL Server Native Client 11.0;" & _
        "Description=Application Name;" & _
        "APP=Microsoft? Access;" & _
        "SERVER={0};" & _
        "DATABASE={1};" & _
        "UID={2};" & _
        "PWD={3};" & _
'    Const cstrConnect   As String = _
'        "ODBC;Driver=SQL Server Native Client 11.0;Server=(localdb)\MSSQLLocalDB;Database=Test;Trusted_Connection=Yes"

    Dim FullConnect     As String
    If Right(Hostname, Len(AzureDomain)) = AzureDomain Then
        ' Azure SQL connection.
        ' Append servername to username.
        Username = Username & "@" & Split(Hostname)(0)
    End If
    FullConnect = OdbcConnect
    FullConnect = Replace(FullConnect, "{0}", Hostname)
    FullConnect = Replace(FullConnect, "{1}", Database)
    FullConnect = Replace(FullConnect, "{2}", Username)
    FullConnect = Replace(FullConnect, "{3}", Password)
    FullConnect = Replace(FullConnect, "{4}", IIf(Username & Password = "", "Yes", "No"))
    ConnectionString = FullConnect

End Function

Public Function AttachSqlServer( _
    ByVal Hostname As String, _
    ByVal Database As String, _
    ByVal Username As String, _
    ByVal Password As String) _
    As Boolean

' Attach all tables linked via ODBC to SQL Server or Azure SQL.
' 2016-04-24. Cactus Data ApS, CPH.

    Const cstrDbType    As String = "ODBC"
    Const cstrAcPrefix  As String = "dbo_"

    Dim dbs             As DAO.Database
    Dim tdf             As DAO.TableDef
    Dim qdf             As DAO.QueryDef
    Dim strConnect      As String
    Dim strName         As String
    On Error GoTo Err_AttachSqlServer
    Set dbs = CurrentDb
    strConnect = ConnectionString(Hostname, Database, Username, Password)
    For Each tdf In dbs.TableDefs
        strName = tdf.Name
        If Asc(strName) <> Asc("~") Then
            If InStr(tdf.Connect, cstrDbType) = 1 Then
                If Left(strName, Len(cstrAcPrefix)) = cstrAcPrefix Then
                    tdf.Name = Mid(strName, Len(cstrAcPrefix) + 1)
                End If
                tdf.Connect = strConnect
                Debug.Print Timer, tdf.Name, tdf.SourceTableName, tdf.Connect
            End If
        End If
    For Each qdf In dbs.QueryDefs
        If qdf.Connect <> "" Then
            Debug.Print Timer, qdf.Name, qdf.Type, qdf.Connect
            qdf.Connect = strConnect
        End If
    Debug.Print "Done!"
    AttachSqlServer = True
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Function
 '   Call ErrorMox
    Resume Exit_AttachSqlServer
End Function

Open in new window

It will also reconnect Pass-Through queries, should you have any.
Replace the error display function with your own.

Call it like this:

Dim Attached As Boolean
Attached = AttachSqlServer("(localdb)\MSSQLLocalDB","Test","","")

Open in new window

John TsioumprisSoftware & Systems EngineerCommented:
You have to include the database name along with AttachdbFileName..something like this

Open in new window

mitaiAuthor Commented:
sorry guys, it turns out that my database wasn't connected correctly and when i did move it over it was in a format that was not compatible with the sql localdb version i was running.  All works well now
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.