This is my code to open an external database. I open the external file fine. I just need to update the connection string for all objects in the external file?
this code is inherited from ADP projects and it was pretty simple. You can replace strAdpFile with strACCDBFile.
thanks!
Function ChangeACCDBExternalConnection(strAdpFile As String, strServerName As String, strDBName As String, Optional strUN As String, Optional strPW As String) As Boolean
Dim strConnect As String
On Error GoTo EH:
strConnect = strActConn = "DRIVER=SQL Server;SERVER=" & strServerName & ";UID=" & strUN & ";PWD=" & strPW & ";APP=Microsoft Office;DATABASE=" & strDBName
'On Error GoTo Error_Handler
'Early binding
'Use the following line if being used in Access or using Access reference
' provides intellisense!
Dim oAccess As Access.Application
'Late binding
'Use the following line if being used outside of Access without an Access reference
' Dim oAccess As Object
Set oAccess = CreateObject("Access.Application") 'Create a new Access instance
With oAccess
.OpenCurrentDatabase strAdpFile 'Open the specified db
.Visible = True 'Ensure it is visible to the end-user
.UserControl = True
' .DoCmd.OpenForm "YourFormName" 'Open a form?
' .DoCmd.RunMacro "YourMacroName" 'Run a Macro?
End With
If Not oAccess Is Nothing Then Set oAccess = Nothing
'Dim appAccess As Object
'Set appAccess = CreateObject("Access.Application")
'With appAccess
'.Visible = False
'.OpenCurrentDatabase strAdpFile
'Set currenproject = appAccess.CurrentDb
'appAccess.CurrentProject.CloseConnection
'appAccess.CurrentProject.OpenConnection strConnect
'.CloseCurrentDatabase
'.Quit acQuitSaveNone
'End With
ChangeACCDBExternalConnection = True
Exit Function
EH:
MsgBox err.NUMBER & ": " & err.DESCRIPTION, vbCritical, "Connection Error"
ChangeACCDBExternalConnection = False
Set appAccess = Nothing
End Function
this code is inherited from ADP projects and it was pretty simple. You can replace strAdpFile with strACCDBFile.
thanks!
Function ChangeACCDBExternalConnection(strAdpFile As String, strServerName As String, strDBName As String, Optional strUN As String, Optional strPW As String) As Boolean
Dim strConnect As String
On Error GoTo EH:
strConnect = strActConn = "DRIVER=SQL Server;SERVER=" & strServerName & ";UID=" & strUN & ";PWD=" & strPW & ";APP=Microsoft Office;DATABASE=" & strDBName
'On Error GoTo Error_Handler
'Early binding
'Use the following line if being used in Access or using Access reference
' provides intellisense!
Dim oAccess As Access.Application
'Late binding
'Use the following line if being used outside of Access without an Access reference
' Dim oAccess As Object
Set oAccess = CreateObject("Access.Application") 'Create a new Access instance
With oAccess
.OpenCurrentDatabase strAdpFile 'Open the specified db
.Visible = True 'Ensure it is visible to the end-user
.UserControl = True
' .DoCmd.OpenForm "YourFormName" 'Open a form?
' .DoCmd.RunMacro "YourMacroName" 'Run a Macro?
End With
If Not oAccess Is Nothing Then Set oAccess = Nothing
'Dim appAccess As Object
'Set appAccess = CreateObject("Access.Application")
'With appAccess
'.Visible = False
'.OpenCurrentDatabase strAdpFile
'Set currenproject = appAccess.CurrentDb
'appAccess.CurrentProject.CloseConnection
'appAccess.CurrentProject.OpenConnection strConnect
'.CloseCurrentDatabase
'.Quit acQuitSaveNone
'End With
ChangeACCDBExternalConnection = True
Exit Function
EH:
MsgBox err.NUMBER & ": " & err.DESCRIPTION, vbCritical, "Connection Error"
ChangeACCDBExternalConnection = False
Set appAccess = Nothing
End Function