How to Convert a Text File into a New Access Database

- 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

1. 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.

2. Add three command buttons and two grid controls (GRID.VBX) to Form1.

3. 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
   
   

4. 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"

5. 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
   
   

6. 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
   
   

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

8. 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
   
   

9. 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.