Article ID: 103441
Article Last Modified on 1/8/2003
APPLIES TO
- Microsoft Visual Basic 3.0 Professional Edition
This article was previously published under Q103441
SUMMARY
The example in this article demonstrates how to build a Microsoft Access database without having a database or database template already built. The example uses a Btrieve for MS-DOS database file to supply the data to be placed into the newly created Microsoft Access database.
MORE INFORMATION
NOTE: You will need to have a Btrieve for MS-DOS database file
already built to test this example.
Steps to Demonstrate 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 two grid controls using GRID.VBX to Form1. Using the following table as a guide, set the properties of the controls you added in step 2.
Control Property New Value Comment ---------------------------------------------------------------------- Command1 Caption "Press to Load Btrieve 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 6 Grid1 Rows 35 Grid2 Cols 6 Grid2 Rows 35
The following is an brief outline of the table from the Btrieve for MS-DOS database:
Table Name: Customers Field Names Field Type Field Size -------------------------------------------------- Cust_ID Long First_Name Text 15 Last_Name Text 15 Cust_Addr Text 30 Cust_Phone Text 20 Index Names Index Fields Unique Primary ---------------------------------------------------- Cust_ID_IDX +Cust_ID Yes No
Add the following variables and constants to the (general) section of Form1:
Dim cust_ids(30) As Integer Dim first_names(30) As String * 15 Dim last_names(30) As String * 15 Dim cust_addr(30) As String * 30 Dim cust_phones(30) As String * 20 Const DB_LONG = 4 Const DB_TEXT = 10 Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
Add the following code to the Form1 Load event procedure:
Sub Form_Load () Show grid1.ColWidth(1) = 1000 'For Cust ID grid1.ColWidth(2) = 2000 'For First Name grid1.ColWidth(3) = 2000 'For Last Name grid1.ColWidth(4) = 3000 'For Cust Addr grid1.ColWidth(5) = 2000 'For Cust Phone grid1.Col = 1 grid1.Row = 0 grid1.Text = "Cust ID" 'Header for Cust ID grid1.Col = 2 grid1.Row = 0 grid1.Text = "First Name" 'Header for First Name grid1.Col = 3 grid1.Row = 0 grid1.Text = "Last Name" 'Header for Last Name grid1.Col = 4 grid1.Row = 0 grid1.Text = "Cust Addr" 'Header for Cust Addr grid1.Col = 5 grid1.Row = 0 grid1.Text = "Cust Phone" 'Header for Cust Phone grid2.ColWidth(1) = 1000 'For Cust ID grid2.ColWidth(2) = 2000 'For First Name grid2.ColWidth(3) = 2000 'For Last Name grid2.ColWidth(4) = 3000 'For Cust Addr grid2.ColWidth(5) = 2000 'For Cust Phone grid2.Col = 1 grid2.Row = 0 grid2.Text = "Customer ID" 'Header for Cust ID grid2.Col = 2 grid2.Row = 0 grid2.Text = "Cust First Name" 'Header for First Name grid2.Col = 3 grid2.Row = 0 grid2.Text = "Cust Last Name" 'Header for Last Name grid2.Col = 4 grid2.Row = 0 grid2.Text = "Customer Addr" 'Header for Cust Addr grid2.Col = 5 grid2.Row = 0 grid2.Text = "Customer Phone" 'Header for Cust Phone 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$ = "Btrieve;" ' Enter the following Set as one, single line: Set db = OpenDatabase("C:\articles\btrvdos\file.ddf", False, False, conn$) Set dt = db.OpenTable("Customers") i% = 1 '* counter for loading the grid Do Until (dt.EOF = True) grid1.Col = 1 grid1.Row = i% grid1.Text = dt(0) 'Load the grid cust_ids(i%) = dt(0) 'Load the temporary array grid1.Col = 2 grid1.Row = i% grid1.Text = dt(1) 'Load the grid first_names(i%) = dt(1) 'Load the temporary array grid1.Col = 3 grid1.Row = i% grid1.Text = dt(2) 'Load the grid last_names(i%) = dt(2) 'Load the temporary array grid1.Col = 4 grid1.Row = i% grid1.Text = dt(3) 'Load the grid cust_addr(i%) = dt(3) 'Load the temporary array grid1.Col = 5 grid1.Row = i% grid1.Text = dt(4) 'Load the grid cust_phones(i%) = dt(1) 'Load the temporary array dt.MoveNext i% = i% + 1 Loop 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 Emp nums Dim field2 As New field 'For Emp names Dim field3 As New field 'For Emp addresses Dim field4 As New field 'For Emp ss_nums screen.MousePointer = 11 'To display the time to build Set newdb = CreateDatabase("NEWBTRDB.MDB", DB_LANG_GENERAL) newtd.Name = "Cust_Table" '* New table name field1.Name = "Cust_ID" '* Holds Cust ID nums() field1.Type = DB_LONG newtd.Fields.Append field1 field2.Name = "First_Name" '* Holds First names() field2.Type = DB_TEXT field2.Size = 15 newtd.Fields.Append field2 field3.Name = "Last_Name" '* Holds Last names() field3.Type = DB_TEXT field3.Size = 15 newtd.Fields.Append field3 field4.Name = "Cust_Addr" '* Holds cust Addr() field4.Type = DB_TEXT field4.Size = 30 newtd.Fields.Append field4 field5.Name = "Cust_Phone" '* Holds cust phones() field5.Type = DB_TEXT field5.Size = 20 newtd.Fields.Append field5 newidx.Name = "Cust_ID_IDX" '* You must have to have an index newidx.Fields = "Cust_ID" newidx.Primary = True newtd.Indexes.Append newidx newdb.TableDefs.Append newtd Set newtb = newdb.OpenTable("Cust_Table") For i%=1 to 10 'There are only ten entries newtb.AddNew newtb("Cust_ID") = cust_ids(i%) 'place in field1 newtb("First_Name") = Trim$(first)names(i%)) 'place in field2 newtb("Last_Name") = Trim$(last_names(i%)) 'place in field3 newtb("Cust_Addr") = Trim$(Cust_addr(i%)) 'place in field4 newtb("Cust_Phone") = Trim$(Cust_phones(i%)) 'place in field5 newtb.Update 'Saving to table Next i% newtb.Close 'Close DB's table newdb.Close 'Close DB screen.MousePointer = 0 'Set back to show finished End Sub
Add the following code to the Command3 Click event procedure:
Sub Command3_Click () Dim db As Database Dim t As Table Dim counter% Set db = OpenDatabase("NEWBTRDB.MDB") Set t = db.OpenTable("Cust_Table") counter% = 1 'Start counter at Row=1 Do Until t.EOF grid2.Col = 1 grid2.Row = counter% grid2.Text = t(0) 'Load the Cust ID grid2.Col = 2 grid2.Row = counter% grid2.Text = t(1) 'Load the First Name grid2.Col = 3 grid2.Row = counter% grid2.Text = t(2) 'Load the Last Name grid2.Col = 4 grid2.Row = counter% grid2.Text = t(3) 'Load the Cust Addr grid2.Col = 5 grid2.Row = counter% grid2.Text = t(4) 'Load the Cust Phone counter% = counter% + 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. First, click the Command1 button. Next, click the Command2 button. Then click the Command3 button, and compare the results.
Additional query words: 3.00
Keywords: KB103441