sharris_glascol
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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(stLocal TableName 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(s tLocalTabl eName, 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
'//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(stLocal
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
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(s
CurrentDb.TableDefs.Append
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
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
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= NameOfSQLS erverDb;Tr usted_Conn ection=Yes
ODBC;DRIVER=SQL Server;SERVER=DevSQLServer ;DATABASE= NameOfSQLS erverDb;Tr usted_Conn ection=Yes
OM Gang
Table columns/fields are : SQLdbName PRD_ConnectString DEV_ConnectString
NameOfSQLServerDb
ODBC;DRIVER=SQL Server;SERVER=TheSQLServer
ODBC;DRIVER=SQL Server;SERVER=DevSQLServer
OM Gang
ASKER
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
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
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
ASKER
Where does this process go?
But there should be nothing to update ...
You are facing a non-issue.
/gustav
You are facing a non-issue.
/gustav
ASKER
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
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;DATAB ASE=BigBer thaDb;Trus ted_Connec tion=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
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;DATAB
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
ASKER
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
OM Gang
ASKER
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?
ASKER
Thanks
/gustav