We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
261 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.
Comment
Watch Question

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

AW

Author

Commented:
In a seperate Vb programme Not insidethe Access application
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
 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"


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

Author

Commented:
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

Author

Commented:
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

Commented:
did the file (Exported.txt) is exist on your directori ?

Author

Commented:
I wonder
How can we export a file without it's existance

Author

Commented:
Moderators please delete this Question, since I'm not geting any response
Did you look at the link I gave above?

Leon

Author

Commented:
Yes leon I tried with that.

But it did not helped me.

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

Author

Commented:
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

Author

Commented:
Moderators please delete this Question, since I'm not getting any straight solution for my problem
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.