Connect to a Text file (.txt)

[KeithG]

Hello,

Is it possible to connect to a text file via ODBC with an Access database. I have tried with a pass thrrough query but I get an error stating you can not use ODBC to link to an external Microsoft Jet or ISAM database table. I know I could import the file but this would put my database way over the 2 gig limit.

 

[boblarson]

Keith:

Have you tried using the Microsoft Text Driver (ODBC Driver) for this?

 

[KeithG]

Yes that is the driver I tried and received the error with.

 

[RuralGuy]

Keith,
Have you tried importing the file into a Temp mdb and then link to it?

 

[KeithG]

I have thought of that but I have about 20 huge text files so I would probably need about 10 temp db and I am trying to avoid that.

 

[RuralGuy]

OK. Just wanted to make sure that thought process had been explored. Good luck. I'll chime in again if I think of something.

 

[boblarson]

Keith:

The only other thing I can think of is to process the files with VBA then. It would definitely take longer than an import, but if you open them as input and read through them and put the applicable data into the tables, that's at least an option (not a good option, but an option).

 

[The_Doc_Man]

I think I'm with Bob on that VBA path. With the addition that you should also compress your database before you even start this process and also should make a good backup copy.

 

[Moniker]

Is there a reason you can't just link to the text files without importing them? If the text files are fixed-width or delimited with a comma or something, this is a snap and is built-in -- no need for ODBC Text Drivers or anything else.

Go to File -> Get External Data -> Link Tables, then change the file type to "Text files" and link your large text files that way. It doesn't get added to your DB since it's just a link, so no worries about the 2GB limit. Then you can access it and use it just like a regular Access table.

~Moniker

 

[boblarson]

Hey Keith - It's funny as your situation came around to me last week. I found myself with a text file with 289,000 records to try to change from comma-delimited to tab-delimited so a program we had would recognize them. I thought about you as I created my solution. My code worked out great, but the input process for it would have worked for bringing the data in if you couldn't do it another way.

The code I had just changed the file type but a few small tweaks, here and there, would have worked. Did you get your problem solved?