Microsoft KB Archive/103440: Difference between revisions

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - "&" to "&")
 
(One intermediate revision by the same user not shown)
Line 12: Line 12:
<div id="TitleRow">
<div id="TitleRow">


= <span id="KB103440"></span>How to Build Access DB &amp; Load Data from Btrieve for Windows DB =
= <span id="KB103440"></span>How to Build Access DB & Load Data from Btrieve for Windows DB =




Line 59: Line 59:
<pre class="fixed_text">  Control    Property  New Value
<pre class="fixed_text">  Control    Property  New Value
   ----------------------------------------------------------------------
   ----------------------------------------------------------------------
   Command1  Caption    &quot;Press to Load Btrieve File and Display in Grid&quot;
   Command1  Caption    "Press to Load Btrieve File and Display in Grid"
   Command2  Caption    &quot;Press to Transfer Data and Build New DB&quot;
   Command2  Caption    "Press to Transfer Data and Build New DB"
   Command3  Caption    &quot;Press to Display Data from the New Database&quot;
   Command3  Caption    "Press to Display Data from the New Database"
   Grid1      Cols      4
   Grid1      Cols      4
   Grid1      Rows      15
   Grid1      Rows      15
Line 87: Line 87:
   Const DB_TEXT = 10
   Const DB_TEXT = 10
   Const DB_CURRENCY = 5
   Const DB_CURRENCY = 5
   Const DB_LANG_GENERAL = &quot;;LANGID=0x0809;CP=1252;COUNTRY=0&quot;
   Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
                         </pre></li>
                         </pre></li>
<li><p>Add the following code to the Form Load event procedure:</p>
<li><p>Add the following code to the Form Load event procedure:</p>
Line 97: Line 97:
       grid1.Col = 1
       grid1.Col = 1
       grid1.Row = 0
       grid1.Row = 0
       grid1.Text = &quot;Primary Keys&quot;   'Header for PK ID
       grid1.Text = "Primary Keys"   'Header for PK ID
       grid1.Col = 2
       grid1.Col = 2
       grid1.Row = 0
       grid1.Row = 0
       grid1.Text = &quot;Money&quot;         'Header for Money
       grid1.Text = "Money"         'Header for Money
       grid1.Col = 3
       grid1.Col = 3
       grid1.Row = 0
       grid1.Row = 0
       grid1.Text = &quot;Big String&quot;     'Header for Story
       grid1.Text = "Big String"     'Header for Story
       grid2.ColWidth(1) = 1000      'For PK ID
       grid2.ColWidth(1) = 1000      'For PK ID
       grid2.ColWidth(2) = 2000      'For Money
       grid2.ColWidth(2) = 2000      'For Money
Line 109: Line 109:
       grid2.Col = 1
       grid2.Col = 1
       grid2.Row = 0
       grid2.Row = 0
       grid2.Text = &quot;Prime's&quot;       'Header for PK ID
       grid2.Text = "Prime's"       'Header for PK ID
       grid2.Col = 2
       grid2.Col = 2
       grid2.Row = 0
       grid2.Row = 0
       grid2.Text = &quot;Your Money&quot;     'Header for Money
       grid2.Text = "Your Money"     'Header for Money
       grid2.Col = 3
       grid2.Col = 3
       grid2.Row = 0
       grid2.Row = 0
       grid2.Text = &quot;Your Story&quot;     'Header for Story
       grid2.Text = "Your Story"     'Header for Story
   End Sub
   End Sub
                         </pre></li>
                         </pre></li>
Line 123: Line 123:
       Dim conn$
       Dim conn$
       Dim dt As Table
       Dim dt As Table
       conn$ = &quot;Btrieve;&quot;
       conn$ = "Btrieve;"
       ' Enter the following Set as one, single line:
       ' Enter the following Set as one, single line:
       Set db = OpenDatabase(&quot;C:\articles\btrvwin\file.ddf&quot;, False, False,
       Set db = OpenDatabase("C:\articles\btrvwin\file.ddf", False, False,
         conn$)
         conn$)
       Set dt = db.OpenTable(&quot;Big_Tab&quot;)
       Set dt = db.OpenTable("Big_Tab")
       ' Counter for loading the grid
       ' Counter for loading the grid
       For i% = 1 To 10      'Grab the first ten for a test
       For i% = 1 To 10      'Grab the first ten for a test
