In Parts I and II, we set up our Userform and controls and built some special purpose procedures to handle the data and the CommandButtons. Now, we’ll create the Recordset that will fill our TextBoxes with useful information.
The Initialize event fires whenever a Userform opens. This is a good place to create the Recordset because we know it will be available whenever the form is showing.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
Private Sub UserForm_Initialize() Dim sConn As String Dim sSQL As String Dim sDbPath As String Dim sDbName As String 'store the path and name of the database sDbPath = "C:Program FilesMicrosoft OfficeOfficeSamples" sDbName = "Northwind" 'store the connection string sConn = "DSN=MS Access Database;" sConn = sConn & "DBQ=" & sDbPath & "" & sDbName & ".mdb;" sConn = sConn & "DefaultDir = " & sDbPath & ";" sConn = sConn & "DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" 'store the SQL statement sSQL = "SELECT Employees.EmployeeID, Employees.LastName, " sSQL = sSQL & "Employees.FirstName, Employees.BirthDate, Employees.HireDate " sSQL = sSQL & "FROM '" & sDbPath & "" & sDbName & "'.Employees Employees" 'Create a new connection and recordset Set mADOCon = New ADODB.Connection Set mADORs = New ADODB.Recordset mADORs.CursorLocation = adUseClient 'Open the connection and recordset mADOCon.Open sConn mADORs.Open sSQL, mADOCon, adOpenDynamic 'Go to the first record mADORs.MoveFirst 'Call special purpose subs FillTextBoxes DisableButtons "ButtonFirst", "ButtonPrev" End Sub |
Most of this procedure is setting up strings. Those pesky connection strings and sql statements are hard for me to make. The way that I do it is to create an external data table in Excel (Data>Get External Data) and then read the Connection and CommandText properties. Then I make any changes like removing the ODBC; from the connection string and replacing the database information with variables. Once I have the external data table set up, I go to the Immediate Window and type
1 2 |
Print Sheet1.QueryTables(1).Connection Print Sheet1.QueryTables(1).CommandText |
You’ll get a couple of really long strings, but you have only to copy and paste them into your code. If you’re anal about seeing all your code like me, then you’ll want to break up the strings like I did.
If you’re following along at home, there are two things that you must do: Change sDbPath to point to the folder with Northwind.mdb in it; and set a reference to the ADO Library (Tools>References from the VBE). My References box looks like this
When the Userform is closed, the QueryClose event fires. We don’t need the Recordset if the form’s not open, so we release it back to the wild (and from our computers memory) in this event.
1 2 3 4 5 6 7 8 9 |
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) mADORs.Close mADOCon.Close Set mADORs = Nothing Set mADOCon = Nothing End Sub |
The hard part is over. In Part IV, we’ll see how to code the buttons to move between records. You’ll be amazed at how simple it is.
I can? find Part IV…
And I?e proplems with the sSQL statement, seems the connection between SELECT and FROM statements.
I? especially concerned with this ‘ marker.
Any Ideas?
part IV is here
http://www.dailydoseofexcel.com/archives/2004/05/26/navigate-a-recordset-with-a-userform-part-iv/