Solved

Deploy Access 2013 database

Posted on 2014-11-12
18
186 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
  • 8
  • 6
  • 4
18 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
Comment Utility
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 49

Expert Comment

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

/gustav
0
 

Author Comment

by:sharris_glascol
Comment Utility
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
 

Author Comment

by:sharris_glascol
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
"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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:sharris_glascol
Comment Utility
Where does this process go?
0
 
LVL 49

Expert Comment

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

/gustav
0
 

Author Comment

by:sharris_glascol
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
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
Comment Utility
What is the best way to deploy it to the other machines?
0
 
LVL 28

Expert Comment

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

Author Comment

by:sharris_glascol
Comment Utility
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
Comment Utility
Thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now