Microsoft KB Archive/103441: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 67: | Line 67: | ||
<pre class="fixed_text"> Control Property New Value Comment | <pre class="fixed_text"> Control Property New Value Comment | ||
---------------------------------------------------------------------- | ---------------------------------------------------------------------- | ||
Command1 Caption | Command1 Caption "Press to Load Btrieve File and Display in Grid" | ||
Command2 Caption | Command2 Caption "Press to Transfer Data and Build New DB" | ||
Command3 Caption | Command3 Caption "Press to Display the Data of the New Database" | ||
Grid1 Cols 6 | Grid1 Cols 6 | ||
Grid1 Rows 35 | Grid1 Rows 35 | ||
Line 100: | Line 100: | ||
Const DB_LONG = 4 | Const DB_LONG = 4 | ||
Const DB_TEXT = 10 | Const DB_TEXT = 10 | ||
Const DB_LANG_GENERAL = | Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0" | ||
</pre></li> | </pre></li> | ||
Line 113: | Line 113: | ||
grid1.Col = 1 | grid1.Col = 1 | ||
grid1.Row = 0 | grid1.Row = 0 | ||
grid1.Text = | grid1.Text = "Cust ID" 'Header for Cust ID | ||
grid1.Col = 2 | grid1.Col = 2 | ||
grid1.Row = 0 | grid1.Row = 0 | ||
grid1.Text = | grid1.Text = "First Name" 'Header for First Name | ||
grid1.Col = 3 | grid1.Col = 3 | ||
grid1.Row = 0 | grid1.Row = 0 | ||
grid1.Text = | grid1.Text = "Last Name" 'Header for Last Name | ||
grid1.Col = 4 | grid1.Col = 4 | ||
grid1.Row = 0 | grid1.Row = 0 | ||
grid1.Text = | grid1.Text = "Cust Addr" 'Header for Cust Addr | ||
grid1.Col = 5 | grid1.Col = 5 | ||
grid1.Row = 0 | grid1.Row = 0 | ||
grid1.Text = | grid1.Text = "Cust Phone" 'Header for Cust Phone | ||
grid2.ColWidth(1) = 1000 'For Cust ID | grid2.ColWidth(1) = 1000 'For Cust ID | ||
Line 134: | Line 134: | ||
grid2.Col = 1 | grid2.Col = 1 | ||
grid2.Row = 0 | grid2.Row = 0 | ||
grid2.Text = | grid2.Text = "Customer ID" 'Header for Cust ID | ||
grid2.Col = 2 | grid2.Col = 2 | ||
grid2.Row = 0 | grid2.Row = 0 | ||
grid2.Text = | grid2.Text = "Cust First Name" 'Header for First Name | ||
grid2.Col = 3 | grid2.Col = 3 | ||
grid2.Row = 0 | grid2.Row = 0 | ||
grid2.Text = | grid2.Text = "Cust Last Name" 'Header for Last Name | ||
grid2.Col = 4 | grid2.Col = 4 | ||
grid2.Row = 0 | grid2.Row = 0 | ||
grid2.Text = | grid2.Text = "Customer Addr" 'Header for Cust Addr | ||
grid2.Col = 5 | grid2.Col = 5 | ||
grid2.Row = 0 | grid2.Row = 0 | ||
grid2.Text = | grid2.Text = "Customer Phone" 'Header for Cust Phone | ||
End Sub | End Sub | ||
Line 155: | Line 155: | ||
Dim conn$ | Dim conn$ | ||
Dim dt As table | Dim dt As table | ||
conn$ = | conn$ = "Btrieve;" | ||
' Enter the following Set as one, single line: | ' Enter the following Set as one, single line: | ||
Set db = OpenDatabase( | Set db = OpenDatabase("C:\articles\btrvdos\file.ddf", False, | ||
False, conn$) | False, conn$) | ||
Set dt = db.OpenTable( | Set dt = db.OpenTable("Customers") | ||
i% = 1 '* counter for loading the grid | i% = 1 '* counter for loading the grid | ||
Do Until (dt.EOF = True) | Do Until (dt.EOF = True) | ||
Line 199: | Line 199: | ||
Dim field4 As New field 'For Emp ss_nums | Dim field4 As New field 'For Emp ss_nums | ||
screen.MousePointer = 11 'To display the time to build | screen.MousePointer = 11 'To display the time to build | ||
Set newdb = CreateDatabase( | Set newdb = CreateDatabase("NEWBTRDB.MDB", DB_LANG_GENERAL) | ||
newtd.Name = | newtd.Name = "Cust_Table" '* New table name | ||
field1.Name = | field1.Name = "Cust_ID" '* Holds Cust ID nums() | ||
field1.Type = DB_LONG | field1.Type = DB_LONG | ||
newtd.Fields.Append field1 | newtd.Fields.Append field1 | ||
field2.Name = | field2.Name = "First_Name" '* Holds First names() | ||
field2.Type = DB_TEXT | field2.Type = DB_TEXT | ||
field2.Size = 15 | field2.Size = 15 | ||
newtd.Fields.Append field2 | newtd.Fields.Append field2 | ||
field3.Name = | field3.Name = "Last_Name" '* Holds Last names() | ||
field3.Type = DB_TEXT | field3.Type = DB_TEXT | ||
field3.Size = 15 | field3.Size = 15 | ||
newtd.Fields.Append field3 | newtd.Fields.Append field3 | ||
field4.Name = | field4.Name = "Cust_Addr" '* Holds cust Addr() | ||
field4.Type = DB_TEXT | field4.Type = DB_TEXT | ||
field4.Size = 30 | field4.Size = 30 | ||
newtd.Fields.Append field4 | newtd.Fields.Append field4 | ||
field5.Name = | field5.Name = "Cust_Phone" '* Holds cust phones() | ||
field5.Type = DB_TEXT | field5.Type = DB_TEXT | ||
field5.Size = 20 | field5.Size = 20 | ||
newtd.Fields.Append field5 | newtd.Fields.Append field5 | ||
newidx.Name = | newidx.Name = "Cust_ID_IDX" '* You must have to have an index | ||
newidx.Fields = | newidx.Fields = "Cust_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( | Set newtb = newdb.OpenTable("Cust_Table") | ||
For i%=1 to 10 'There are only ten entries | For i%=1 to 10 'There are only ten entries | ||
newtb.AddNew | newtb.AddNew | ||
newtb( | newtb("Cust_ID") = cust_ids(i%) 'place in field1 | ||
newtb( | newtb("First_Name") = Trim$(first)names(i%)) 'place in field2 | ||
newtb( | newtb("Last_Name") = Trim$(last_names(i%)) 'place in field3 | ||
newtb( | newtb("Cust_Addr") = Trim$(Cust_addr(i%)) 'place in field4 | ||
newtb( | newtb("Cust_Phone") = Trim$(Cust_phones(i%)) 'place in field5 | ||
newtb.Update 'Saving to table | newtb.Update 'Saving to table | ||
Next i% | Next i% | ||
Line 246: | Line 246: | ||
Dim t As Table | Dim t As Table | ||
Dim counter% | Dim counter% | ||
Set db = OpenDatabase( | Set db = OpenDatabase("NEWBTRDB.MDB") | ||
Set t = db.OpenTable( | Set t = db.OpenTable("Cust_Table") | ||
counter% = 1 'Start counter at Row=1 | counter% = 1 'Start counter at Row=1 | ||
Do Until t.EOF | Do Until t.EOF |
Revision as of 09:27, 20 July 2020
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