Solved

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

Posted on 2004-04-12
17
227 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
[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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
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…

751 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