LakshmanaRavula
asked on
I'm Facing a problem to import a Text file to Access 97 through a programme
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.
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.
ASKER
In a seperate Vb programme Not insidethe Access application
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"
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
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q262/5/37.ASP&NoWebContent=1
Leon
ASKER
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
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
ASKER
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
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
did the file (Exported.txt) is exist on your directori ?
ASKER
I wonder
How can we export a file without it's existance
How can we export a file without it's existance
ASKER
Moderators please delete this Question, since I'm not geting any response
Did you look at the link I gave above?
Leon
Leon
ASKER
Yes leon I tried with that.
But it did not helped me.
(please refer to my posting Date: 04/21/2004 10:36AM PDT)
But it did not helped me.
(please refer to my posting Date: 04/21/2004 10:36AM PDT)
Ok for tab delimited files look here:
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForText
Leon
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForText
Leon
ASKER
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
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
ASKER
Moderators please delete this Question, since I'm not getting any straight solution for my problem
AW