Forms use linked tables and have multiple users

[Shell]

Hi all,

I have an access database that has 1 main form that is created with
several subforms. The underlying data is several linked text files
(csv) which are created automatically each night by our HRMS system.
My database does exactly what I need it to do for the first person who
goes in. However, if a second user tries to open the database, they
get the error message that "The Microsoft Access database engine
cannot open the file '' . It is already opened exclusively by another
user, or you need permission to view and write its data".

Is there any way around this? This database is a informational only
so no users will make any edits to any of the data. I tried to split
the database to see if that would help but the wizard just said that
there were no tables so I couldn't split it, I guess it doesn't count
linked tables as tables.

Any ideas?

Appreciate it!
Michelle

 

[John W. Vinson]

Hi all,

I have an access database that has 1 main form that is created with
several subforms. The underlying data is several linked text files
(csv) which are created automatically each night by our HRMS system.
My database does exactly what I need it to do for the first person who
goes in. However, if a second user tries to open the database, they
get the error message that "The Microsoft Access database engine
cannot open the file '' . It is already opened exclusively by another
user, or you need permission to view and write its data".

Is there any way around this? This database is a informational only
so no users will make any edits to any of the data. I tried to split
the database to see if that would help but the wizard just said that
there were no tables so I couldn't split it, I guess it doesn't count
linked tables as tables.

Any ideas?

Appreciate it!
Michelle

You'll need to import, not link, the text files. Access Tables can be shared,
but text files cannot.

You may want to create a new copy of a backend database with tables
corresponding to the text files at the beginning of the day, and run a process
to import the tables; your frontend could then link to this database.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Shell]

Thanks, that requires someone to manually do that every day though
which is no good.

I thought about having the autoexec macro import the tables
automatically on database open, but I was worried about what would
happen if two users went in while that process was still running and
it tried to run again. I am assuming everything would crash.

Bummer, I was really hoping for a way around this but I guess there
may not be one.

I appreciate you replying!

 

[John W. Vinson]

Thanks, that requires someone to manually do that every day though
which is no good.

I thought about having the autoexec macro import the tables
automatically on database open, but I was worried about what would
happen if two users went in while that process was still running and
it tried to run again. I am assuming everything would crash.

Bummer, I was really hoping for a way around this but I guess there
may not be one.

Well, it's not necessarily the case that this must be done manually. You can
use the TransferText method in VBA code to import the text files into tables;
you can run this code from a Macro, and use Windows Scheduler to launch Access
and run the macro, at 5am or some suitable time after the text files are
available but before your staff starts work. You could even lock the database
during the process so nobody else could get in. I don't think you're as bad
off as you assume!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com