Microsoft KB Archive/103807: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 59: | Line 59: | ||
<pre class="fixed_text"> Control Property New Value | <pre class="fixed_text"> Control Property New Value | ||
-------------------------------------------------------------------- | -------------------------------------------------------------------- | ||
Command1 Caption | Command1 Caption "Press to Build Text 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 5 | Grid1 Cols 5 | ||
Grid1 Rows 35 | Grid1 Rows 35 | ||
Line 75: | Line 75: | ||
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 87: | Line 87: | ||
grid1.Col = 1 | grid1.Col = 1 | ||
grid1.Row = 0 | grid1.Row = 0 | ||
grid1.Text = | grid1.Text = "Emp ID" 'Header for Emp ID from text file | ||
grid1.Col = 2 | grid1.Col = 2 | ||
grid1.Row = 0 | grid1.Row = 0 | ||
grid1.Text = | grid1.Text = "Emp Name" 'Header for Emp Name from text file | ||
grid1.Col = 3 | grid1.Col = 3 | ||
grid1.Row = 0 | grid1.Row = 0 | ||
grid1.Text = | grid1.Text = "Emp Addr" 'Header for Emp Addr from text file | ||
grid1.Col = 4 | grid1.Col = 4 | ||
grid1.Row = 0 | grid1.Row = 0 | ||
grid1.Text = | grid1.Text = "Emp SSN" 'Header for Emp SSN from text file | ||
grid2.ColWidth(1) = 1000 'For Emp ID | grid2.ColWidth(1) = 1000 'For Emp ID | ||
Line 104: | Line 104: | ||
grid2.Col = 1 | grid2.Col = 1 | ||
grid2.Row = 0 | grid2.Row = 0 | ||
grid2.Text = | grid2.Text = "Employee ID" 'Header for Emp ID from DB | ||
grid2.Col = 2 | grid2.Col = 2 | ||
grid2.Row = 0 | grid2.Row = 0 | ||
grid2.Text = | grid2.Text = "Employee Name" 'Header for Emp Name from DB | ||
grid2.Col = 3 | grid2.Col = 3 | ||
grid2.Row = 0 | grid2.Row = 0 | ||
grid2.Text = | grid2.Text = "Employee Addr" 'Header for Emp ID from DB | ||
grid2.Col = 4 | grid2.Col = 4 | ||
grid2.Row = 0 | grid2.Row = 0 | ||
grid2.Text = | grid2.Text = "Employee SSN" 'Header for Emp Name from DB | ||
End Sub | End Sub | ||
Line 121: | Line 121: | ||
For i% = 1 To 30 | For i% = 1 To 30 | ||
nums(i%) = i% | nums(i%) = i% | ||
names(i%) = | names(i%) = "John Doe # " + Str$(i%) | ||
addresses(i%) = Str$(i%) + | addresses(i%) = Str$(i%) + " Mocking Bird Lane" | ||
If i% < 9 Then | If i% < 9 Then | ||
'* Enter the following four lines as one, single line: | '* Enter the following four lines as one, single line: | ||
ss_nums(i%) = Trim$(Str$(i%) + Trim$(Str$(i%)) | ss_nums(i%) = Trim$(Str$(i%) + Trim$(Str$(i%)) | ||
+ Trim$(Str$(i%)) + | + Trim$(Str$(i%)) + "-" + Trim$(Str$(i% + 1)) | ||
+ Trim$(Str$(i% + 1)) + | + Trim$(Str$(i% + 1)) + "-" + Trim$(Str$(i%)) | ||
+ Trim$(Str$(i%)) + Trim$(Str$(i%)) + Trim$(Str$(i%))) | + Trim$(Str$(i%)) + Trim$(Str$(i%)) + Trim$(Str$(i%))) | ||
Else | Else | ||
'* Enter the following two lines as one, single line: | '* Enter the following two lines as one, single line: | ||
ss_nums(i%) = Trim$(Trim$(Str$(999)) + | ss_nums(i%) = Trim$(Trim$(Str$(999)) + "-" + Trim$(Str$(88)) | ||
+ | + "-" + Trim$(Str$(7777))) | ||
End If | End If | ||
Next i% | Next i% | ||
Open | Open "Testdata.DAT" For Output As #1 | ||
For j% = 1 To 30 | For j% = 1 To 30 | ||
Print #1, nums(j%) | Print #1, nums(j%) | ||
Line 171: | Line 171: | ||
Dim field4 As New field 'For Emp ss_nums | Dim field4 As New field 'For Emp ss_nums | ||
screen.MousePointer = 11 'Display the time to build | screen.MousePointer = 11 'Display the time to build | ||
Set newdb = CreateDatabase( | Set newdb = CreateDatabase("NEWDB.MDB", DB_LANG_GENERAL) | ||
newtd.Name = | newtd.Name = "Emp_Table" '* New table name | ||
field1.Name = | field1.Name = "Emp_ID" '* Holds Employee ID nums() | ||
field1.Type = DB_LONG | field1.Type = DB_LONG | ||
newtd.Fields.Append field1 | newtd.Fields.Append field1 | ||
field2.Name = | field2.Name = "Emp_Name" '* Holds Emp names() | ||
field2.Type = DB_TEXT | field2.Type = DB_TEXT | ||
field2.Size = 20 | field2.Size = 20 | ||
newtd.Fields.Append field2 | newtd.Fields.Append field2 | ||
field3.Name = | field3.Name = "Emp_Addr" '* Holds Employee addr() | ||
field3.Type = DB_TEXT | field3.Type = DB_TEXT | ||
field3.Size = 25 | field3.Size = 25 | ||
newtd.Fields.Append field3 | newtd.Fields.Append field3 | ||
field4.Name = | field4.Name = "Emp_SSN" '* Holds emp ss_nums() | ||
field4.Type = DB_TEXT | field4.Type = DB_TEXT | ||
field4.Size = 12 | field4.Size = 12 | ||
newtd.Fields.Append field4 | newtd.Fields.Append field4 | ||
newidx.Name = | newidx.Name = "Emp_ID_IDX" '* You have to have an index | ||
newidx.Fields = | newidx.Fields = "Emp_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("Emp_Table") | ||
Open | Open "Testdata.dat" For Input As #1 | ||
BeginTrans | BeginTrans | ||
Do While Not (EOF(1)) | Do While Not (EOF(1)) | ||
Line 202: | Line 202: | ||
Line Input #1, tmp3$ 'Retrieve empl_addr | Line Input #1, tmp3$ 'Retrieve empl_addr | ||
Line Input #1, tmp4$ | Line Input #1, tmp4$ | ||
newtb( | newtb("Emp_ID") = Trim$(tmp1$) 'Place in field1 | ||
newtb( | newtb("Emp_Name") = Trim$(tmp2$) 'Place in field2 | ||
newtb( | newtb("Emp_Addr") = Trim$(tmp3$) 'Place in field3 | ||
newtb( | newtb("Emp_SSN") = Trim$(tmp4$) 'Place in field4 | ||
newtb.Update 'Save to table | newtb.Update 'Save to table | ||
Loop | Loop | ||
Line 221: | Line 221: | ||
Dim t As Table | Dim t As Table | ||
Dim counter% | Dim counter% | ||
Set db = OpenDatabase( | Set db = OpenDatabase("NEWDB.MDB") | ||
Set t = db.OpenTable( | Set t = db.OpenTable("Emp_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:36, 20 July 2020
Article ID: 103807
Article Last Modified on 1/14/2005
APPLIES TO
- Microsoft Visual Basic 3.0 Professional Edition
This article was previously published under Q103807
SUMMARY
This article shows by example how to build a Microsoft Access database from scratch without having a database or database template already built. Then it shows how to load that database from data supplied by a standard ASCII text file.
MORE INFORMATION
Step-by-Step Example
- If you don't have one already, build an ASCII text file to use in this example. If you already have the text file built, you can ignore most of step 5 -- except for loading Grid1 with data from your text file.
- Add three command buttons and two grid controls (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 Build Text 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 5 Grid1 Rows 35 Grid2 Cols 5 Grid2 Rows 35
Add the following code to the (general) section of Form1:
Dim nums(30) As Long Dim names(30) As String * 20 Dim addresses(30) As String * 25 Dim ss_nums(30) As String * 12 Const DB_LONG = 4 Const DB_TEXT = 10 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 Emp ID grid1.ColWidth(2) = 2000 'For Emp Name grid1.ColWidth(3) = 3000 'For Emp Addr grid1.ColWidth(4) = 2000 'For Emp SSN grid1.Col = 1 grid1.Row = 0 grid1.Text = "Emp ID" 'Header for Emp ID from text file grid1.Col = 2 grid1.Row = 0 grid1.Text = "Emp Name" 'Header for Emp Name from text file grid1.Col = 3 grid1.Row = 0 grid1.Text = "Emp Addr" 'Header for Emp Addr from text file grid1.Col = 4 grid1.Row = 0 grid1.Text = "Emp SSN" 'Header for Emp SSN from text file grid2.ColWidth(1) = 1000 'For Emp ID grid2.ColWidth(2) = 2000 'For Emp Name grid2.ColWidth(3) = 3000 'For Emp Addr grid2.ColWidth(4) = 2000 'For Emp SSN grid2.Col = 1 grid2.Row = 0 grid2.Text = "Employee ID" 'Header for Emp ID from DB grid2.Col = 2 grid2.Row = 0 grid2.Text = "Employee Name" 'Header for Emp Name from DB grid2.Col = 3 grid2.Row = 0 grid2.Text = "Employee Addr" 'Header for Emp ID from DB grid2.Col = 4 grid2.Row = 0 grid2.Text = "Employee SSN" 'Header for Emp Name from DB End Sub
Add the following code to the Command1 click event procedure:
Sub Command1_Click () For i% = 1 To 30 nums(i%) = i% names(i%) = "John Doe # " + Str$(i%) addresses(i%) = Str$(i%) + " Mocking Bird Lane" If i% < 9 Then '* Enter the following four lines as one, single line: ss_nums(i%) = Trim$(Str$(i%) + Trim$(Str$(i%)) + Trim$(Str$(i%)) + "-" + Trim$(Str$(i% + 1)) + Trim$(Str$(i% + 1)) + "-" + Trim$(Str$(i%)) + Trim$(Str$(i%)) + Trim$(Str$(i%)) + Trim$(Str$(i%))) Else '* Enter the following two lines as one, single line: ss_nums(i%) = Trim$(Trim$(Str$(999)) + "-" + Trim$(Str$(88)) + "-" + Trim$(Str$(7777))) End If Next i% Open "Testdata.DAT" For Output As #1 For j% = 1 To 30 Print #1, nums(j%) Print #1, names(j%) Print #1, addresses(j%) Print #1, ss_nums(j%) Next j% Close #1 For i% = 1 To 30 'Display results from text file grid1.Col = 1 grid1.Row = i% grid1.Text = nums(i%) 'Load Emp IDs grid1.Col = 2 grid1.Row = i% grid1.Text = names(i%) 'Load Emp Names grid1.Col = 3 grid1.Row = i% grid1.Text = addresses(i%) 'Load Emp Addrs grid1.Col = 4 grid1.Row = i% grid1.Text = ss_nums(i%) 'Load Emp SSNs 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 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 'Display the time to build Set newdb = CreateDatabase("NEWDB.MDB", DB_LANG_GENERAL) newtd.Name = "Emp_Table" '* New table name field1.Name = "Emp_ID" '* Holds Employee ID nums() field1.Type = DB_LONG newtd.Fields.Append field1 field2.Name = "Emp_Name" '* Holds Emp names() field2.Type = DB_TEXT field2.Size = 20 newtd.Fields.Append field2 field3.Name = "Emp_Addr" '* Holds Employee addr() field3.Type = DB_TEXT field3.Size = 25 newtd.Fields.Append field3 field4.Name = "Emp_SSN" '* Holds emp ss_nums() field4.Type = DB_TEXT field4.Size = 12 newtd.Fields.Append field4 newidx.Name = "Emp_ID_IDX" '* You have to have an index newidx.Fields = "Emp_ID" newidx.Primary = True newtd.Indexes.Append newidx newdb.TableDefs.Append newtd Set newtb = newdb.OpenTable("Emp_Table") Open "Testdata.dat" For Input As #1 BeginTrans Do While Not (EOF(1)) newtb.AddNew Line Input #1, tmp1$ 'Retrieve empl_id Line Input #1, tmp2$ 'Retrieve empl_name Line Input #1, tmp3$ 'Retrieve empl_addr Line Input #1, tmp4$ newtb("Emp_ID") = Trim$(tmp1$) 'Place in field1 newtb("Emp_Name") = Trim$(tmp2$) 'Place in field2 newtb("Emp_Addr") = Trim$(tmp3$) 'Place in field3 newtb("Emp_SSN") = Trim$(tmp4$) 'Place in field4 newtb.Update 'Save to table Loop CommitTrans Close #1 'Close text file 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("NEWDB.MDB") Set t = db.OpenTable("Emp_Table") counter% = 1 'Start counter at Row=1 Do Until t.EOF grid2.Col = 1 grid2.Row = counter% grid2.Text = t(0) 'Load Emp ID grid2.Col = 2 grid2.Row = counter% grid2.Text = t(1) 'Load Emp Name grid2.Col = 3 grid2.Row = counter% grid2.Text = t(2) 'Load Emp Addr grid2.Col = 4 grid2.Row = counter% grid2.Text = t(3) 'Load Emp SSN 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 first. Then click the Command2 button, and then click the Command3 button to compare the results.
Additional query words: 3.00
Keywords: KB103807