Much has been written about managing recordsets for forms, controls and reports in Microsoft Access databases, but recently I was obliged to develop an application with a slightly new twist that required some creative recordset manipulation. The requirements were as follows:
1. Program must be an ADP (Access Data Project), not an MDB
2. The project persisted its connection to a 'config' SQL Server database
3. All data is stored in several SQL Server 'client' databases
4. Forms must connect to the correct client database at runtime
Are you following this? All databases are contained on a single SQL Server, but the application needs to pull data from any one of several databases at a moment's notice. In some cases, the user requests data from a specific database. Other times, they launch tasks that are database specific and the app must dynamically point to the correct data store.
My first thought was to change the connection property of the ADP application each time the user selected a different database, but this was neither simple nor elegant. Changing connections at runtime is like changing horses in mid-stream ... you are bound to get soaked. I don't care what anyone on the newsgroups say, it cannot be done well. Then when an ADP's connection property is closed, all forms are unceremoniously destroyed. It would take nothing short of an orchestra of smoke and mirrors to obfuscate this from the user ... if it can be done at all.
The final solution ended up being very elegant and reusable. In a nutshell, it includes the following pieces:
- ADP connection is attached to the config database
- Config database manages user logins
- Config contains a list of client databases
- Tasks for all clients are in Config database
- ADP includes two procedures to simplify switching
- Sub: LoadDbsConnectString(ClientID)
- Function: GetRecordset(SQL) As Recordset
- Form and Control data are loaded via the Recordset property
- Form.RecordSource property must be blank
- Form.RecordSet property must be set in VBA code
- Must use Set operator
- Tables, views or stored procs may be used
By way of example, the screen-shot below shows frmAddress in design mode. This form will require the setting of four recordsets: One for the form's data and three for the combo boxes. Notice that the recordset for one of those combo boxes, cboStateCode, depends upon the value selected in the country option, cboCountryID. Let's see how we can load this form with data!
Note: There is no code download this month. Because the bulk of the code has to do with connections, and since readers will have their own servers and databases, it would not be a simple matter to deploy.
Set ADP Connection
As mentioned above, the first step is to set the overall connection for the ADP. In our scenario, this is set to a config database that contains, among other things, information about the various client databases our application may point to.
The Connection property of the ADP can easily be set at design time and need not be modified by the user. Simply select Connection from the File menu and fill in the values requested by the dialog box.
This dialog includes a "Test Connection" button, but as was once pointed out to me, it's just a placebo. You can only select a database if you enter a valid server and if you have both the server and database name validated, there's really nothing left to test. But if it makes you feel better, click the Test Connection button before closing the dialog box.
Create the Procedures
According to our requirements above, the source for the client data is determined in a JIT (just in time) fashion. It may be when the user elects to search a particular database or it may be the result of double-clicking a task that is associated with a particular client database, but the bottom line is that you don't know which database to point to until just before the form is opened.
To accommodate this requirement, we created a global string variable, g_ClientCnn, into which we stuff the full connection string to the currently requested client database. So, at the instant a data request is made, we identify the appropriate ClientID and pass it to the sub procedure that sets the connection string. (see code below for specifics)
' Client connect string is saved in this public variable. Public g_ClientCnn As String Public Sub LoadDbsConnectString(ByVal lClientID As Long) On Error GoTo Err_Handler Dim rstTemp As New ADODB.Recordset Dim strSQL As String Dim strDatabase As String Dim strProjCnn As String ' You must hard-code the name of the config database. It will ' be used below to simplify the creation of the new connect string. Const strProjDBS As String = "RecreationConfigDB" ' The config database must include a table to manage client ' database names based on an identifier, such as ClientID. strSQL = "SELECT [DatabaseName] FROM tblClient " & _ "WHERE [ClientID]=" & lClientID rstTemp.Open strSQL, CurrentProject.Connection ' If the client record isn't found, throw an error. Otherwise, ' use the database name to update the connection string variable. If rstTemp.BOF And rstTemp.EOF Then MsgBox "Couldn't locate the database.", vbCritical, "ERROR" Else ' The Replace function simplifies the connection string edit. ' We know that the format of CurrentProject.Connection is ' correct. Simply replace the database name (assuming the ' user's login has permission to both databases.) strDatabase = rstTemp!DatabaseName strProjCnn = CurrentProject.Connection g_ClientCnn = Replace(strProjCnn, strProjDBS, strDatabase) End If Exit_Here: Set rstTemp = Nothing Exit Function Err_Handler: Resume Next End Sub
In addition to a sub procedure to set the connection string, it pays to have one or more functions to load data into recordsets. Below is one of my functions that returns an ADO recordset. I have another that executes a stored proc and another to execute INSERT, UPDATE and DELETE statements. However, for the task at hand, we need to return an ADO recordset, so the following function will do.
Public Function GetRecordset(ByVal sSQL As String) As ADODB.Recordset On Error GoTo Err_Handler Dim rstTemp As New ADODB.Recordset Dim cnnTemp As New ADODB.Connection ' First open a temporary connection, and then load the recordset. cnnTemp.Open g_ClientCnn rstTemp.Open sSQL, cnnTemp, adOpenDynamic, adLockOptimistic ' This doesn't include any error handling ' You will have to add that to meet your needs. Set GetRecordset = rstTemp Exit_Here: Set rstTemp = Nothing Set cnnTemp = Nothing Exit Function Err_Handler: Resume Exit_Here End Function
Putting It All Together
Now that we have all the pieces (a project connection, a form and our procedures), it only remains to bring them all together in the Form_Open() event. Be sure that your form has no SELECT statement in the RecordSource property, as it will compete with the code we are about to run. For all intents and purposes, it will appear that the form has no record source while in design mode ... because it doesn't. In fact, it can't point to any tables because the client tables we seek are not even in our config database, to which the ADP is currently connected. The datasets will be dynamically assigned to the form and its controls in the following code.
Private Sub Form_Open(Cancel As Integer) On Error GoTo Err_Handler Dim strSQL As String Dim lngEmployeeID As Long Dim lngCountryID As Long ' Call a 'Property Get' function to grab EmployeeID ' This will be used to filter the recordset. (You will need to adapt ' this part of the code to match your SQL requirements.) lngEmployeeID = GetEmployeeID() ' Identify SQL to be passed, a stored proc in this case, and ' remember to use the SET command when assigning the recordset. strSQL = "spc_uclAddress @EmployeeID=" & EmployeeID Set Me.Recordset = GetRecordset(strSQL) ' Load record sources for all combo boxes strSQL = "spc_ddlAddressType" Set Me!cboAddressTypeID.Recordset = GetRecordset(strSQL, client) strSQL = "spc_ddlCountry" Set Me!cboCountryID.Recordset = GetRecordset(strSQL, client) ' The list of states depends upon the country. This param ' is passed to the stored proc. (our default is 1 for USA) lngCountryID = Nz(Me.Recordset!CountryID, 1) strSQL = "spc_ddlState @CountryID=" & lngCountryID Set Me!cboStateProvince.Recordset = GetRecordset(strSQL, client) Exit_Here: Exit Sub Err_Handler: Resume Next End Sub
Works Like Magic
I've known for years that you could slam a recordset into a form or control, but never understood why I'd ever do it. Suddenly, this approach made sense to me. It's also especially useful if and when you want to use SQL Server stored procs in an Access Data Project. This process will undoubtedly work for MDBs, though I didn't actually test it. There are strong opinions out there for and against ADPs, but I have to say that with the methods described in this article, it's been exceedingly simple to use ADO recordsets for everything, but especially when loading data into forms and controls.