Writing the VB6/VBA code to open a database and create a recordset on a table or query/view is very commonplace, yet complex and tedious. The Recordset Builder helps you create ADO and DAO code to access your data. Data can be from a Microsoft Access database or any DSN data source like SQL Server.
Once you select the data source, you can pick the table or query/view. You can also select a subset of fields. By setting a few options, you quickly have code to browse, add, or edit your records.
Recordset Builder Generating ADO Code (DAO Example)
Press [OK] to insert the code directly into your project at your cursor, or send it to the clipboard, file, or notepad.
Recordset Builder Generating DAO Example
With a few mouse clicks, you can quickly create ADO code that opens a database connection, selects a table, and lists all its fields for you to add new records:
Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Set cnn = New ADODB.Connection Set rst = New ADODB.Recordset cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Samples\Northwind.mdb" rst.Open "Categories", cnn, adOpenDynamic, adLockOptimistic, adCmdTable With rst .AddNew ![CategoryID] = 0 ![CategoryName] = "" ![Description] = "" ![Picture] = 0 .Update .Close End With Set rst = Nothing cnn.Close Set cnn = Nothing
All the complexity of creating connection and recordset objects are provided. Simply assign the field values between the .AddNew and .Update and you're done. All the complexity with opening the connections and table are handled.
Similarly, with a few mouse clicks, you can quickly create DAO code that opens a database, selects a table, and lists all its fields for you to update. In this example, we use the Current Database option in Microsoft Access:
Dim dbs As DAO.Database Dim rst As DAO.Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Categories", dbOpenDynaset, dbSeeChanges) With rst .Edit ![CategoryID] = 0 ![CategoryName] = 0 ![Description] = 0 ![Picture] = 0 .Update .Close End With Set rst = Nothing dbs.Close Set dbs = Nothing
All the complexity with opening the database and recordset are handled. Simply assign the field values between the .Edit and .Update and you're done.
A variety of options are available to let you customize the Recordset Builder to generate the code you want.
The first part is getting the database, table, and fields.
Specify whether you want to open an Access database or DSN data source such as SQL Server, then open it:
Once you've selected the database, a list of tables and queries/views appear. Select the object you want:
The fields within that object appear. They are all selected by default, but if you only want a subset, select them:
With the data defined, you specify the options for the code you want to build
Specify whether you want to create ADO or DAO code and see the code immediately change in the Generated Code text box. The complexity of opening the database connections, creating the SQL string, and opening the recordset are written for you!
By default, an explicit reference is made to the database. For Access developers, the database connection can be replaced by the built-in Access objects to reference the current database/project or code database/project.
For the variables created, you can have them defined (the Dim statements are added). You can also specify the name of the SQL string variable that's created if you've selected a subset of fields.
Decide whether you want to add, edit, or browse the records. For Add and Edit, the fields are listed to make it easy to assign their values. For Browse, a Debug.Print statement is used to display the field values to the Immediate Window for all the records in the table or query/view.
The final set of options let you specify whether you want to use Field Collections (rather than ![FieldName] it uses .Fields("FieldName")), and define parameter properties if your query/view has parameters.
Supports Office/Access 2016, 2013, 2010, 2007, 2003, 2002, 2000, and Visual Basic 6.0!
Also available for
"Total Visual CodeTools is by far my favorite third-party product."
Alison Balter, Author, Conference Speaker, Instructor
Best Visual Basic Add-In