How to Create an Access DB & Transfer Data from dBASE III DB
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
- 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
- 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
- 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
- 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 query words:
3.00
Keywords :
Version :
Platform :
Issue type :
|