How to Create an Access DB & Transfer Data from dBASE III DB (104013)



The information in this article applies to:

  • Microsoft Visual Basic Standard Edition for Windows 3.0

This article was previously published under Q104013

SUMMARY

This example demonstrates how to build a new Microsoft Access database and load it with data coming from a dBASE III database file.

MORE INFORMATION

To use this example, you will need a dBASE III database file. The dBASE III database file that was tested with this example can be sent upon request.

Step-by-Step 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 use GRID.VBX to add two grid controls to Form1. Then using following table as a guide, set the properties of the controls:
       Control Name   Property       New Value
       ------------------------------------------------------------------
       Command1       Caption        "Press to Load dBASE III DB 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           7
       Grid1          Rows           15
       Grid2          Cols           7
       Grid2          Rows           15
    						
  3. The following is an brief outline of the table from the dBASE III database:
       Table Name:      CHECKS
    
       Field Name   Field Type   Field Size
       -------------------------------------
       CHKNO        Double
       PAYTO        Text         30
       AMT          Double
       DATE         Date/Time
       MEMO         Text         25
       NAME5        Double
    
       Index Name   Index Field   Unique   Primary
       -------------------------------------------
       nm5          +NAME5        Yes      No
    						
  4. Add the following variables and constants to the (general) section of Form1:
       Dim CK_nums(20) As Double
       Dim paytos(20) As String * 30
       Dim amts(20) As Double
       Dim dates(20) As Variant
       Dim memos(20) As String * 25
       Dim indexs(20) As Double
       Dim counter%
       Const DB_DATE = 8
       Const DB_DOUBLE = 7
       Const DB_TEXT = 10
       Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
    						
  5. Add the following lines to the Form load event procedure:
       Sub Form_Load ()
          Show
          grid1.ColWidth(1) = 1000      'For Chk nums
          grid1.ColWidth(2) = 2000      'For Paid to
          grid1.ColWidth(3) = 1500      'For Amt for
          grid1.ColWidth(4) = 2000      'For Date written
          grid1.ColWidth(5) = 3000      'For Memo
          grid1.ColWidth(6) = 1000      'For index
          grid1.Col = 1
          grid1.Row = 0
          grid1.Text = "Check No."
          grid1.Col = 2
          grid1.Row = 0
          grid1.Text = "Party Paid"
          grid1.Col = 3
          grid1.Row = 0
          grid1.Text = "Amount"
          grid1.Col = 4
          grid1.Row = 0
          grid1.Text = "Date Written"
          grid1.Col = 5
          grid1.Row = 0
          grid1.Text = "Memo about"
          grid1.Col = 6
          grid1.Row = 0
          grid1.Text = "Index"
          grid2.ColWidth(1) = 1000      'For Chk nums
          grid2.ColWidth(2) = 2000      'For Paid to
          grid2.ColWidth(3) = 1500      'For Amt for
          grid2.ColWidth(4) = 2000      'For Date written
          grid2.ColWidth(5) = 3000      'For Memo
          grid2.ColWidth(6) = 1000      'For index
          grid2.Col = 1
          grid2.Row = 0
          grid2.Text = "Check No."
          grid2.Col = 2
          grid2.Row = 0
          grid2.Text = "Party Paid"
          grid2.Col = 3
          grid2.Row = 0
          grid2.Text = "Amount"
          grid2.Col = 4
          grid2.Row = 0
          grid2.Text = "Date Written"
          grid2.Col = 5
          grid2.Row = 0
          grid2.Text = "Memo about"
          grid2.Col = 6
          grid2.Row = 0
          grid2.Text = "Index"
       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$ = "dBASE III;"
    
          ' Enter the following two lines as one, single line:
          Set db = OpenDatabase("c:\articles\db3\dbaseiii", False,
             False, conn$)
    
          Set dt = db.OpenTable("CHECKS")
          screen.MousePointer = 11
          counter% = 1
          Do Until (dt.EOF = True)
             grid1.Col = 1
             grid1.Row = counter%
             grid1.Text = dt(0)
             CK_nums(counter%) = Val(grid1.Text)
             grid1.Col = 2
             grid1.Row = counter%
             grid1.Text = dt(1)
             paytos(counter%) = grid1.Text
             grid1.Col = 3
             grid1.Row = counter%
             grid1.Text = dt(2)
             amts(counter%) = Val(grid1.Text)
             grid1.Col = 4
             grid1.Row = counter%
             If IsNull(dt(4)) Then 'In case there is no date entered
                grid1.Text = ""
             Else
                grid1.Text = dt(4)
             End If
             dates(counter%) = grid1.Text
             grid1.Col = 5
             grid1.Row = counter%
             grid1.Text = dt(5)
             memos(counter%) = grid1.Text
             grid1.Col = 6
             grid1.Row = counter%
             grid1.Text = dt(8)
             indexs(counter%) = Val(grid1.Text)
             counter% = counter% + 1
             dt.MoveNext
          Loop
          screen.MousePointer = 0
       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 chknum
          Dim field2 As New field       'For party paid to
          Dim field3 As New field       'For amount
          Dim field4 As New field       'For date written
          Dim field5 As New field       'For memo field
          Dim field6 As New field       'For in index
          screen.MousePointer = 11
          Set newdb = CreateDatabase("DBASE3.MDB", DB_LANG_GENERAL)
          newtd.Name = "Checks_Table"   'New table name
          field1.Name = "Check_nums"
          field1.Type = DB_DOUBLE
          newtd.Fields.Append field1
          field2.Name = "Paid_to"
          field2.Type = DB_TEXT
          field2.Size = 30
          newtd.Fields.Append field2
          field3.Name = "Check_amt"
          field3.Type = DB_DOUBLE
          newtd.Fields.Append field3
          field4.Name = "Date_wrt"
          field4.Type = DB_DATE
          newtd.Fields.Append field4
          field5.Name = "Check_memo"
          field5.Type = DB_TEXT
          field5.Size = 25
          newtd.Fields.Append field5
          field6.Name = "Check_indx"
          field6.Type = DB_DOUBLE
          newtd.Fields.Append field6
          newidx.Name = "Check_nums_IDX"
          newidx.Fields = "Check_indx"
          newidx.Primary = True
          newtd.Indexes.Append newidx
          newdb.TableDefs.Append newtd
          Set newtb = newdb.OpenTable("Checks_Table")
          For j% = 1 To counter% - 1
             newtb.AddNew
             newtb("Check_nums") = CK_nums(j%)  'from dBASE III file
             newtb("Paid_to") = paytos(j%)      'from dBASE III file
             newtb("Check_amt") = amts(j%)      'from dBASE III file
             newtb("Date_wrt") = dates(j%)      'from dBASE III file
             newtb("Check_memo") = memos(j%)    'from dBASE III file
             newtb("Check_indx") = indexs(j%)   'from dBASE III file
             newtb.Update                       'Saving to table
          Next j%
          newtb.Close
          newdb.Close
          screen.MousePointer = 0
       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 cntr%
          Set db = OpenDatabase("DBASE3.MDB")
          Set t = db.OpenTable("Checks_Table")
          cntr% = 1           'Start counter at Row=1
          Do Until t.EOF
             grid2.Col = 1
             grid2.Row = cntr%
             grid2.Text = t(0)
             grid2.Col = 2
             grid2.Row = cntr%
             grid2.Text = t(1)
             grid2.Col = 3
             grid2.Row = cntr%
             grid2.Text = t(2)
             grid2.Col = 4
             grid2.Row = cntr%
             If IsNull(t(3)) Then 'In case there is no date entered
                grid2.Text = ""
             Else
                grid2.Text = t(3)
             End If
             grid2.Col = 5
             grid2.Row = cntr%
             grid2.Text = t(4)
             grid2.Col = 6
             grid2.Row = cntr%
             grid2.Text = t(5)
             cntr% = cntr% + 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. Click the Command1 button first. Then click the Command2 button. Then click the Command3 button, and compare the results.

Modification Type:MinorLast Reviewed:1/8/2003
Keywords:KB104013