Using Visual Basic Variables in an SQL Statement

In a Visual Basic program, you can create a SELECT statement in your
application by concatenating local variables into the statement as needed to
select, sort, or filter the data as dictated by your application. For example, if you
have a TextBox control (TitleWanted) containing the name of a Title and you
want to fetch all books from the Titles table with this title, you can create an
SQL statement that includes the current value of the TextBox. Note that the
SQL query encloses the TitleWanted value in single quotation marks (' '):
Set Rst = Db.OpenRecordset("SELECT * FROM Titles " _
& " WHERE Title = '" & TitleWanted.T




The procedure for creating a new Jet database is simply a process of creating
and defining data access objects that correspond to the tables, fields, indexes,
and relations of your database design. In this overview, the process will be
presented step-by-step, followed by a complete code example for creating the
Biblio.mdb database.
The first step in creating a new database is to create the Database object itself,
and to define its structure by adding TableDef and Field objects that correspond
to your design.

To create a new database
1 Use the Dim statement to create new object variables for each object in your
database. In addition to the DBEngine and default Workspace objects that
make up the working environment, you will need:
One Database object
One TableDef object for each table
One Field object for each field in each table
One Index object for each index in each table
For example, to create object variables for the Biblio.mdb database, you could
use the following code:
Dim MyDB As Database, MyWs As Workspace
Dim AuTd As TableDef, TitTd As TableDef, _
PubTd As tableDef
Dim AuFlds(2) As Field, TitFlds(5) _
As Field, PubFlds(10) As Field
Dim AuIdx As Index, TitIdx(3) As Index, _
PubIdx As Index

2 Use the CreateDatabase method of the Workspace object to create the new
database. In this example, the method uses the minimum two arguments: one
to specify the database name, and one to specify the locale:
Set MyWs = DBEngine.Workspaces(0)
Set MyDb = MyWs.CreateDatabase("C:\VB\Biblio.mdb", _
dbLangGeneral, dbVersion30)

Note that the constant dbVersion30 specifies a Jet version 3.0 database. If you
use the dbVersion30 constant to create a version 3.0 database, only 32-bit
applications using the Jet version 3.0 engine or higher will be able to access it.
3 Use the CreateTableDef method of the Database object to create new
TableDef objects for each table in the database, as follows:
Set TitTd = MyDB.CreateTableDef("Titles")
Set AuTd = MyDB.CreateTableDef("Authors")
Set PubTd = MyDB.CreateTableDef("Publishers")

4 Use the CreateField method of the TableDef object to create new Field objects
for each field in the table, and to set properties of each field to define the
field's size, data type, and other needed attributes. For example, the following
code creates the Authors table in the Biblio.mdb database:
Set AuFlds(0) = AuTd.CreateField("Au_ID", dbLong)
' Make it a counter field.
AuFlds(0).Attributes = dbAutoIncrField
Set AuFlds(1) = MyTd.CreateField("Author", dbText)
AuFlds(1).Size = 50

5 Use the Append method to add each field to its table and each table to the
database, as shown in the following code:
AuTd.Fields.Append AuFlds(0)
AuTd.Fields.Append AuFlds(1)
MyDB.TableDefs.Append AuTd