Microsoft KB Archive/103440

= How to Build Access DB & Load Data from Btrieve for Windows DB =

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

Additional query words: 3.00

Keywords: KB103440

Technology: kbAudDeveloper kbVB300 kbVB300Search kbVBSearch

-

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

© 2004 Microsoft Corporation. All rights reserved.