Knowledge Base |

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

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

Additional query words: 3.00

Keywords: KB103807