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:
The fields within that object appear. They are all selected
by default, but if you only want a subset, select them:
Setting Recordset Options
With the data defined, you specify the options for the code
you want to build
Database Library
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
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
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
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
|
 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.
|
