Jet database engine message

[Guest]

I have a db split into a front and back end. Front end resides on several
PC's accessed by several users. Back end on a community server.

Each day a text file is downloaded from a second party program into a
community folder. It containes apprx 1300 records.

I added a link to the text file in each copy of my front end database...the
idea is that several users can open a form that displays the data from the
linked table/text file.

However, when the second user attempts to open the form, they receive the
following error message:

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

I gave read/update/delete/insert access to everyone in the security file...

The whole point goes out the window if only one user can open the form at a
time. (Several staff members view specific areas of data on the text
file...thats what make the access form so useful, they can filter and sort on
any data type they happened to be tasked with)

I am the administrator for the db...when I log on as myself on another PC
under my log in, I get the same message...

I have never run into this issue before and would appreciate any help.

TIA.

Antonio

 

[Guest]

Antonio,

A text file does cannot have a primary key or unique index. Thus, the ISAM
driver can’t lock individual records, so it locks ‘em all with a file lock.
You can add new records to a linked text file, but once committed, you cannot
edit or delete the record.

Why not just import these records into a table? That way, you can take
advantage of the power of Access. My guess is that in your networked
situation, 1300 records are travelling down the skinny network wire, since
Access cannot use an index. Seems to me like you would be a lot better off to
import the records at the beginning of the day.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Tom,

Thank you for the insight.

The reason I went with a file "viewer" instead of importing the data is
because of the dynamic nature of the report. This particular report lists
unresolved bills. The number of bills not "dropped" changes by the hour
depending on the situation.

With a imported file that fluctuates such as this one, I am wondering about
instances where a bill listed on the report is resolved without any action on
our staff. In this way, we would be importing the record into a table that
would not reflect any recent changes since the last upload....unless it
somehow updates the table where the data resides based on the matching
account number NO LONGER being displayed on the linked text file...does that
sound like something that can be done? Or does my predisposition for miracles
getting the better of me?

Thank you.

 

[Guest]

Hi Antonio,

What is the source of data used to generate the text files? If it is another
database, such as SQL Server, Oracle, MySQL, Sybase, etc., it seems to me
that you would be much better off to just link directly to the source data.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Tom,

Its a program called Meditech and I am not sure what platform it might be
built on.

In the end, I think you are right and I am going to query the IT folks about
the possibility.

Thank you very much for your help.

Antonio