Adding Data to a Database Field in VB6


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Adding Data to a Database Field in VB6

  1. #1
    Brian Higgins Guest

    Adding Data to a Database Field in VB6


    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.



  2. #2
    Larry Goldstein Guest

    Re: Adding Data to a Database Field in VB6

    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.
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center