First of all, today is April 1- April Fool’s Day, so I have written this post for some light entertainment. My friend has just sent me an email about why a person should go for Access Database. For a short background, I used to be an MS Access user once (I will not call myself MS Access DBA), and I must say I had a good time with Database at that time. As time passed by, I moved from MS Access to SQL Server. Well, as for my friend’s email, his reasons considering MS Access usage really made me laugh. MS Access may have a few points where it totally makes sense to use it. However, in the email that I received, there was not a single reason which was valid. In fact, I thought it is an April 1st joke- just delivered a little earlier. Let us see some of the reasons from that email. Thanks to Mahesh Bhesania for sending this email to me.
- MS Access comes with lots of free stuff, e.g. MS Excel
- MS Access is the most preferred desktop database system
- MS Access can import data from MS Excel and SQL Server
- MS Access provides a real time database
- MS Access has a free IDE-to-VB Script
- MS Access fits well in your hard drive
I actually think that the above points are either incorrect beliefs of some users, or someone just wrote them to give some laughter with such inaccurate data. And, for the same reason I decided to browse the Internet and do some research on MS Access database to verify my thoughts.
While searching on this subject, I found the following two interesting statements from the site: Microsoft Access Database, Why Choose It?
- Other software manufacturers are more likely to provide interfaces to MS Access than any other desktop database system
- Microsoft Access consulting rates are typically lower for Access consultants compared to Oracle or SQL Server consultants
The second one is may be the worst reason for you to switch to MS Access if you are already an SQL Server consultant.
With this cartoon, have you ever felt like you were one of these chickens at some point in time? I guess that the moment might have just happened before the minute we say “I guess we were on the same page?” Does this mean we are IN the same table, or ON the same table?! (I accept bad joke!)
It is All Fools’ Day after all, so just laugh! If you have something funny but non-offensive to share, just leave your comment here.
Reference: Pinal Dave (https://blog.sqlauthority.com), Cartoon source unknown.
I work for a medium sized University and we have a developer who insists on using MS Access despite us having SQL 2005 and oracle servers. We have tried many times to convince said developer to move to SQL Server to no avail.
It makes me so sad :(
Your articles are full of knowledge. They are very fruitful for any sql developer who go through them.. This is a great job. Pinal could you please help us in understanding the data page architecture, ie. how data is saved in memory and how we access them through indexes. This topic will be highly useful for us. Thanks in advance.
I was hoping you would help me on this topic. I cant seem to find someone that has knowledge of using both types of DB…Access and SQL server. If I may, here is my situation:
I am running Access 2010 FE and SQL Server 2005 BE.
I can execute pass through queries to my SQL Server succesfully by using DSNless connections.
During my testing phase sometimes I need to restore my database to get back to my original records so I can rerun my pass through queries. What I have found is when I run a pass through query, it creates an active connection on my SQL Server. I see the connection via the SQL Server Management Console under the MANAGEMENT | SQL Server Logs | Activity Monitor, select view processes. There I can see which process ID is being used and who is using it when I run my pass through query.
Now the only way for me to restore my database is to KILL the PROCESS e.g. Active connection
Now when I have my restored database in place and re-run the pass through query, I receive a ODBC — Call Failed message box. I have attempted to run a procedure to refresh my querydefs but to no avail, I will still get the ODBC– Call Failed message box when I click on those objects.
Now there are two options on how to fix this problem, which in either case I find not USER Friendly.
1.Restart my Access Application
2.Wait approx 5-10 minutes to rerun the Pass Through Query
I created a function to trap my ODBC Errors and this is what appears:
ODBC Error Number: 0
Error Description: [Microsoft][ODBC SQL Server Driver]Communication link failure
ODBC Error Number: 3146
Error Description: ODBC–call failed.
So if for some reason, I need to restart my SQL server or kill a process (Active Connection) on my SQL server while the Access Application is currently connected via ODBC, the objects created via ODBC will not be retrieved properly till I execute the 2 workaround solutions as stated above.
Can you shed some advice on a solution? I appreciate your expert insight. BTW, please forgive me of my ignorance on this topic as I an still a newb.
I would consider Access as a development tool in only one case – to prepare a prototype/mock-up for users to agree future developments.
In other cases I find it not enough for enterprise development. If it’s going to support your business, then you shouldn’t use Access.
your statement denote that you’re not a hardcore ms access developer. MS Access as a front end to sql database work well in the enterprise and even in the cloud using Terminal Services.
Exactly. We use MS Access with DSNLess connections using SQL Server Native Client (and Linked Servers to Oracle) with the full range of Views, Stored Procedures and Data Mining.
This is distributed over Citrix (or Terminal Servers) nationally to hundreds of users (concurrently). All reports take user inputs, create custom queries (stored procedures) and use Excel Object Model Programming to create unique reports that include array formulas based on the specific records pulled. Excel reports are stored and highly organized in the users personal network folder.
Access via Citrix Client is available over extremely low bandwidth in very rural areas and works on Windows and non-Windows clients.
The rapid prototyping of applications, the power of SQL Server, and security of Citrix lowers the total cost of ownership.
The SQL Server Migration Tool for Microsoft Access allows for rapid prototyping of tables and queries that can be moved to SQL Server rapidly.
The rapid application development constantly beats the competition. It is not a web tool for millions of concurrent users. If the concurrent user base is under 250 users, a seasoned Access programmer with SQL Server (and Oracle) back-ends should be considered if time-to-market equals money.
B.T.W. – how about an article on Citrix Client for Smart Phone? Serving SQL Server data via Access to smart phone in low bandwidth with high security might be an interesting article.
I have been able to use MS Access to deploy a whole Point of Sale solution to a small appliance store with MS Access. Its easy deploy. it comes with reports that can be easily turned into pos receipts, monthly invoices and statements.
The interface is easy to design. Its easy to deploy in a google drive so you have continuous redundancy. I programmed the ability for it to print to pdf and email. The only problem is it gets unstable with several connections. Other than that its a great product.