Solved

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

Posted on 2004-04-12
17
220 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
ID: 10807453
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
ID: 10807907
In a seperate Vb programme Not insidethe Access application
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
ID: 10809655
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Expert Comment

by:Ozhee
ID: 10810425
 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
ID: 10868334
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
ID: 10880858
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
ID: 10880985
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
 

Expert Comment

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

Author Comment

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

Author Comment

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

Expert Comment

by:leonstryker
ID: 10982094
Did you look at the link I gave above?

Leon
0
 

Author Comment

by:LakshmanaRavula
ID: 10989177
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
ID: 10989309
0
 

Author Comment

by:LakshmanaRavula
ID: 10989435
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
ID: 11044666
Moderators please delete this Question, since I'm not getting any straight solution for my problem
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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…
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…
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…

773 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