Multiple Access to Linked .txt Files?

[Darrenc]

We've had a problem for quite a while with regards to multiple users being able to view/run, queries/reports at the same time.
If someone is running a query/report, and then someone attempts to use any other query/report that’s already using the linked .txt file they get the error message:

The Microsoft Jet database engine cannot open the file ". It is already opened exclusively by another user, or you need permission to view its data.


I've read various posts with regards to user rights to the folder which the DB is stored, which isn't the problem in this case (we have tested and set user access to all read, write, delete etc).

I've also read that .txt files are managed in a different way than most other file types, in that DOS controls user access rather than jet?

So my question is:
Is there a way round this issue with linked tables that use .txt file as there source?

Thanks

Darren

 

[macca the hacke]

Hi

As far as I am aware the only way round the issue is to import the tables rather than link them. This obviously is no help if the text files are constantly updated, but if they are only updated daily then import them.

 

[Darrenc]

I've been asked if i found a solution to this problem. So i thought i'd reply here in case anyone else needs a fix.

macca the hacke was right, the only way to have multiple user access the same linked text file is to import the tables and not link to them.

The text files i wanted to link too were exported text files written from UNIX every night.
So to get round this i created a database that's sole purpose was to import all the data every morning before all the users come in. And then all my other database's would link to the freshly imported data.

This was quite simple to do. I created a form with a timer, and soon as the timer hit a predetermined time then it would fire a macro, the macro would delete all the tables in the database and then import them again.

There are a couple of important things you must do though.

1. You will need to import the tables manually the first time so that you can save the Import Specification, this will ensure that every time you import the data you use the same table specification every time.
   
2. Eventually deleting and importing tables will cause the database to 'bloat' once it hits 2gig you won't be able to use the database. So after the macro has finished importing the tables, i run a function that fires the 'compact & repair' procedure. Once that has finished it loads up the timer form and its ready for the next morning.

The database is completely automated, i just leave it running on a server, and just check on it from time to time to see if its fallen over.

Hope this helps someone.

Darren.

I’ve attached the database i use, just in case my explanation was difficult to follow.