How to Convert a Text File into a New Access Database

ID: Q103807

The information in this article applies to:

Microsoft Visual Basic programming system for Windows, version 3.0

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          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: June 17, 1999