importing files through VB code

i have a tab-delimited text file and i want to import him as it is to a table in access through VB code.
how?
avi666Asked:
Who is Participating?
 
tureConnect With a Mentor Commented:
avi666,

Here's one way to do it...

Private Sub Command1_Click()
  'This procedure needs references to the ADO and ADOX libraries

  Dim cn As New ADODB.Connection
  Dim cat As New ADOX.Catalog
  Dim tbl As New ADOX.Table
  Dim rs As New ADODB.Recordset
 
  Dim x As String
  Dim arr As Variant
  Dim i As Integer
 
  'Initialize connection and catalog
  cn.Provider = "Microsoft.Jet.OLEDB.4.0"
  cn.Open "c:\northwind.mdb"
  cat.ActiveConnection = cn
 
  'Define new table
  tbl.Name = "TestTable"
  tbl.Columns.Append "FirstName", adWChar, 30
  tbl.Columns.Append "LastName", adWChar, 30
  tbl.Columns.Append "Age", adInteger
 
  'Add table to catalog
  cat.Tables.Append tbl
 
  'Open the table as a recordset
  rs.Open "TestTable", cn, adOpenDynamic, adLockOptimistic
 
  'Open tab-delimited file
  Open "c:\test\test.txt" For Input As 1
 
  'Read first line from tab-delimited file
  Line Input #1, x
  Do Until EOF(1)
    'Split tab-delimited text into array
    arr = Split(x, vbTab)
   
    'Create a new record
    rs.AddNew
   
    'Loop through each value in array and write to fields
    For i = 0 To UBound(arr)
      rs.Fields(i).Value = arr(i)
    Next i
   
    'Write new record to table
    rs.Update
   
    'Read next line from tab-delimited file
    Line Input #1, x
  Loop
 
  'Close tab-delimited file, recordset and connection
  Close 1
  rs.Close
  cn.Close
End Sub

Ture Magnusson
Karlstad, Sweden
0
 
RuchiCommented:
DAO or ADO?
0
 
avi666Author Commented:
In Hebrew ture would be called "cli-cli"
which means mega-expert
0
 
tureCommented:
Thanks, avi666!

It's nice to be a cli-cli!

/Ture
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.