Link to home
Start Free TrialLog in
Avatar of sharris_glascol
sharris_glascolFlag for United States of America

asked on

Deploy Access 2013 database

I have a 2013 access database that has links to Sql server.  I need to deploy this to multiple machines.  How can I do there so when I install that all of the connections to the sql server are the same?
ASKER CERTIFIED SOLUTION
Avatar of omgang
omgang
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or you can create a file DSN and distribute this file as well.

/gustav
Avatar of sharris_glascol

ASKER

any examples??  I am new to this.  I thought you use to be able to create a access deployment file.  All of my tables in my database are linked from Sql.
so.. omgang do you create local tables inside the database and then use this code to link the tables to the sql server?  for instance I have a table that I am linking to in sql call dbo.emp_course.  would I create a local table called that and create a macro inside it with this code?  

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
   
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
     
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
   
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function
Initially, you can link the tables manually and then use the procedure to change the ODBC connect string from using a local DSN to a DSNless connection.  Following is the procedure I use.
OM Gang

Public Function RefreshTableLinks(strEnvironment As String, strAddRefresh As String, _
        Optional strTableName As String) As String
'strEnvironment should be either 'production' or 'development'
'refreshes existing ODBC table links or adds new ODBC table link depending on value of strAddRefresh
'use strAddRefresh = refresh  or  strAddRefresh = add
'new ODBC linked table is added as name = strTableName with _ and first four characters of strEnvrionment appended
'returns "fail" on fail, returns name linked table (add) or name of last linked table (refresh) on success
On Error GoTo Err_RefreshTableLinks

    Dim tdf As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim strLink As String, strDBName As String, strLocalTableName As String
    Dim strMsg As String, strProdLink As String, strDevLink As String
    Dim strSuffix As String, strResult As String
    Dim strLinkedTableName As String
    
        'initialize return variable
    strResult = "fail"
    
        'make sure we were passed expected values
    If strEnvironment <> "production" And strEnvironment <> "development" Then GoTo Exit_RefreshTableLinks
    If strAddRefresh <> "refresh" And strAddRefresh <> "add" Then GoTo Exit_RefreshTableLinks
    
        'name of local table with connection string info
    strLocalTableName = "SQL_ConnectionStrings"
    
        'name of SQL database we want to reconnect to
    strDBName = "NameOfSQLServerDb"
    
        'open recordset (as dynaset so we can use Find methods) on table and find record
        'for named database
    Set rs = CurrentDb.OpenRecordset(strLocalTableName, dbOpenDynaset)
    rs.FindFirst "SQLdbName = " & Chr(34) & strDBName & Chr(34)
    If rs.NoMatch Then
        strMsg = "No record for the database " & strDBName & " could be found in table " _
                & strTableName & "."
        MsgBox strMsg, , "Error Re-Linking SQL Tables"
        GoTo Exit_RefreshTableLinks
    End If
    
        'retrieve both production and development connection strings from table
    strProdLink = rs!PRD_ConnectString
    strDevLink = rs!DEV_ConnectString
    
    Select Case strEnvironment
        Case "production"
            strLink = strProdLink
        
        Case "development"
            strLink = strDevLink
            
        Case Else   'do nothing
    
    End Select
    
        'proceed based upon passed value of strAddRefresh
    Select Case strAddRefresh
        Case "refresh"
                'iterate through table definitions and refresh links for tables that have connection
                'strings
            For Each tdf In CurrentDb.TableDefs
                    'get table name as return value
                strResult = tdf.Name
                If Len(tdf.Connect) > 0 Then
                    If InStr(tdf.Connect, "SQL Server") Then
                        tdf.Connect = strLink
                    End If
                    tdf.RefreshLink
                End If
            Next tdf
            
        Case "add"
                'make sure we received a non-empty value for the option table name variable
            If strTableName = "" Then
                strMsg = "You must pass a valid table name for the table you want to link to."
                MsgBox strMsg, , "Function RefreshTableLinks"
                GoTo Exit_RefreshTableLinks
            End If
                'create suffix string we'll append to passed table name
            strSuffix = "_" & Left(strEnvironment, 4)
                'create name for linked table
            strLinkedTableName = "dbo_" & strTableName & strSuffix
                'link table using the specified connection string
            DoCmd.TransferDatabase acLink, "ODBC Database", strLink, acTable, strTableName, _
                    strLinkedTableName
                'set return value to table name
            strResult = strLinkedTableName

        Case Else
            'do nothing
            
    End Select
        
