Microsoft KB Archive/104013

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

Article ID: 104013

Article Last Modified on 1/8/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



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
 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.  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   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                          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"   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   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 </li>  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 </li>  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 </li> 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.</li></ol>

Additional query words: 3.00

Keywords: KB104013

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.