MS-Access users can not access linked text file

Question

I have a small Access database that is distributed amongst several users. They are not on my network, so we set them up with a small Access database that links to a txt file that we send them, so they have the ability to run canned queries/reports (that are just a copy of our main database's reports).

The problem is that if two users try to run reports (there is only one source table - the linked text file), then one of them gets locked out. We checked the folders and everyone has read/write access to the folder (but again we're just reading the text file, so does it matter if they have write access?).

Does anyone know why this is happening? Does Access automatically make a link to an external text file exclusive?

Answer 1

I finally just created a linker.MDB that will sit on the same shared folder as the data.TXT file at the client's location. (I don't have access to their network, and relying on my contact to place files where they need to go).

When the user opens their client.MDB file on their local PC, it will have a link to table on the linker.MDB, called "dailydata"

Also, their client.mdb will run code to open the linker.MDB database, run the following to import the txt file, and then close the linker.MDB database. This ensures that the data in the linker.MDB is always up to date with the latest. I may play around with the date attributes to only let the first call import the text file, but we'll see.

strPath = CurrentProject.Path

strFullName = strPath & "\data.txt"

strTable = "dailydata"

DoCmd.TransferText acImportDelim, , strTable, strFullName, True