How to Make Access DB & Transfer Data from Btrieve for MS-DOS

- 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 MS-DOS 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 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 ()
         Show
         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
            dt.MoveNext
            i% = i% + 1
         Loop
      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.AddNew
            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
            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, and compare the results.