?
Solved

importing files through VB code

Posted on 2000-02-14
4
Medium Priority
?
166 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 280 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses
Course of the Month12 days, 18 hours left to enroll

777 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