Microsoft KB Archive/103807

= How to Convert a Text File into a New Access Database =

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.



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

</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 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

</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("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

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

Additional query words: 3.00

Keywords: KB103807

-

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

© Microsoft Corporation. All rights reserved.