Solved

importing files through VB code

Posted on 2000-02-14
4
163 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to With line 4 86
RUNRMTCMD from AS/400 13 125
Publisher:   Unknown     VB.exe Application 1 31
Copy a range from 1..n excel sheets to one destination sheet 2 71
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 …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

737 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