How to Build Access DB & Load Data from Btrieve for Windows DB

MORE INFORMATION

Steps to demonstrate the 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
      ----------------------------------------------------------------------
      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 Data from the New Database"
      Grid1      Cols       4
      Grid1      Rows       15
      Grid2      Cols       4
      Grid2      Rows       15
   						

3. Review the following brief outline of the table from the Btrieve for Windows database:

      Table Name:     Big_Tab
   
      Field Names    Field Type      Field Size
      -----------------------------------------
      PrimaryKey     Long Integer
      MyMoney        Currency
      MyString       Text            154
   
      Index Names    Index Fields      Unique   Primary
      ---------------------------------------------------
      tabindex       +PrimaryKey       Yes      No
   						

4. Add the following variables and constants to the (general) section of Form1:

      Dim PrimaryKeys(30) As Long
      Dim Money(30) As Currency
      Dim Strings(30) As String * 154
      Const DB_LONG = 4
      Const DB_TEXT = 10
      Const DB_CURRENCY = 5
      Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
   						

5. Add the following code to the Form Load event procedure:

      Sub Form_Load ()
         Show
         grid1.ColWidth(1) = 1000      'For PK ID
         grid1.ColWidth(2) = 2000      'For Money
         grid1.ColWidth(3) = 5000      'For Story
         grid1.Col = 1
         grid1.Row = 0
         grid1.Text = "Primary Keys"   'Header for PK ID
         grid1.Col = 2
         grid1.Row = 0
         grid1.Text = "Money"          'Header for Money
         grid1.Col = 3
         grid1.Row = 0
         grid1.Text = "Big String"     'Header for Story
         grid2.ColWidth(1) = 1000      'For PK ID
         grid2.ColWidth(2) = 2000      'For Money
         grid2.ColWidth(3) = 5000      'For Story
         grid2.Col = 1
         grid2.Row = 0
         grid2.Text = "Prime's"        'Header for PK ID
         grid2.Col = 2
         grid2.Row = 0
         grid2.Text = "Your Money"     'Header for Money
         grid2.Col = 3
         grid2.Row = 0
         grid2.Text = "Your Story"     'Header for Story
      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\btrvwin\file.ddf", False, False,
            conn$)
         Set dt = db.OpenTable("Big_Tab")
         ' Counter for loading the grid
         For i% = 1 To 10       'Grab the first ten for a test
            grid1.Col = 1
            grid1.Row = i%
            grid1.Text = dt(0)       'Load the grid
            PrimaryKeys(i%) = dt(0)  'Load the temporary array
            grid1.Col = 2
            grid1.Row = i%
            grid1.Text = dt(1)       'Load the grid
            Money(i%) = dt(1)        'Load the temporary array
            grid1.Col = 3
            grid1.Row = i%
            grid1.Text = dt(2)       'Load the grid
            Strings(i%) = dt(2)      'Load the temporary array
            dt.MoveNext
         Next i%
      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 PK IDs
         Dim field2 As New field     'For Money
         Dim field3 As New field     'For Story's
         screen.MousePointer = 11    'To display the time to build
         Set newdb = CreateDatabase("NEWBTWDB.MDB", DB_LANG_GENERAL)
         newtd.Name = "Money_Table"  '* New table name
         field1.Name = "PK_ID"       '* Holds PK ID
         field1.Type = DB_LONG
         newtd.Fields.Append field1
         field2.Name = "Money"       '* Holds Money
         field2.Type = DB_CURRENCY
         newtd.Fields.Append field2
         field3.Name = "Story"       '* Holds Story
         field3.Type = DB_TEXT
         field3.Size = 154
         newtd.Fields.Append field3
         newidx.Name = "PK_ID_IDX"   '* You have to have an index
         newidx.Fields = "PK_ID"
         newidx.Primary = True
         newtd.Indexes.Append newidx
         newdb.TableDefs.Append newtd
         Set newtb = newdb.OpenTable("Money_Table")
         For i% = 1 To 10
            newtb.AddNew
            newtb("PK_ID") = PrimaryKeys(i%)      'place in field1
            newtb("Money") = Money(i%)            'place in field3
            newtb("Story") = Trim$(Strings(i%))   'place in field4
            newtb.Update                          'Saving to table
         Next i%
         newtb.Close                 '* Close DB's table
         newdb.Close                 '* Close DB
         screen.MousePointer = 0     'Set back to show done
      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("NEWBTWDB.MDB")
         Set t = db.OpenTable("Money_Table")
         counter% = 1           'Start counter at Row=1
         Do Until t.EOF
            grid2.Col = 1
            grid2.Row = counter%
            grid2.Text = t(0)         'Load the PK ID
            grid2.Col = 2
            grid2.Row = counter%
            grid2.Text = t(1)         'Load the Money
            grid2.Col = 3
            grid2.Row = counter%
            grid2.Text = t(2)         'Load the Story
            counter% = counter% + 1
            t.MoveNext
         Loop
         t.Close
         db.Close
      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. Compare the results.