
	Quick Find:		Search for:

Total Visual CodeTools
Microsoft Access 2007 Version Is Shipping! Supports Office/Access 2007, 2003, 2002, 2000, and Visual Basic 6.0! Also available for: Access 97 All Access Products
CodeTools Info
PDF Fact Sheet (409K) Product Guide Take a Tour Code Builders Sample Code Cleanup Sample Code Delivery Unused Variable Analysis Macro Recorder Coding Standards FAQs Best Visual Basic Add-In Rave Reviews
Why CodeTools?
for Developers for the Enterprise Convince Your Boss
More Info:
Check for Updates License Terms

"Total Visual CodeTools is by far my favorite third-party product." *- Alison Balter, Author, Conference Speaker, Instructor* More Reviews

Recordset Builder

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 (Larger Image) 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 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.ACE.OLEDB.12.0;" & vbCrLf & _
"Data Source=C:\Office2007\Office12\SAMPLES\Northwind.accdb"
rst.Open "Categories", cnn, adOpenDynamic, adLockOptimistic, adCmdTable

With rst
.AddNew
![CategoryID] = 0
![CategoryName] = 0
![Description] = 0
![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:

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

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.