SQLServer 2000 and MSAccess 2000.

Question

SQLServer 2000 and MSAccess 2000.

timberwood

SSC Veteran

Points: 277
More actions
April 26, 2004 at 9:20 am

#59255

I have a MSAccess application (not my choice) that uses SQLServer via linked tables. Currently there are about 5 users using the app and I need to expand it to about 10 users. Currently I have a copy of the MSAccess app on a file server that all the users use. My questions are:

1)      Can the SQLServer handle 10 concurrent MSAccess users?
2)      Should the MSAccess app be on a file server used by many or on each users PC?
3)      I am not experiencing locking / blocking problems but sometimes I get ODBC timeout errors. (I think MSAccess is periodically downloading, refreshing something on the MSAccess side).
4)      Can anyone recommend reading or links for information on creating MSAccess apps on SQLServer.

Thanks..

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

There are no restrictions connecting to sql server from ms access apart from the sql server limitations itself.

I have maintained MS Access applications linked to sql server with each user having their own database acting as a front end stored on their own c: drive. I have never seen any locking problems but I have seen some latency in the application when more users are connected as well as a few odbc timeout problems.

Personally, I would not recommend the practice of linking to sql server from Ms Access from a system design and maintenance view - I never adopt this policy when creating new applications. You are much better off running a vb application.

If you are going to use Access then you are better off connecting to sql server using ADO and working with recordsets to insert, update and retrieve records etc.

You can then build in error handling to take care of the timeout problems and stop the system from falling over and throwing the users out.

------------------------------
The Users are always right - when I'm not wrong!

jcwagne Valued Member Points: 57 More actions · Answer 2

I currently run an MSAccess 2K FrontEnd to SQL Server 2K for about 30 users with no issues. I run a copy of the front end on every machine's c: drive. While not the best choice, vb would be preferred, it seems to run pretty good, even over VPN connections without timing out.

I would recommend you do not run the Front End from the file server, as past experience has shown me that you run into a lot of headaches once you get to about 10 users with this method.

As for some reading, a couple of books I used when I took over this app were "Microsoft Access Projects with SQL Server", by Ralf Albrecht and Natascha Nicol, MS Press publishing; and "Microsoft Access Developer's Guide to SQL Server" by Mary Chipman and Andy Baron, SAMS publishing.

Hope this helps, got any more questions, let me know.

John

timberwood SSC Veteran Points: 277 More actions · Answer 3

Thanks for the information. I will move the app from the file server to each users pc. I need to improve my MSAccess and VB skills. Previously I was developing web apps in Cold Fusion so MSAccess is a few steps back for me. I'm sure you can build robust applications in MSAccess.

Thanks for the feedback.

TML-139925 SSC Enthusiast Points: 113 More actions · Answer 4

Jonathan,

How do you handle the errors?

""You can then build in error handling to take care of the timeout problems and stop the system from falling over and throwing the users out.""

Thanks you for your time.

Alamir SSCertifiable Points: 6085 More actions · Answer 5

of course this is the best book :

"Microsoft Access Developer's Guide to SQL Server".

you must take care from Hanging Issue (Locking problem between Access and SQL Server) which cause Access to make "ODBC timeout errors"

see this link for more help

http://www.access-programmers.co.uk/forums/showthread.php?t=35365&highlight=Hang+Access+SQL+Server

I hope this help u

Alamir Mohamed
Alamir_mohamed@yahoo.com

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 6

I am not the worlds best when it comes to error handling I am afraid. I have had users have timeouts happen when connecting to a database using ADO and it has brought up the standard ms access error message but has not thrown the user out of the system.

I am sure someone else out there will be able to help you out if you are looking to capture the actual event and deal with it another way.

------------------------------
The Users are always right - when I'm not wrong!

smknox Hall of Fame Points: 3275 More actions · Answer 7

I think Alamir may have mentioned the same book, but Microsoft Developer's Guide to SQL Server by Mary Chipman and Andy Baron is in my opinion by far the best resource.

Thanks,

SMK

Bill Nye Hall of Fame Points: 3248 More actions · Answer 8

Jonathan,

To reduce timeouts w/ADO, you may want to try adding something like this:

oconn.CommandTimeout=120

I think default is 60 sec.

Alamir SSCertifiable Points: 6085 More actions · Answer 9

please agan see that link ..

http://www.access-programmers.co.uk/forums/showthread.php?t=35365&highlight=Hang+Access+SQL+Server

because It will help u to solve to problems or locking between Accesss and SQL Server .. because Access cashes the whole table when you select data from it .. so it locks it ):

Believe me .. the book that i recommended is the best .. i have been searching alot before i buy it..

Alamir Mohamed
Alamir_mohamed@yahoo.com

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 10

Thanks for that Bill. I will revisit a couple of apps set up before I got here and use in future myself on my vb apps.

------------------------------
The Users are always right - when I'm not wrong!

SQLSrvrN00b Say Hey Kid Points: 688 More actions · Answer 11

Timberwood,

We are currently using the MSAccess / SQL Server configuration that you are using. We have approximately 330 users with about 30 users connected at one time. These Access dbs were created in '99 and use a combination of DAO (run queries against the linked tables) and ADO to exec Stored Procedures against SQL Server. Our limitation on concurrent users depends on the mood of the MDW file (Security File). If it is cooperative we have no problems.

We deploy a copy of the front-end application to the client machines using the Package and Deploy wizard. This is handy because it ensures that the client has everything that they need. I would definitely recommend that you move it off of the file server and onto the client's hard drive.

To date, I have experienced only one locking issue and this was with a stored procedure. The fix was to increase its timeout and let it execute.

**One thought, maybe you could use the ADP(Access Data Project). I have used this in testing and seems to be very efficient. If you do not have too many objects in your present app, you might want to take a look at this.

Hope this is helpful.

Yaron Ben Oved SSC Enthusiast Points: 107 More actions · Answer 12

Hey, I have been using MSAccess AND SQL Server with great success.

The key is to convert to a .adp (Access Project) No more locking problems, No ODBC needed, it is using the native drive. it works great,

Your application should run from the user's PCs, you can put a copy on the server and use bat files for user's updates of your latest versions

Using adp sorts out the need for Access security (mdw) as you are using a ado connection to SQL Server, It is deffenantly the way to go,

Another thing - Try using Access XP, It was released after SQL 2000 and is far more afficient then Access 2000.

bobsterboy SSCommitted Points: 1539 More actions · Answer 13

I have been using an Access2k front end to SQL 2k for about 2 years with 6 users. I've never had the locking problem and all 6 are in it all day.

I created link (on their desktop) to a batch file (on the server) that copies the front end from the server to their local drive every time they start "the program". The end users never know they're doing this because I changed the icon and it looks just like it always did. The major advantage to this is the automatic updates and their local clients don't bloat up.

As a side note, Access as a front end to SQL Server is a MUCH more stable environment than Access as a front end to Access data. This client had a MAJOR corruption every 6 months or so before I upgraded their data to SQL Server. No problems in 2 years and counting.

Alamir SSCertifiable Points: 6085 More actions · Answer 14

you said :

I created link (on their desktop) to a batch file (on the server) that copies the front end from the server to their local drive every time they start "the program".

can you please send me the lines you write in that batch file

thanks

Alamir Mohamed
Alamir_mohamed@yahoo.com