Any known issues using Access with SQL Server
-
Hello,
I'm currently learning SQL Server and have had years of experience with Microsoft Access. I've heard from several DBAs that using Microsoft Access to retrieve data from SQL Server can cause issues on the server. I'm trying to get more information on specific types if issues seen so I can document them for our support team. I've done quite a bit research in forums and news groups and I can't seem to find anything specific on this topic, or at least nothing since SQL Server 2000 and Access 2000 were introduced.
Has anyone had any specific issues with Access that can help me shed some light on this topic? Is this still an issue, or was it a problem with the pre-2000 versions of these technologies?
-
Hi Glen,
I'm in the same boat as you so to speak.
I've been working with Access for the past 5 years and have recently been working on my MCDBA on SQL.
I would recommend this book:
I own this one and would definately recommend it.
I haven't run into any problems per se, how experienced are you at Access VBA?
FYI, Access VBA is a little different than the VBA in other Office apps.
Let me know if there's something in particular you're having trouble with, I'd be happy to help.
-Don
-
Hi Don,
Thanks for the book recommendation, I'll take a look at that one once I'm finished with the .Net book I'm currently studying.
I'm not experiencing any problems, but rather trying to gather data on any known issues with connecting to SQL Server from Access. I've heard from DBAs at work and online that there are known issues, but I haven't been able to track down any details. Perhaps it's just the fact that end users can define queries in Access that makes it such a pain for some DBAs.
I've been working with Access VBA for about 6 years now, and it's been quite a learning experience, and a beast at times. I'm currently studying VB.Net and SQL Server and reading "Programming Microsoft Visual Basic .Net (2003)" http://www.amazon.com/exec/obidos/tg/detail/-/0735620598/qid=1082561052/sr=1-2/ref=sr_1_2/104-0355755-2837533?v=glance&s=books I would definatly recommend this book to anyone who has VB6/VBA experience and is interested in the new .Net technologies.
-
this is the best book
"Microsoft Access Developer's Guide to SQL Server". its link is
http://www.amazon.com/exec/obidos/tg/detail/-/0672319446/103-0676301-1184601?v=glance
you must take care from Hanging Issue (Locking problem between Access and SQL Server)
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 -
Hi Alamir,
Thanks very much for the link. I'll add the information to my documentation.
- glen
-
Hi Glen,
We have been using Access 2000 and SQL Server 2000 for the past two years without any issues. All the tables are linked uisng DSN-Less ODBC connection.
All the big updates and inserts and deletes are done in the SQL Server stored procedure due to performance problems. The stored procedures are called from Access using OLE DB. Also we process the report logics in SQL Server and move the data required to a table which is then used in Access to run the reports. This improved the performance on reports as well.
Rushdi
-
We use Access 2000 to talk to SQL 2000 all the time, with no known issues other than those documented--Access 2000 has some limitations on being able to define SQL table layouts, but I suspect newer versions of Access have resolved this.
One application involves Access writing HTML reports every 2 minutes, 24/7 for periods of days to weeks. It hums along just fine. On rare occasions Access dies, but that may be an artifact of how the program is written. It doesn't cause server problems--it's just an ODBC application talking the the server and that is a completely supported thing. Practically all our production apps also talk to SQL using ODBC, all day.
-
Thanks for all the replies!
From what I've been able to gather from various sources, most of the problem stems from building the views in Access from linked SQL server tables, and then binding those views to forms or reports. Using temporary tables, stored procedures, or caching data in the mdb file appears to thwart most blocking issues on the server. However, this really isn't an Access problem but more of an application design problem that can also affect any application platform that doesn't optimize the server connection.
One DBA I've spoken with actually said that if Access can cause problems on the server (other than poorly written queries), then there is a problem with the server configuration, not Access. (He also said that if I quoted him, he would deny he even knew me. :cool
This seems to be the most logical conclusion at this point since I've yet to find any case studies or documented bug reports on the subject.If anyone has any stories to share (horror or otherwise), please share them.
- glen
-
Only one issue to avoid that I have run into: make sure your bit fields in SQL are set to NOT NULL. MS Access cannot handle nulls in bit fields so if that should occur, you simply get a generic message that the data on your form cannot be updated that tells you nothing about what the origin of the problem is.
Other than that, I've had SQL backend for two years with no significant problems.
SMK
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply