Importing Text File

[NH]

(posted to: access.externaldata, access.modulesdaovba)

I have a load of text files sent to me on a regular basis (on CD) which I
want to automatically upload into an Access database. The files are comma
delimited, but have been given a .dat extension. If I try to import them as
they are, Access returns a "File is read-only" error. If I change the
extensions to .txt, Access imports them without any complaints.

I really want to import these files directly from the CD, rather than having
to copy them, rename them, etc..

Is there another way of importing where Access is not so fussy about file
extensions?

Ultimately, I want to be able to automatically import all of the data from
the files on the CD at the click of a button..

Thanks

Nick.

 

[NH]

Please note- the "file is read-only" message applies even if I copy the file
to my hard drive, and remove the read-only attribute. The file imports as
soon as I change the file extension to '.txt'

 

[John Nurick]

Hi Nick,

Recent versions of Access limit the ability to import textfiles with
non-standard extensions. Symptoms: Run-time error '3027' and/or "Cannot
update. Database or object is read-only. "

Here are a couple of ways round the problem:

This http://support.microsoft.com/default.aspx?scid=kb;en-us;304206
shows how to change the extensions that Access will accept. The neatest
method is the last, editing the registry (provided you're careful<g>).

This http://support.microsoft.com/default.aspx?scid=kb;EN-GB;306144
includes code that automatically makes a copy of the textfile with a
..txt extension and imports that.