Line 156: Line 156:
       Dim field3 As New field    'For Story's
       Dim field3 As New field    'For Story's
       screen.MousePointer = 11    'To display the time to build
       screen.MousePointer = 11    'To display the time to build
       Set newdb = CreateDatabase(&quot;NEWBTWDB.MDB&quot;, DB_LANG_GENERAL)
       Set newdb = CreateDatabase("NEWBTWDB.MDB", DB_LANG_GENERAL)
       newtd.Name = &quot;Money_Table&quot; '* New table name
       newtd.Name = "Money_Table" '* New table name
       field1.Name = &quot;PK_ID&quot;       '* Holds PK ID
       field1.Name = "PK_ID"       '* Holds PK ID
       field1.Type = DB_LONG
       field1.Type = DB_LONG
       newtd.Fields.Append field1
       newtd.Fields.Append field1
       field2.Name = &quot;Money&quot;       '* Holds Money
       field2.Name = "Money"       '* Holds Money
       field2.Type = DB_CURRENCY
       field2.Type = DB_CURRENCY
       newtd.Fields.Append field2
       newtd.Fields.Append field2
       field3.Name = &quot;Story&quot;       '* Holds Story
       field3.Name = "Story"       '* Holds Story
       field3.Type = DB_TEXT
       field3.Type = DB_TEXT
       field3.Size = 154
       field3.Size = 154
       newtd.Fields.Append field3
       newtd.Fields.Append field3
       newidx.Name = &quot;PK_ID_IDX&quot;   '* You have to have an index
       newidx.Name = "PK_ID_IDX"   '* You have to have an index
       newidx.Fields = &quot;PK_ID&quot;
       newidx.Fields = "PK_ID"
       newidx.Primary = True
       newidx.Primary = True
       newtd.Indexes.Append newidx
       newtd.Indexes.Append newidx
       newdb.TableDefs.Append newtd
       newdb.TableDefs.Append newtd
       Set newtb = newdb.OpenTable(&quot;Money_Table&quot;)
       Set newtb = newdb.OpenTable("Money_Table")
       For i% = 1 To 10
       For i% = 1 To 10
         newtb.AddNew
         newtb.AddNew
         newtb(&quot;PK_ID&quot;) = PrimaryKeys(i%)      'place in field1
         newtb("PK_ID") = PrimaryKeys(i%)      'place in field1
         newtb(&quot;Money&quot;) = Money(i%)            'place in field3
         newtb("Money") = Money(i%)            'place in field3
         newtb(&quot;Story&quot;) = Trim$(Strings(i%))  'place in field4
         newtb("Story") = Trim$(Strings(i%))  'place in field4
         newtb.Update                          'Saving to table
         newtb.Update                          'Saving to table
       Next i%
       Next i%
Line 191: Line 191:
       Dim t As Table
       Dim t As Table
       Dim counter%
       Dim counter%
       Set db = OpenDatabase(&quot;NEWBTWDB.MDB&quot;)
       Set db = OpenDatabase("NEWBTWDB.MDB")
       Set t = db.OpenTable(&quot;Money_Table&quot;)
       Set t = db.OpenTable("Money_Table")
       counter% = 1          'Start counter at Row=1
       counter% = 1          'Start counter at Row=1
       Do Until t.EOF
       Do Until t.EOF

Latest revision as of 11:24, 21 July 2020

Knowledge Base


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

  1. 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.
  2. 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
                            
  3. 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
                            
  4. 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"
                            
  5. 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
                            
  6. 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
                            
  7. 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
                            
  8. 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
                            
  9. 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