Solved

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

Posted on 2004-04-12
17
224 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

Title # Comments Views Activity
SSRS expression Issue finding a string 10 91
Saving history changes to sub form 4 48
Add a task in Outlook from access 11 42
Exit a vb6 apps when a calling it apps closes 15 52
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

861 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