how to import files without an extension in access 2000 1

[zjoskebest]

hello

how can i import files without an extension. Per file its about 40.000 records to many for exchange the records in *.txt files
who can help me ...? Thanks

jos bogers

 

[p27br]

Hi,

I don't think there is a restriction to the number of lines in a text file.

Here is a link that might help. It shows how to import a file with an extension that Access doesn't recognize.

HTH

 

[zjoskebest]

p27br

I´ve files with an extension *.ab . I´ve found the solution for this extensions in Microsoft help, but without an extension I can not find anything also could you help me how to import files to access without any extension
thanks

jos

 

[p27br]

I forgot to post the link, sorry !

http://support.microsoft.com/?kbid=306144

how about something like this :

First manually add a txt extension to your file, then start the import wizard and save the Import Specs. Cancel the import.
Remove the extension and run the following code (assuming all the files have the same specs and are imported to the same table.

Sub test()
Dim fso As Scripting.FileSystemObject
Dim strTmpFile As String
Dim i As Long
Set fso = New Scripting.FileSystemObject

With Application.FileSearch
.FileName = "*.*"
.FileType = msoFileTypeAllFiles
.LookIn = "C:\eisims\data"

If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
'create temp text file
fso.CopyFile .FoundFiles(i), .FoundFiles(i) & ".txt"
'import temp file to access
DoCmd.TransferText acImportDelim, "MySpecs", "MyTable", strTmpFile
'delete temp text file
fso.DeleteFile .FoundFiles(i) & ".txt"
Next i
End If
End With
set fso = nothing
End Sub

 

[p27br]

replace the line

 DoCmd.TransferText acImportDelim, "MySpecs", "MyTable", strTmpFile

with

 DoCmd.TransferText acImportDelim, "MySpecs", "MyTable", .FoundFiles(i) & ".txt"

 

[zjoskebest]

Thanks I will try it out

 

[zjoskebest]

Hello P27br

The problem is that we have with your solution a manually action 3 times a day in total 3 files loaded with about 40000 records. I want it automatically imported in ACCESS. In our previous access (1997) those files could be automatically imported in Access. (I mean files without extension) But now I´ve ACCESS 2000, it is no more possible to import the files automatically. Would you be so kind to help me out with this problem....?
Thanks
Jos

bogers.jos@lycos.nl

 

[zjoskebest]

P27br

I forgot something to tell. The files to import has a txt format but no txt extension . and as Access 2000 can not read / import files without extension we have to find something else

I´m not a vendor ... I´m a user How can I change my profile ...

regards

Jos Bogers

 

[p27br]

You say that with acces 97 it was possible. What has changed ? Have you tried converting the old database ?

If you want to run the code at scheduled times then have a look at this link

http://support.microsoft.com/default.aspx?scid=kb;en-us;209979&Product=acc2000

the code I posted adds a txt extension to the file on the fly, that's the only way I can thinnk of to solve the problem.

also see FAQ181-1154 (icon for running macro from desktop)