From SQL Server to MS Access 2007

Question

Could you recommend a good resource on learning MS Access from the perspective of a developer with a good background in SQL Server.

Also some best practice tips would be welcome, in areas such as

• multi-user access support
• security; both the database and the front end Access interface (Forms,Reports)
• deployment

Basically if you have experience in both SQL Server and MS Access development what best practice advice would you give to your fellow developer with SQL Server experience that needs to develop an application in MS Access (both the database and the front end).

The application in question will be hosted on the server and shared by multiple users (10 to 20). It needs to be written exclusively in MS Access 2007. Data to be accessed with the use of Forms and Reports (no direct access to the tables). There will be 3 different user types - which implies different access rights. All within an Intranet set-up.

I realise that the question is quite broad but I am hopping to use the answers as a good starting point and I will appreciate any input on this

Thank you

EDIT

Thank you for you answers so far. Just wanted to clarify that in this particular scenario, MS Access is the only option

Answer 1

MS Access is a great tool but like any tools has to be used correctly. I would wager that 80% of all access applications are built badly along the lines of binding a form to a whole table etc.

With all of my access applications I do not use bound forms but instead control the whole IO using code and if the project is never going to go outside of access then I would strongly recommend using DAO to control the data.

One of my apps is used by around 150 users (10-30 concurrent) and has no problems at all. There are some excellent sites from the access MVPs and I have learnt a lot from them.

The short answer is, if it is designed correctly access should be perfect for that type of project. Ignore the naysayers and people how say access is not designed for multi-user environments and code it right from the start

Answer 2

Access is an excellent front-end for sql server. Jet is also usable, but with reduced performance and reliability.

Nice web sites:

• Allen Browne (MVP) has a great site
• This site showing differences between Access and Sql Server is very helpfull for me

Books:
There are plenty, but I would mention O'Reilly's Access Cookbook 2d edition.

Answer 3

Unfortunately Access suffers from bad press and hearsay, much of it very out-of-date indeed.

There are some useful notes here:

MS Access 2003 - Good book on learning advanced VBA

Is MS Access (JET) suitable for multiuser access?

Setting up an MS-Access DB for multi-user access

https://stackoverflow.com/questions/469799/what-are-the-appropriate-uses-for-ms-access

There are a number of other useful threads within the ms-access tag, written by people with a fuller experience of Access than seems to be evinced in some of the comments.

I would imagine the main difficulty for someone with SQL-server experience will be adjusting to the more limited Jet/Ace SQL.

Answer 4

MS Access doesn't work well with multiple users, security sucks, and there's a lot of performance issues with large tables. I'll just venture to say that this is an ABUNDANTLY bad idea.

-- EDIT --

To expound on "security sucks":

Jet uses a "weak method of encryption and should never be used to protect sensitive data" - http://msdn.microsoft.com/en-us/library/aa139961(office.10).aspx

"Access 2007 does not provide user-level security for databases that are created in the new file format (.accdb and .accde files)." - http://office.microsoft.com/en-us/access/HA101980471033.aspx?pid=CH100621891033

"all users can see all database objects when you open databases that were created in Office Access 2007." - http://office.microsoft.com/en-us/access/HA101980471033.aspx?pid=CH100621891033

-- END EDIT --

If the price and the ease of reporting is the primary concern driving the MS Access direction, consider mySql with any one of the many report generating tools that exist for it.

I worked at a company that used a MS Access database with a max of 4 concurrent users in it, and it continuously choked.

I would VERY literally refuse to build in Access. If that requirement is set in stone and can't be changed, I would walk away now, before your life is ruined and you're stuck in an endless loop of trying to patch a system that should have never been built in the first place.

Other free options to mySql also exist, as mentioned in comments:

PostgreSQL

SQL Compact

SQL Express

And there are MANY other free/cheap and decent solutions.

If you AREN'T going to walk away, and the MS Access requirements AREN'T going to change : ( see:

Get started with Access 2007 security

You also might want to read this document about some of the potential problems and solutions with things you're going to face.

Answer 5

Honestly I love access. What a tool for the intermediate developer. And it comes with MS Office. You can create full-blown apps with this inexpensive API.

Yes there are downsides to using MSAccess. Multi-user support is the BIGGEST downside. But lets face it - Access wasn't designed specifically for this.

You are looking to create an application for 10 to 20 users. Access is perfect for this. The problem you will face is having those users all accessing the frontend and tables at once (again with the multi-user). Access as a database engine is only good for a few connections. Access includes many tools to reach your solution however.

I suggest creating your frontend as you see fit with Access. You can publish an MDE file to distribute to end users. This resolves multiple users accessing the frontend. You can then upsize your tables in Access and publish them to a SQL database. Lets face it - your nuts not to use SQL for the backend when you're dealing with 10+ users. SQLEXPRESS is FREE. There is no max on connections as well. This resolves your multi-user issue.

The only prerequisite here is knowing or being able to learn VB as it is the lifeblood to Access programming. Your options with Access are limitless.

EDIT: You can set users & workgroup permissions in Access. Also - Publishing an MDE file ensures no changes can be made to the frontend. The MDE file is a read-only distributable. Nice, huh? Also there is no access to the tables once you upsize to SQL. How about that?