Adding Data to a Database Field in VB6

[Brian Higgins]

I am trying to figure out how to add data to database fields using VB6 code.
I wish to import a text file, create a database, then populate the fields
within tables created. I created the following sample using a reference
to DAO 3.6 Object Library and have added the dialog control to the form.

Option Explicit

Dim CostTitle As String
Dim T As String
Dim Response As String

Private Sub cmdCreateDB_Click()
On Error GoTo DialogCancel
With CommonDialog1
.CancelError = True
.FileName = "*.mdb"
.DialogTitle = "Create a New Database"
.Filter = "Access Database File(*.mdb)|*.mdb"
.InitDir = "c:\access\"
.ShowOpen
T = Dir("C:\access\" & .FileTitle)
If Len(T) <> 0 Then
Response = MsgBox("File already exists, erase?",
vbQuestion + vbYesNo, "File Already Exists")
If Response = vbYes Then
Kill .FileName
End If
If Response = vbNo Then
MsgBox "Operation cancelled"
Exit Sub
End If
End If
CostTitle = .FileName
End With

'Create the database
Dim db As Database
Set db = DBEngine(0).CreateDatabase(CostTitle, dbLangGeneral,
dbVersion30)

'Define a variable as a tabledef object
Dim TblDef As TableDef

'Create the first table in the database named 'Employee'
Set TblDef = db.CreateTableDef("Employee")

'Create fields within the table
With TblDef
.Fields.Append .CreateField("FirstName", dbText, 15)
.Fields.Append .CreateField("LastName", dbText, 15)
.Fields.Append .CreateField("Title", dbText, 25)
.Fields.Append .CreateField("Salary", dbCurrency)
.Fields.Append .CreateField("EmplNum", dbSingle)
db.TableDefs.Append TblDef 'Append the table to the database
End With

'Create a second table in the database called 'Organization'
Set TblDef = db.CreateTableDef("Organization")

With TblDef
.Fields.Append .CreateField("EmplNum", dbSingle)
.Fields.Append .CreateField("Department", dbText, 25)
.Fields.Append .CreateField("Manager", dbText, 25)
db.TableDefs.Append TblDef 'Append the table to the database
End With

'Add data to the fields within the database
Dim FldData As Field






Exit Sub
'-------------------------------------------------------------------------
DialogCancel: MsgBox "Operation cancelled"

End Sub


The above code correctly creates a database file with 2 tables that contain
the appropriate fields. However, my problem is how to populate the database
in my VB6 program that I will be reading from a text file then adding the
appropriate data to the database.

For example, for the Employee table, I would like to add a number of employees
such as:

FirstName: John
LastName: Doe
Title: Engineer
Salary: 75000
EmplNum: 12345

and

FirstName: Jane
LastName: Smith
Title: Sales Rep
Salary: 50000
EmplNum: 12345

and to the Organization table, I would like to add

EmplNum: 12345
Department: Engineering
Manager: Frank Jones

and

EmplNum: 67890
Department: Sales
Manager: Terry Moore

I am not interested in creating primary keys or linkages between the tables,
simply creating a database file and populating the fields with data, that
can be opened using Access. All the reference material I have read explain
the method of creating database files, adding tables and fields, but they
don't deal with adding data to the records.

Any help would be appreciated.

[Larry Goldstein]

Open the database, then for each table, create a recordset, use the addnew
method to create a buffer for each new record, set field values, then use
the update method to save the record:

set db = DBEngine.Workspaces(0).OpenDatabase("C:\TEST\MYDB.MDB")

'The SELECT here will read all fields in all records. You can restrict what
you get by explictly listing included fields (e.g., rstRecordSet!ID,
rstRecordset!Customer...), using a WHERE clause to choose which record(s)
are included, etc. Read up on SQL for more information.
Set rstRecordset = db.openrecordset("SELECT * FROM
tblTestTable,dbOpenDynaset)

rstRecordset.addnew
rstRecordset.fields("Customer") = "My data"
rstRecordset.fields("Contact") = "My data 2"
rstRecordset.fields("PhoneNo") = lMyValue
rstRecordset.Update

when done...

rstRecordset.close
set rstRecordset =nothing
db.close
set db = nothing

FYI, there are a number of ways to refer to fields. I've excerpted the
writeup on this below from MSDN.

Good luck!

Larry Goldstein
Advanswers

Referring to Field Objects
You can identify a Field object by its DAO Name property, which corresponds
to the column name in the table from which the data in the field was
retrieved. The Fields collection is the default collection of a Recordset
object. Therefore, you can refer to the LastName field in the rstEmployees
Recordset in any of the following ways:

rstEmployees.Fields("LastName")
rstEmployees!LastName
rstEmployees![LastName]

When using the ! operator, you must include brackets around a field name
when it contains spaces. For example, the statement:

strEmp = rstEmployees!Last Name

will not compile, but the statement:

strEmp = rstEmployees![Last Name]

will compile with no problems.

Within the Fields collection, each Field object can also be identified by
its index:

rstEmployees.Fields(0)

The index enables you to walk through the collection in a loop, replacing
the index with a variable that is incremented with each pass through the
loop. Objects in a collection are numbered starting with zero, so the first
Field object in the Fields collection is number 0, the second is 1, and so
on. The field order is determined by the underlying table. Fields are
usually numbered in the order retrieved when the Recordset object is opened.
One drawback to this approach is that you can't be certain which field will
be referred to, because the underlying table structure may change, fields
may be added or deleted, and so on.



"Brian Higgins" <bkh_vb@hotmail.com> wrote in message
news:3c02e75b@147.208.176.211...
>
> I am trying to figure out how to add data to database fields using VB6
code.
> I wish to import a text file, create a database, then populate the fields
> within tables created. I created the following sample using a reference
> to DAO 3.6 Object Library and have added the dialog control to the form.
>
> Option Explicit
>
> Dim CostTitle As String
> Dim T As String
> Dim Response As String
>
> Private Sub cmdCreateDB_Click()
> On Error GoTo DialogCancel
> With CommonDialog1
> .CancelError = True
> .FileName = "*.mdb"
> .DialogTitle = "Create a New Database"
> .Filter = "Access Database File(*.mdb)|*.mdb"
> .InitDir = "c:\access\"
> .ShowOpen
> T = Dir("C:\access\" & .FileTitle)
> If Len(T) <> 0 Then
> Response = MsgBox("File already exists, erase?",
> vbQuestion + vbYesNo, "File Already Exists")
> If Response = vbYes Then
> Kill .FileName
> End If
> If Response = vbNo Then
> MsgBox "Operation cancelled"
> Exit Sub
> End If
> End If
> CostTitle = .FileName
> End With
>
> 'Create the database
> Dim db As Database
> Set db = DBEngine(0).CreateDatabase(CostTitle, dbLangGeneral,
> dbVersion30)
>
> 'Define a variable as a tabledef object
> Dim TblDef As TableDef
>
> 'Create the first table in the database named 'Employee'
> Set TblDef = db.CreateTableDef("Employee")
>
> 'Create fields within the table
> With TblDef
> .Fields.Append .CreateField("FirstName", dbText, 15)
> .Fields.Append .CreateField("LastName", dbText, 15)
> .Fields.Append .CreateField("Title", dbText, 25)
> .Fields.Append .CreateField("Salary", dbCurrency)
> .Fields.Append .CreateField("EmplNum", dbSingle)
> db.TableDefs.Append TblDef 'Append the table to the
database
> End With
>
> 'Create a second table in the database called 'Organization'
> Set TblDef = db.CreateTableDef("Organization")
>
> With TblDef
> .Fields.Append .CreateField("EmplNum", dbSingle)
> .Fields.Append .CreateField("Department", dbText, 25)
> .Fields.Append .CreateField("Manager", dbText, 25)
> db.TableDefs.Append TblDef 'Append the table to the
database
> End With
>
> 'Add data to the fields within the database
> Dim FldData As Field
>
>
>
>
>
>
> Exit Sub
> '-------------------------------------------------------------------------
> DialogCancel: MsgBox "Operation cancelled"
>
> End Sub
>
>
> The above code correctly creates a database file with 2 tables that
contain
> the appropriate fields. However, my problem is how to populate the
database
> in my VB6 program that I will be reading from a text file then adding the
> appropriate data to the database.
>
> For example, for the Employee table, I would like to add a number of
employees
> such as:
>
> FirstName: John
> LastName: Doe
> Title: Engineer
> Salary: 75000
> EmplNum: 12345
>
> and
>
> FirstName: Jane
> LastName: Smith
> Title: Sales Rep
> Salary: 50000
> EmplNum: 12345
>
> and to the Organization table, I would like to add
>
> EmplNum: 12345
> Department: Engineering
> Manager: Frank Jones
>
> and
>
> EmplNum: 67890
> Department: Sales
> Manager: Terry Moore
>
> I am not interested in creating primary keys or linkages between the
tables,
> simply creating a database file and populating the fields with data, that
> can be opened using Access. All the reference material I have read
explain
> the method of creating database files, adding tables and fields, but they
> don't deal with adding data to the records.
>
> Any help would be appreciated.
>
>