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

Last reviewed: June 21, 1995
Article ID: Q103440
The information in this article applies to:

- Microsoft Visual Basic programming system for Windows, version 3.0

SUMMARY

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 Windows database file to supply the data to be placed into the newly created Microsoft Access database.

MORE INFORMATION

NOTE: You will need to have a Btrieve for Windows database file

      already built to test this example. The Btrieve for Windows
      database file tested with this example can be sent upon request.

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.


Additional reference words: 3.00
KBCategory: kbinterop kbprg kbcode
KBSubcategory: APrgDataIISAM


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 21, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.