Ændre ODBC-connection via VBA (Access 2007)
Jeg har tidligere (Access2000/2003) brugt denne kode til at skifte mellem forskellige BackEnds (Test/Prod), både Front- og BackEnds har været mbd'er::::::::::
Private Sub cboConnectStr_AfterUpdate()
On Error GoTo Err_Handling
Const Attached = dbAttachedTable Or dbAttachedODBC
Dim aoForm As AccessObject
Dim frm As Form
Dim ctl As Control
Dim strFont As String
Dim DB As Database: Set DB = DBEngine(0)(0)
Dim TD As TableDef: For Each TD In DB.TableDefs
If (TD.Attributes And Attached) <> 0 Then
If TD.Connect = cboConnectStr Then GoTo Exit_Code
TD.Connect = cboConnectStr
TD.RefreshLink
End If
Next
DB.Properties!AppTitle = "Database - " & DLookup("Database", "tblBackEnds", "ConnectStr = '" & cboConnectStr & "'")
Application.RefreshTitleBar
strBackEnd = " - " & DLookup("Database", "tblBackEnds", "ConnectStr = '" & cboConnectStr & "'")
For Each aoForm In CurrentProject.AllForms
If aoForm.Name <> Me.Name Then
DoCmd.Close acForm, aoForm.Name, acSaveYes
Application.Echo True
End If
Next aoForm
Set DB = Nothing
Exit_Code:
DoCmd.OpenForm "frmMainMenu", acNormal
DoCmd.Close acForm, "frmSelectBackEnd"
Application.Echo True
SysCmd acSysCmdClearStatus
Exit Sub
Err_Handling:
Select Case Err.Number
Case 438
Resume Next
Case Else
Application.Echo True
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Code
End Select
End Sub
:::::::::
Jeg sidder nu med et projekt, hvor jeg ønsker at genbruge denne funktionalitet, men kan ikke få den til at virke.
Mit nuværende system har Oracle BackEnds, og Access 2007 FrontEnd.
Jeg har forbindelsen til 'Produktion', og jeg kan manuelt oprette forbindelse til en af tabellerne i 'Test', så der er altså forbindelse.
Men når jeg prøver at ændre ConnectStr via koden får jeg:
'Error 3011'
"The Microsoft Office Access database engine could not find the object 'ACCESS_LOG'. Make sure the object exists and that you spell its name and the path name correctly."