Exit_RefreshTableLinks:
        'clear object variables
    Set tdf = Nothing
    Set rs = Nothing
        'function return value
    RefreshTableLinks = strResult
    Exit Function
    
Err_RefreshTableLinks:
    MsgBox Err.Number & ", " & Err.Description, , "Error in function RefreshTableLinks"
    Resume Exit_RefreshTableLinks

End Function

Open in new window

And here's an example of a connection string I have in the local table

Table columns/fields are :  SQLdbName             PRD_ConnectString            DEV_ConnectString
NameOfSQLServerDb
ODBC;DRIVER=SQL Server;SERVER=TheSQLServer;DATABASE=NameOfSQLServerDb;Trusted_Connection=Yes
ODBC;DRIVER=SQL Server;SERVER=DevSQLServer;DATABASE=NameOfSQLServerDb;Trusted_Connection=Yes

OM Gang
Ok I am very new at this.  So I have a database that has 20 tables that I am linking to sql.  Do I create a local table in access for each of those?  Where do I put the code above?  an event procedure for on open?  Sorry Really confused.
If this is in-house distribution, have you tried simply to copy the application to another machine and run it from this?
As all machines can see your database server, nothing should need to be changed.

/gustav
"Do I create a local table in access for each of those?"
No, the procedure loops through ALL tables in the local (Access) database looking for tables that have connection strings (linked tables).  It updates/replaces the connection string for each of the linked tables with either the Production or Development connection string (depending upon which parameter you pass to the procedure).

If/when you add a new table to the SQL Server database, or simply need to link a SQL Server database table that isn't currently linked to the Access db, you run the procedure passing in "add" as the second parameter (instead of "refresh") and you then pass in a third parameter of the SQL Server database table name you wish to link.

OM Gang
Where does this process go?
But there should be nothing to update ...
You are facing a non-issue.

/gustav
I have I go and put a shortcut of the database on another machine it doesn't know where to look for the sql database
It should look for it where it found it last.
What does your connection string looke like?

/gustav
Perhaps best if you tell us how you have the Access db deployed.  Is it on a file server and your users are running it as a shared file?  (not good by the way).  Each user has a copy of the Access db on their own workstation?  Did you use/create a file DSN (as Gustav indicated) for the ODBC connection to the SQL Server database?

I've attached an Access db with the table and the procedure so you can see how it works.  You need to modify in a couple of places.
In the table, for either (or both) the PROD or the DEV connection strings, you need to specify the SQL Server and the SQL Server Db name
E.g., if your SQL Server is named Bertha and your SQL Server Db name is BigBerthaDb then the connection string should be
ODBC;DRIVER=SQL Server;SERVER=Bertha;DATABASE=BigBerthaDb;Trusted_Connection=Yes

Make that/those change(s) and then run the procedure.  If you changed the PROD connection string in the table you'd pass in "production" as the first parameter.  Pass in "add" as the second parameter and you'll need to pass in a valid table name (from the SQL Server db) as the third parameter.  The procedure will create a linked table in your Access db to the table specified.
OM Gang
Database2.zip
What is the best way to deploy it to the other machines?
Each user should have their own copy of the Access db on their local machine.
OM Gang
Attached is the database I am working with how would I incorporate your code in or should I just leave the ODBC file link in there?
Thanks