Solved

Deploy Access 2013 database

Posted on 2014-11-12
18
200 Views
Last Modified: 2014-11-17
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?
0
Comment
Question by:sharris_glascol
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
18 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 40438203
I do it with DSN-less connection strings.
I have a local table, in the Access db, that has the connection strings for Production and Test SQL servers.
I have a VBA routine to link/re-link SQL the server tables.

OM Gang
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40438230
Or you can create a file DSN and distribute this file as well.

/gustav
0
 

Author Comment

by:sharris_glascol
ID: 40438507
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sharris_glascol
ID: 40438742
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
0
 
LVL 28

Expert Comment

by:omgang
ID: 40438781
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

0
 
LVL 28

Expert Comment

by:omgang
ID: 40438787
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
0
 

Author Comment

by:sharris_glascol
ID: 40439788
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.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40439819
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
0
 
LVL 28

Expert Comment

by:omgang
ID: 40439972
"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
0
 

Author Comment

by:sharris_glascol
ID: 40440159
Where does this process go?
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40440175
But there should be nothing to update ...
You are facing a non-issue.

/gustav
0
 

Author Comment

by:sharris_glascol
ID: 40440188
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
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40440199
It should look for it where it found it last.
What does your connection string looke like?

/gustav
0
 
LVL 28

Expert Comment

by:omgang
ID: 40440224
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
0
 

Author Comment

by:sharris_glascol
ID: 40440322
What is the best way to deploy it to the other machines?
0
 
LVL 28

Expert Comment

by:omgang
ID: 40440519
Each user should have their own copy of the Access db on their local machine.
OM Gang
0
 

Author Comment

by:sharris_glascol
ID: 40440545
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?
0
 

Author Closing Comment

by:sharris_glascol
ID: 40447370
Thanks
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question