How to Convert a Text File into a New Access Database

Last reviewed: June 21, 1995
Article 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

  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 reference words: 3.00
KBCategory: kbprg kbcode
KBSubcategory: APrgDataAcc


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 21, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.