Solved

I'm Facing a problem to import a Text file to Access 97 through a programme

Posted on 2004-04-12
17
213 Views
Last Modified: 2013-12-25
Hi,
I'm trying to import a text file through VB in to Access97. with the following code

DoCmd.TransferText acImportDelim, Specification1, TarTable1, SourceFile, True

While using this I'm getting an error message

"The Microsoft Jet database engine cannot find the input table or Query 'MySysIMEXSpecs' Make sure it exists and that its name is spelled correctly"

When I use the same code in the Access module it is working fine without any error.
But the problem is when I'm using it from a VB programme.
0
Comment
Question by:LakshmanaRavula
  • 8
  • 3
  • 2
  • +1
17 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
where is your VB code, in a separate VB 6 program, or is this using VBA inside the Access application?

AW
0
 

Author Comment

by:LakshmanaRavula
Comment Utility
In a seperate Vb programme Not insidethe Access application
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
Comment Utility
you cannot use the DoCmd method in a separate VB program.  That is part of the Access Application object.  That is why you are getting the error message, because the method is looking for the LOCAL table MySysIMEXSpecs, as if you were running from within Access.  But since you are not, that table does not exist locally, and the method will fail.

You would be much better off to Read the file, using the FileSystemObject, into your VB Program, and then save each record into the Access table.

AW
0
 

Expert Comment

by:Ozhee
Comment Utility
 Hi, try this code..

On Error GoTo LocalErrorHandler
   
    Dim FullName        As String
    Dim FileHandle    As Byte
    Dim ImportRecord  As String
    Dim flnName       As String
    Dim RowPosition   As Double
    Dim EmpNumber     As String

    flnName = App.Path & "\Exported.txt"
    'Delimiter you can give any or what is the input file
    Delimiter = ","

    FileHandle = FreeFile
    Open flnName For Input As FileHandle
    'Remove first line If it have Header
    Line Input #FileHandle, ImportRecord

    open your recordset
 
    'Looping in text files
    Do Until EOF(FileHandle)
      'Opening Line by Line
      Line Input #FileHandle, ImportRecord
      RowPosition = RowPosition + 1
      EmpNumber = Trim(Mid(ImportRecord, 1, InStr(1, ImportRecord, Delimiter, 1) - 1))
      'insert into your recordset
     
    Loop


    Close FileHandle
    MsgBox "Success !"
    Exit Sub
LocalErrorHandler:
    MsgBox "Error Occured :" & Err.Description, , "Error"


0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
Depending on the structure of your text file, you may also want to try connecting to it with ADO and transfering data that way.  Take a look here:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q262/5/37.ASP&NoWebContent=1

Leon
0
 

Author Comment

by:LakshmanaRavula
Comment Utility
Leon,
it is working for only comma Delimited and failing for tabDelimited text file

I'm working with text files which are tab Delimited

can you modify the code to suit me
0
 

Author Comment

by:LakshmanaRavula
Comment Utility
Ozhee,
Sorry for the delay,
As I'm poor in VB it took time to work on your method
i tried to modify to suit to me but I failed

It is giving me error while opening the text file

Open flnName For Input As FileHandle

At this line the error is coming
can you modify please

thank You
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Expert Comment

by:Ozhee
Comment Utility
did the file (Exported.txt) is exist on your directori ?
0
 

Author Comment

by:LakshmanaRavula
Comment Utility
I wonder
How can we export a file without it's existance
0
 

Author Comment

by:LakshmanaRavula
Comment Utility
Moderators please delete this Question, since I'm not geting any response
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
Did you look at the link I gave above?

Leon
0
 

Author Comment

by:LakshmanaRavula
Comment Utility
Yes leon I tried with that.

But it did not helped me.

(please refer to my posting Date: 04/21/2004 10:36AM PDT)
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
0
 

Author Comment

by:LakshmanaRavula
Comment Utility
Leon,
I've alredy mentioned that I'm poor in VB
I can't do any thing with the driver mentioned in the above link
I need a function or code which can import tabdeliminated text files(having Headers) in to access db
0
 

Author Comment

by:LakshmanaRavula
Comment Utility
Moderators please delete this Question, since I'm not getting any straight solution for my problem
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now