Solved

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

Posted on 2004-04-12
17
217 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

910 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

22 Experts available now in Live!

Get 1:1 Help Now