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 35Add 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 SubAdd 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 SubAdd 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 SubAdd 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
