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

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.