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

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

- Microsoft Visual Basic programming system for Windows, version 3.0

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.


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.