Best way to deploy Access read-only database that is linked to SQL tables

[Green Biro]

I have created an Access database on a file server (shared network drive) as
way of reading the tables of an SQL (2208) based application. I created a
dsn to point to the SQL server (different server to the file server) and
linked in some tables and then created queries, forms and reports. At the
moment, there are no native Access tables in my database (but there might be
in the future). I havedeployed my database as an MDE on the shared network
drive.

The problem I have is that when more than one user tries to access it at the
same time, they get the 'egg timer'. I think this is something to do with
the creation of the LDE file.

As I only ever want users to read the SQL tables and / or any native Access
tables, I'm thinking that multi user access should not be a problem. What
do I need to change to make it work? I have local admin rights to the SQL
server and my own computer but not to the file server or anywhere else.

Thanks in anticipation of suggestions.

GB

 

[Phil Hunt]

1) set up a readonly user for them to access the data. Because it is just
too easy to modify a record in Access.

2) Let everyone has a copy of the MDE. Sounds like they are not working in a
sharing environment.

 

[Green Biro]

Thanks for reply.

I must confess I don't know a lot about MDEs.

My user base is generally non computer literate and not up to much more than
clicking a link in an email (which is what I gave then to execute my network
drive based MDE) or double clicking an attachment.

Can I ditribute the MDE as an attachment that could be double clicked?
Would it know where to find the dsn and then link in the SQL tables?

Thanks.

GB

 

[Phil Hunt]

They have to have DSN setup ready to run the MDE whichever way.
You should be able to attach your MDE in the email and have them download to
their PC. Also I think they have to have Access run time thingy to open the
MDE. (I don't work with MDE that much)

 

[Green Biro]

As mentioned previously, I don't have access ot others' computers.

I think that I am going to have to abandon Access and just right an ASP page
to access the data directly from the SQL Server.

GB

 

[a a r o n . k e m p f]

If I were you, I'd just use Access Data Projects.. you can still use your existing forms and reports and it's a simple migration (if you've listened to Microsoft's recommendation to move to ActiveX Data Objects ADO).

With ADP, you don't need to do anything on the client machine, it's just a vastly simplified system.

-Aaron

 

[Access Developer]

Caution, Green Biro, Mr. Kempf only has one recommendation that he makes
over, and over, and over (that is, ADP with MS SQL Server) -- on occasion,
it turns out to be the right thing to do; most of the time, it only reflects
that he has a narrow, biased view and just doesn't understand database
development.

By the way, it's been multiple versions past since Microsoft actively
recommended switching to ActiveX Data Objects (ADO) for your default data
access method; their current recommendation for default data access method
is Data Access Objects (DAO) with both MDB/MDE and ACCDB/ACCDE, as well as
with ODBC to connect to server databases.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

message