PSS ID Number: 103440
Article Last Modified on 1/8/2003
The information in this article applies to:
- Microsoft Visual Basic Standard Edition for Windows 3.0
This article was previously published under Q103440
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 Windows 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 Windows database file already built to test this example. The Btrieve for Windows database file tested with this example can be sent upon request.
Steps to demonstrate the 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 ---------------------------------------------------------------------- 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 Data from the New Database" Grid1 Cols 4 Grid1 Rows 15 Grid2 Cols 4 Grid2 Rows 15
Review the following brief outline of the table from the Btrieve for Windows database:
Table Name: Big_Tab Field Names Field Type Field Size ----------------------------------------- PrimaryKey Long Integer MyMoney Currency MyString Text 154 Index Names Index Fields Unique Primary --------------------------------------------------- tabindex +PrimaryKey Yes No
Add the following variables and constants to the (general) section of Form1:
Dim PrimaryKeys(30) As Long Dim Money(30) As Currency Dim Strings(30) As String * 154 Const DB_LONG = 4 Const DB_TEXT = 10 Const DB_CURRENCY = 5 Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
Add the following code to the Form Load event procedure:
Sub Form_Load () Show grid1.ColWidth(1) = 1000 'For PK ID grid1.ColWidth(2) = 2000 'For Money grid1.ColWidth(3) = 5000 'For Story grid1.Col = 1 grid1.Row = 0 grid1.Text = "Primary Keys" 'Header for PK ID grid1.Col = 2 grid1.Row = 0 grid1.Text = "Money" 'Header for Money grid1.Col = 3 grid1.Row = 0 grid1.Text = "Big String" 'Header for Story grid2.ColWidth(1) = 1000 'For PK ID grid2.ColWidth(2) = 2000 'For Money grid2.ColWidth(3) = 5000 'For Story grid2.Col = 1 grid2.Row = 0 grid2.Text = "Prime's" 'Header for PK ID grid2.Col = 2 grid2.Row = 0 grid2.Text = "Your Money" 'Header for Money grid2.Col = 3 grid2.Row = 0 grid2.Text = "Your Story" 'Header for Story 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\btrvwin\file.ddf", False, False, conn$) Set dt = db.OpenTable("Big_Tab") ' Counter for loading the grid For i% = 1 To 10 'Grab the first ten for a test grid1.Col = 1 grid1.Row = i% grid1.Text = dt(0) 'Load the grid PrimaryKeys(i%) = dt(0) 'Load the temporary array grid1.Col = 2 grid1.Row = i% grid1.Text = dt(1) 'Load the grid Money(i%) = dt(1) 'Load the temporary array grid1.Col = 3 grid1.Row = i% grid1.Text = dt(2) 'Load the grid Strings(i%) = dt(2) 'Load the temporary array dt.MoveNext Next i% 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 PK IDs Dim field2 As New field 'For Money Dim field3 As New field 'For Story's screen.MousePointer = 11 'To display the time to build Set newdb = CreateDatabase("NEWBTWDB.MDB", DB_LANG_GENERAL) newtd.Name = "Money_Table" '* New table name field1.Name = "PK_ID" '* Holds PK ID field1.Type = DB_LONG newtd.Fields.Append field1 field2.Name = "Money" '* Holds Money field2.Type = DB_CURRENCY newtd.Fields.Append field2 field3.Name = "Story" '* Holds Story field3.Type = DB_TEXT field3.Size = 154 newtd.Fields.Append field3 newidx.Name = "PK_ID_IDX" '* You have to have an index newidx.Fields = "PK_ID" newidx.Primary = True newtd.Indexes.Append newidx newdb.TableDefs.Append newtd Set newtb = newdb.OpenTable("Money_Table") For i% = 1 To 10 newtb.AddNew newtb("PK_ID") = PrimaryKeys(i%) 'place in field1 newtb("Money") = Money(i%) 'place in field3 newtb("Story") = Trim$(Strings(i%)) 'place in field4 newtb.Update 'Saving to table Next i% newtb.Close '* Close DB's table newdb.Close '* Close DB screen.MousePointer = 0 'Set back to show done 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("NEWBTWDB.MDB") Set t = db.OpenTable("Money_Table") counter% = 1 'Start counter at Row=1 Do Until t.EOF grid2.Col = 1 grid2.Row = counter% grid2.Text = t(0) 'Load the PK ID grid2.Col = 2 grid2.Row = counter% grid2.Text = t(1) 'Load the Money grid2.Col = 3 grid2.Row = counter% grid2.Text = t(2) 'Load the Story 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. Compare the results.
Additional query words: 3.00
Keywords: KB103440
Technology: kbAudDeveloper kbVB300 kbVB300Search kbVBSearch