Recordset Builder for ADO and DAO

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.

ADO Example

Recordset Builder Creating ADO Code in Total Visual CodeTools for VB6/VBA
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.

DAO Example

Recordset Builder Creating DAO Code in Total Visual CodeTools for VB6/VBA
Recordset Builder Generating DAO Example

Recordset Builder Results

ADO Example for Adding New Records

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.

DAO Example for Editing Records

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.

Recordset Builder Options

A variety of options are available to let you customize the Recordset Builder to generate the code you want.

Defining the Data

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:

Specify the database for the Recordset Builder

Once you've selected the database, a list of tables and queries/views appear. Select the object you want:

Select a table or query/view

The fields within that object appear. They are all selected by default, but if you only want a subset, select them:

Select all or a subset of fields from your datasource

Setting Recordset Options

With the data defined, you specify the options for the code you want to build

Database Library

Create ADO or DAO Recordset Code in Total Visual CodeTools for VB6/VBA

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!

Access Database Connection

Explicitly reference the database or use built-in Microsoft Access database objects

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.

Variable Options

Define variables

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.

Recordset Options

Browse, Add or Edit Records in your ADO/DAO Recordset in Total Visual CodeTools for VB6/VBA

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.

Additional Options

Additional recordset options

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.

Total Visual CodeTools User manual

Microsoft Office Access 2016, 2013, 2010 Version
is Shipping

New Features

Supports Office/Access 2016, 2013, 2010, 2007, 2003, 2002, 2000, and Visual Basic 6.0!

Also available for
Access 97


View all FMS products for Microsoft Access All Our Microsoft Access Products

Rave Reviews

"Total Visual CodeTools is by far my favorite third-party product."

Alison Balter, Author, Conference Speaker, Instructor


Best Visual Basic Add-In
Rave Reviews

CodeTools Info

Why CodeTools?

Additional Info

 

 

Free Product Catalog from FMS