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 (http://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 :(
Hi Pinal,
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.
Hi Pinal,
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.
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.