I recently worked on a project where it was unclear ultimately where the data was going to sit. I started working against an Access database but was aware thet at some point there would be an upgrade to SQLExpress or maybe a different, but equally cheap, RDMS. I was assured that the database schema would remain intact through the transition (though I was sceptical) so I concluded that I needed some way for my app to make a smooth change from one database to another.
I considered various strategies, but the one I finally settled on was to include the setting of the database connection string in my application's Options dialog.
Here's how I did it. If you want to try this in an Excel workbook, just add a new form, add a button (retain it's name), set a reference to the Microsoft OLE DB Service Component 1.0 Type Library noted in the commentary, run the form using F8 and click the button.
Option Explicit
Private Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Sub CommandButton1_Click()
Dim strRet As String
strRet = GetConnString
If Len(strRet) > 0 Then MsgBox strRet, vbInformation
End Sub
Private Function GetConnString() As String
'----------------------------------------------------------------------
' Procedure : GetConnString
' DateTime : 21-Jun-2007 21:14
' Author : Rob Bruce
' Purpose : Gets a connection string from the user by showing the
' : standard ADO Data Link Wizard. Empty string if the user
' : cancels.
' Depends : Requires a reference to the Microsoft OLE DB Service
' : Component 1.0 Type Library (oledb32.dll).
' : Calls GetMSFormHwnd.
'----------------------------------------------------------------------
'
Dim objDataLinks As New MSDASC.DataLinks
Dim lngHWNDParent As Long
' Make the Wizard modal (if possible)...
lngHWNDParent = GetMSFormHwnd(Me.Caption)
If lngHWNDParent > 0 Then objDataLinks.Hwnd = lngHWNDParent
' The Data Link Wizard raises an error if the user cancels...
On Error Resume Next
'Display the dialog
GetConnString = objDataLinks.PromptNew
End Function
' Utility functions...
Private Function GetMSFormHwnd(Caption As String)
'----------------------------------------------------------------------
' Procedure : GetMSFormHwnd
' DateTime : Back in the mists of time
' Author : Rob Bruce
' Purpose : Gets the window handle of an MSForms form. Sadly
' : these forms don't have a simple hWnd property.
' Depends : Requires FindWindow API declare.
' : Calls GetMSFormClassName.
'----------------------------------------------------------------------
Dim lngRet As Long
On Error GoTo GetMSFormHwnd_Error
lngRet = FindWindow(GetMSFormClassName, Caption)
GetMSFormHwnd = lngRet
Exit Function
GetMSFormHwnd_Error:
Err.Clear
GetMSFormHwnd = 0
End Function
Private Function GetMSFormClassName() As String
'----------------------------------------------------------------------
' Procedure : GetMSFormClassName
' DateTime : Back in the mists of time
' Author : Rob Bruce
' Purpose : The MSForms windows class name changed for version 2
' : (Office 2000), so this checks the version of Excel
' : in order to return the correct class name.
' Depends : Application object is assumed to be Excel.
'----------------------------------------------------------------------
On Error GoTo GetMSFormClassName_Error
If CLng(Val(Application.Version)) > 8 Then
' Excel 2000 onwards
GetMSFormClassName = "ThunderDFrame"
Else
' Excel 97 only
GetMSFormClassName = "ThunderXFrame"
End If
Exit Function
GetMSFormClassName_Error:
With Err
.Raise .Number, .Source & "[GetMSFormClassName]", .Description
End With
End Function
As you can see, there's a stupid amount of playing around with the form just to get the Data Link Wizard to become modal. What price an hWnd property for MSForms?
Good Luck!
Rob
============================================
Siaradwch yn araf, os gwelwch'n dda - dim ond dysgwyr ydw i!