Solved External Database Update Connection String (2 Viewers)

A

Alibaba

New member
Local time
Today, 11:51
Joined
May 29, 2008
Messages
12
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
 
I don't see a question. Does the code not work? What happens? What error message is raised?
 
Alibaba said:
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
Click to expand...
What is the problem you need to solve?

You have this code. What is wrong with it?

Details are always a good way to help people understand what you are really asking. As is, it's just a piece of code that does work, but isn't somehow what you need, I guess?

Thank you.
 
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 is an installation program written in prior access versions. I am trying to update it to work with ms-access 365.
The prior versions can connect to SQL server in an ADP file (access project) or to an access file (oldest version)
The 365 ms-access version supposed to connect to SQL server using DSN-less connection string
The program supposed to open a master copy of ms-access and distribute that copy to the clients
each client has their own separate data on SQL server
The Installation program supposed to open the master copy and change the connection string to the client database
The code above opens the master copy of the program
It needs to update the linked tables using a DSN less connection string using the parameters in the function:

strAdpFile As String is the location of the file being opened it's ms-access accdb file
strServerName As String is the client database server
strDBName As String is the name of the client database on SQL server
Optional strUN As String is the client sql authentication user id
Optional strPW As String is the client user password

thank you!
 
Code: 
Function ChangeACCDBExternalConnection(strACCDBFileFullPath As String, strServerName As String, strDBName As String, Optional strUN As String, Optional strPW As String) As Boolean
    
    On Error GoTo Err_Change_ACCDB_External_Connection_String

    
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strNewConnectString As String
    Dim wrk As DAO.Workspace
    
    Dim errMsg As String

Dim strPassTru As String
Dim strStep As String
' OLE & Pass Thru & Step Thru Implementations 2024.03.13 13:37 PM Kaptain17

' Kaptain17 Tracer Added    & completed  2025.06.05 08:49 AM Kaptain17
strStep = "1. Declarations"

 DoCmd.SetWarnings (False)
    DoCmd.Hourglass (True)
    
    ' Set a reference to the current database
    'Set db = CurrentDb
    Set db = OpenDatabase(strACCDBFileFullPath)
    ' Define the new connection string (replace with your actual path)
    strStep = "2. Set Target Database"
    strNewConnectString = "DRIVER=SQL Server;SERVER=" & strServerName & ";UID=" & strUN & ";PWD=" & strPW & ";APP=Microsoft Office;DATABASE=" & strDBName  ' Example for Access database
    ' For ODBC linked tables, it might look like: "ODBC;DRIVER={SQL Server};SERVER=NewServerName;DATABASE=YourDBName;UID=YourUsername;PWD=YourPassword"
    strStep = "3. Set New  Connection String"
    
    ' Loop through all TableDefs
    For Each tdf In db.TableDefs
        ' Check if it's a linked table(tdf is empty)
        If tdf.Connect <> "" Then
            ' Update the connection string
            tdf.Connect = strNewConnectString
            ' Refresh the link
            tdf.RefreshLink
            Debug.Print "Updated linked table: " & tdf.NAME 'change this to log this in the event log
        End If
    Next tdf
    strStep = "4. loop through external linked tables"
    
    
    MsgBox "Linked table paths updated!", vbInformation, "Installation Program Alerts!" 'change this to log this in the event log
    strStep = "4. loop through external linked tables"
    
    ChangeACCDBExternalConnection = True
    strStep = "5. Operation is successful"
    
CloseConnections:

Exit_Change_ACCDB_External_Connection_String:

Set db = Nothing

 DoCmd.SetWarnings (True)
    DoCmd.Hourglass (False)
    
    Set appAccess = Nothing
    
    Exit Function

Err_Change_ACCDB_External_Connection_String:
' 2024.02.27 11:15:15 AM Kaptain17
 ' Custom Error Trapper Msg 2024.03.29 05:47 20 AM Kaptain17
         MsgBox "Sorry! There Is an Unexpected Issue " & Chr(10) & "Linking Tables; 365; ACCDB update Connection String " & Chr(10) & "After Step: " & strStep & Chr(10) & "Code#: " & err.NUMBER & Chr(10) & err.DESCRIPTION & Chr(10) & "Please contact admin." & Chr(10) & "Thank You!", vbExclamation, "Corp_Pro  Alerts!"
         ChangeACCDBExternalConnection = False

     Resume Exit_Change_ACCDB_External_Connection_String
    
  

    
    
End Function

Problem solved as shown. Thank you
 
Glad the problem is solved. But we had no idea what what the problem was. You never told us. You just posted code.
 
Pat Hartman said:
Glad the problem is solved. But we had no idea what what the problem was. You never told us. You just posted code.
Click to expand...
the description of the problem was at the beginning of the msg.
1. the routine needs to open an access dB (master) from another access dB (installer)
2. the routine needs to connect the master dB to the corresponding client SQL dB using connection string from input parameters
3. close and save with the corresponding client name (other external routines)
4. i built this long time ago for connecting via ADP project files and or even the earlier version to an MDB backend
5. I had the code that opens the remote dB already
6. I had the code that update the linked tables to any connection string locally
7. my problem was making the linked table routine work with the remote client front end

thank you for the help!
 
You must log in or register to reply here.

Similar threads

N
Solved Receiving Emails in Access Database using CDO 2000 library
Replies
10
Views
526
sonic8
S
P
form event not executing
Replies
9
Views
399
Pat Hartman
P
A
Solved VBA code for deleting Outlook 'Appointments' lying within a specified date range
Replies
8
Views
571
Gasman
Gasman
Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
677
Gasman
Gasman
P
^{F4} - what does this do
Replies
4
Views
613
Isaac
Isaac

Users who are viewing this thread

Total: 3 (members: 2, guests: 1)
Back
Top Bottom