Microsoft KB Archive/103441

From BetaArchive Wiki
Knowledge Base

Article ID: 103441

Article Last Modified on 1/8/2003


  • Microsoft Visual Basic 3.0 Professional Edition

This article was previously published under Q103441


The example in this article demonstrates how to build a Microsoft Access database without having a database or database template already built. The example uses a Btrieve for MS-DOS database file to supply the data to be placed into the newly created Microsoft Access database.


NOTE: You will need to have a Btrieve for MS-DOS database file

already built to test this example.

Steps to Demonstrate Example

  1. Start Visual Basic or from the File menu, choose New Project (ALT, F, N) if Visual Basic is already running. Form1 is created by default.
  2. Add three command buttons and two grid controls using GRID.VBX to Form1. Using the following table as a guide, set the properties of the controls you added in step 2.

       Control    Property   New Value    Comment
       Command1   Caption    "Press to Load Btrieve File and Display in Grid"
       Command2   Caption    "Press to Transfer Data and Build New DB"
       Command3   Caption    "Press to Display the Data of the New Database"
       Grid1      Cols       6
       Grid1      Rows       35
       Grid2      Cols       6
       Grid2      Rows       35
  3. The following is an brief outline of the table from the Btrieve for MS-DOS database:

       Table Name:      Customers
       Field Names      Field Type   Field Size
       Cust_ID          Long
       First_Name       Text         15
       Last_Name        Text         15
       Cust_Addr        Text         30
       Cust_Phone       Text         20
       Index Names      Index Fields      Unique   Primary
       Cust_ID_IDX      +Cust_ID          Yes      No
  4. Add the following variables and constants to the (general) section of Form1:

       Dim cust_ids(30) As Integer
       Dim first_names(30) As String * 15
       Dim last_names(30) As String * 15
       Dim cust_addr(30) As String * 30
       Dim cust_phones(30) As String * 20
       Const DB_LONG = 4
       Const DB_TEXT = 10
       Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
  5. Add the following code to the Form1 Load event procedure:

       Sub Form_Load ()
          grid1.ColWidth(1) = 1000       'For Cust ID
          grid1.ColWidth(2) = 2000       'For First Name
          grid1.ColWidth(3) = 2000       'For Last Name
          grid1.ColWidth(4) = 3000       'For Cust Addr
          grid1.ColWidth(5) = 2000       'For Cust Phone
          grid1.Col = 1
          grid1.Row = 0
          grid1.Text = "Cust ID"         'Header for Cust ID
          grid1.Col = 2
          grid1.Row = 0
          grid1.Text = "First Name"      'Header for First Name
          grid1.Col = 3
          grid1.Row = 0
          grid1.Text = "Last Name"       'Header for Last Name
          grid1.Col = 4
          grid1.Row = 0
          grid1.Text = "Cust Addr"       'Header for Cust Addr
          grid1.Col = 5
          grid1.Row = 0
          grid1.Text = "Cust Phone"      'Header for Cust Phone
          grid2.ColWidth(1) = 1000       'For Cust ID
          grid2.ColWidth(2) = 2000       'For First Name
          grid2.ColWidth(3) = 2000       'For Last Name
          grid2.ColWidth(4) = 3000       'For Cust Addr
          grid2.ColWidth(5) = 2000       'For Cust Phone
          grid2.Col = 1
          grid2.Row = 0
          grid2.Text = "Customer ID"     'Header for Cust ID
          grid2.Col = 2
          grid2.Row = 0
          grid2.Text = "Cust First Name" 'Header for First Name
          grid2.Col = 3
          grid2.Row = 0
          grid2.Text = "Cust Last Name"  'Header for Last Name
          grid2.Col = 4
          grid2.Row = 0
          grid2.Text = "Customer Addr"   'Header for Cust Addr
          grid2.Col = 5
          grid2.Row = 0
          grid2.Text = "Customer Phone"  'Header for Cust Phone
       End Sub
  6. Add the following code to the Command1 Click event procedure:

       Sub Command1_Click ()
          Dim db As database
          Dim conn$
          Dim dt As table
          conn$ = "Btrieve;"
          ' Enter the following Set as one, single line:
          Set db = OpenDatabase("C:\articles\btrvdos\file.ddf", False,
             False, conn$)
          Set dt = db.OpenTable("Customers")
          i% = 1            '* counter for loading the grid
          Do Until (dt.EOF = True)
             grid1.Col = 1
             grid1.Row = i%
             grid1.Text = dt(0)          'Load the grid
             cust_ids(i%) = dt(0)        'Load the temporary array
             grid1.Col = 2
             grid1.Row = i%
             grid1.Text = dt(1)          'Load the grid
             first_names(i%) = dt(1)     'Load the temporary array
             grid1.Col = 3
             grid1.Row = i%
             grid1.Text = dt(2)          'Load the grid
             last_names(i%) = dt(2)      'Load the temporary array
             grid1.Col = 4
             grid1.Row = i%
             grid1.Text = dt(3)          'Load the grid
             cust_addr(i%) = dt(3)       'Load the temporary array
             grid1.Col = 5
             grid1.Row = i%
             grid1.Text = dt(4)          'Load the grid
             cust_phones(i%) = dt(1)     'Load the temporary array
             i% = i% + 1
       End Sub
  7. Add the following code to the Command2 Click event procedure:

       Sub Command2_Click ()
          Dim newdb As Database
          Dim newtb As Table
          Dim newtd As New tabledef
          Dim newidx As New Index
          Dim field1 As New field        'For Emp nums
          Dim field2 As New field        'For Emp names
          Dim field3 As New field        'For Emp addresses
          Dim field4 As New field        'For Emp ss_nums
          screen.MousePointer = 11       'To display the time to build
          Set newdb = CreateDatabase("NEWBTRDB.MDB", DB_LANG_GENERAL)
          newtd.Name = "Cust_Table"      '* New table name
          field1.Name = "Cust_ID"        '* Holds Cust ID nums()
          field1.Type = DB_LONG
          newtd.Fields.Append field1
          field2.Name = "First_Name"     '* Holds First names()
          field2.Type = DB_TEXT
          field2.Size = 15
          newtd.Fields.Append field2
          field3.Name = "Last_Name"      '* Holds Last names()
          field3.Type = DB_TEXT
          field3.Size = 15
          newtd.Fields.Append field3
          field4.Name = "Cust_Addr"      '* Holds cust Addr()
          field4.Type = DB_TEXT
          field4.Size = 30
          newtd.Fields.Append field4
          field5.Name = "Cust_Phone"     '* Holds cust phones()
          field5.Type = DB_TEXT
          field5.Size = 20
          newtd.Fields.Append field5
          newidx.Name = "Cust_ID_IDX"    '* You must have to have an index
          newidx.Fields = "Cust_ID"
          newidx.Primary = True
          newtd.Indexes.Append newidx
          newdb.TableDefs.Append newtd
          Set newtb = newdb.OpenTable("Cust_Table")
          For i%=1 to 10                 'There are only ten entries
             newtb("Cust_ID") = cust_ids(i%)              'place in field1
             newtb("First_Name") = Trim$(first)names(i%)) 'place in field2
             newtb("Last_Name") = Trim$(last_names(i%))   'place in field3
             newtb("Cust_Addr") = Trim$(Cust_addr(i%))    'place in field4
             newtb("Cust_Phone") = Trim$(Cust_phones(i%)) 'place in field5
             newtb.Update                                 'Saving to table
          Next i%
          newtb.Close                    'Close DB's table
          newdb.Close                    'Close DB
          screen.MousePointer = 0        'Set back to show finished
       End Sub
  8. Add the following code to the Command3 Click event procedure:

       Sub Command3_Click ()
          Dim db As Database
          Dim t As Table
          Dim counter%
          Set db = OpenDatabase("NEWBTRDB.MDB")
          Set t = db.OpenTable("Cust_Table")
          counter% = 1                   'Start counter at Row=1
          Do Until t.EOF
             grid2.Col = 1
             grid2.Row = counter%
             grid2.Text = t(0)           'Load the Cust ID
             grid2.Col = 2
             grid2.Row = counter%
             grid2.Text = t(1)           'Load the First Name
             grid2.Col = 3
             grid2.Row = counter%
             grid2.Text = t(2)           'Load the Last Name
             grid2.Col = 4
             grid2.Row = counter%
             grid2.Text = t(3)           'Load the Cust Addr
             grid2.Col = 5
             grid2.Row = counter%
             grid2.Text = t(4)           'Load the Cust Phone
             counter% = counter% + 1
       End Sub
  9. From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program. First, click the Command1 button. Next, click the Command2 button. Then click the Command3 button, and compare the results.

Additional query words: 3.00

Keywords: KB103441