Solved

importing files through VB code

Posted on 2000-02-14
4
161 Views
Last Modified: 2010-05-02
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?
0
Comment
Question by:avi666
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
ture earned 70 total points
ID: 2518245
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
 
LVL 9

Expert Comment

by:Ruchi
ID: 2519316
DAO or ADO?
0
 

Author Comment

by:avi666
ID: 2527396
In Hebrew ture would be called "cli-cli"
which means mega-expert
0
 
LVL 22

Expert Comment

by:ture
ID: 2527870
Thanks, avi666!

It's nice to be a cli-cli!

/Ture
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question