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

Tillery

Why are you using MS Access? Is it an absolute requirement? I don't have much experience with Access 2007 but previous versions did not work well with multiple users - data corruption is a very real possibility. I would strongly consider something like SQL Server Express or MySQL if you have the option. – Humpage

Are those 10 to 20 concurrent users? Access could choke on that many users... – Bunkhouse

Find another job - that is a horrible downgrade. – Salot

@Humpage MS Access is in the requirement here, and there is no option to change it. @Bunkhouse - yes those 10 to 20 user can be potentially concurrent – Tillery

@OMG Ponies - true but sometimes you can learn some interesting stuff even if technologically it seems like a downgrade. Yes, I am trying to find a bright side ;) But anyway it is just a small hopefully once off project and the user requirements are pretty strict. One of the reasons the access is used is that it can be further updated, maintained by the client by a person with pretty decent knowledge of MS Access (tweaking reports, forms etc) – Tillery

@Tillery - the "person with pretty decent knowledge" can destroy an Access database with a touch of a button or the creation of a single query. I can't stress enough how bad it is to go this route. – Brei

@kristof: This is not an answer to your question but i just want to inform, If you only want to use Access DB because of needing an embedded db, i recommend you Firebird. – Plowman

@md5sum agree on this, but that will be the responsibility of that person to make sure that it does not happen – Tillery

You people are really incompetent if you can't design an Access application with a Jet/ACE back end for a mere 20 concurrent users. It's harder than doing it for 5 concurrent users, but not all that difficult -- a lot of the principles you'd use with a client/server back end transfer over directly. – Fled

Answer 1

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.

Successive

Thanks Remou, I am just looking throught some of the lined questions, and already found some useful answers, especially those written by David W. Fenton – Tillery

David has done some excellent rebutting of the FUD (Fear, Uncertainty and Doubt) spread by many folks whose statemtns are rather ignorant. – Unific

Gee, guys -- thanks. It gets my goat when people spread lies knowingly, or spout them through ignorance, so I have to respond. It does get tiring, though, so it's great that we have knowledgable Access folks like Tony and Albert Kallal around, as well as Access-respecting folks like Remou. – Fled

I like Access. When the job at hand calls for Access, it's a wonderful tool! I also hate Access when I have to go through conniptions to get it to behave the way I want. "Unresolvable outer join" (or whatever the error is) is just SO rotten. I could probably list 40 annoying flaws in Access off the top of my head--but I only know these flaws because I am good with Access and know how to work around them (mostly). So I guess it's a love-hate relationship! But yeah... lately I've taken to asking Remou Access questions when I run across them. :) – Karen

Answer 2

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?

Desideratum

cheers, I need to stick with Access or rather as pointed out Jet/Ace as the db engine, but it is good to know that migrating to SQL Server is an option with preserving the Access front end – Tillery

There's no need to upsize to SQL Server for such a small user populationm, unless the user population doubles, or the schema and business rules require users to constantly edit shared records. If users are primarly adding/editing new records, there are fewer conflicts than if existing records are frequently edited by multiple users. In the latter case, one might consider a transaction-based schema, so that instead of editing and re-editing the same record, changes to the original record are logged. Doesn't work in all situations, but re-designing the schema can overcome a lot of such problems. – Fled

Also, it's important for concurrency to design the most normalized reasonable schema, since that makes updates more granular (e.g., phone numbers -- multiple columns in a single record can produce more edit conflicts than multiple records in a separate table). Also, choose optimistic locking within the Access app and make sure you create all your tables with record-level locking instead of page locking. You might also consider a random Autonumber as PK since this will spread the data pages more uniformly than a sequential one. – Fled

thank you David. One question on random Autonumber. I believe that the primary key becomes a clustered index. Wouldn't choosing the random option lead to index fragmentation? – Tillery

Index fragmentation in Jet/ACE is going to happen anyway, since it's only after a compact that your table is clustered on the PK. Thus, you wouldn't get the benefit of the random Autonumber unless you compacted regularly. But it depends on where the contention is happening -- if it's happening only among the records added at the same time, you're not going to get any benefit from going random. But if the contention is for records added over the last week or two, an overnight compact could get you some benefit there. I'm not recommending it, just throwing out the possibility. – Fled

Answer 3

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.

Maffa

Access 2007 uses accdb and accde now, as opposed to mdb and mde. – Brei

@md5sum: right, but it can still use MDB and MDE, and that's what I use everywhere I work, because most projects have started with older versions, or because I need some backward compatibility. But now I will check to see the differences, thanks ;-) – Maffa

@md5sum: MDB is a native format for A2007. There is little reason to prefer it over ACCDB format unless you require integration with Sharepoint or you desparately need one of the few features of ACCDB format that are lacking in MDB. ACCDB also sacrifices Jet User-Level Security and Jet Replication. – Fled

Answer 4

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

Myel

Interesting that you use unbound forms. It's seldom that I don't use bound forms. – Unific

As far as I'm concerned, if you're not going to use bound forms, you really aren't interested in using Access. You lose all the data-bound events and have to record replacements for them. And it's just not that hard to write a bound app that has no concurrency problems -- 10-30 shouldn't be that big a deal. For 150 concurrent users, I'd certainly not want to use Jet/ACE as the back end, and moving to a server database eliminates a lot of the concurrency problems you'd experience with a Jet/ACE back end (though not all of them) without requiring you go unbound. – Fled

That’s a fair point and to a certain extent going unbound does get rid of a lot of the helpful things that access can do. I will admit that sometimes it is a pain to do some things but for the most part it is just the way I prefer to code things. There was a great article by one of the access MVPs about bound - v – unbound but I can remember which one, that was the turning point of me going unbound. – Myel

Answer 5

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.

Brei

I've not worked with it, but I can EASILY rest assured it would be a 200% improvement over using Access for this. – Brei

@md5sum: Agreed, anything is better than Access, especially when there are free alternatives. I'd pick Postgres over MySQL - Postgres has ranking/analytic functions (as of 8.4), when MySQL doesn't. MySQL views don't allow subqueries, and I believe Postgres supports the WITH clause. – Salot

@Kyralessa - Yes and no. On the no side, concurrent users modifying a single table in Access often run into issues with locking that don't occur as often with higher level database engines. On the yes side, it goes back to "the person with pretty decent knowledge" building queries and reports. – Brei

Very subjective. To answer "it sucks" if the question is "how" is just not helpful. (And it doesn't suck.) – Again

@Again - I've cited some sources for "security sucks". I can cite more sources for performance issues and multi-user issues if you'd like. I also DID give some very good sources for the OP to look at if he has no other options... which everyone else who has posted has somehow failed to do. – Brei

@md5sum Thanks for pointin out the weak sides of Access, it is definitely worth knowing. I need to stick with access here so will need to work within its limitations. – Tillery

It's downvoted for your extreme ignorance and bigotry. If your only experience of an Access app with a Jet/ACE back end is one with 4 users and it couldn't hold up, then you have no experience with a real Access app, but instead have only experience with an incompetently-designed app. I'm not claiming that badly-designed Access apps don't outnumber well-designed ones, only that judging the capabilities of Access/Jet/ACE on such experience simply shows how little experience you have. – Fled

The "weak encryption method" is outdated, as A2007 introduced new, much higher-level encryption. Likewise, back in that time period, MS was deprecating Jet because they wanted people to buy SQL Server licenses. That didn't work out very well, and now Jet/ACE is on a strong development plan with it getting a lot of neat new features. Yes, many of them are being implemented for compatibility with SharePoint, but some of the most useful coming features (like table-level data macros, i.e., all but triggers) will work just fine even without SharePoint. – Fled

The other article cited has hardly anything about engine-level security, because it's oriented towards ACCDB, a format that doesn't support ULS. The other considerations are mostly about code security, and preventing unwanted execution of code, which has nothing to do with data security. You apparently don't understand any of this, or you wouldn't be citing these as reasons not to use Access. If I could downvote you -3, I would do so. – Fled

@David - You clearly misunderstand... that's not my ONLY experience with an Access app with a Jet/ACE back end. I've worked in companies where they had anywhere from 4-30 concurrent users in a single database... it chokes every time, and that INCLUDES Access 2007. As far as the "weak encryption method" yes, it's one version old. The other article shows that you simply have to entirely build your own security model for people with permission to do any single thing in the database but not another. Essentially, his "person with pretty decent knowledge" can become every user able to write a query. – Brei

And by your own admission there are more badly-designed Access apps than well-designed ones, and it is clearly the intent of the OP to leave the app in the hands of a "power user", who obviously doesn't have the ability to make the piece of software in the first place, or the company would have him doing it instead of the OP. Access empowers anyone to screw up even a decently designed one. There's a very high chance that this app, no matter how well it's built, won't work at all a few months after this "person with pretty decent knowledge" does some "further updates and maintenance". – Brei

I've built many complex apps for power users that were well-designed and set up in a manner that segregated the power user's changes from the front end used by the other users. It's not that hard -- it's mostly a matter of proper design and pretty basic initial training for the power user(s). If all your experiences with Access apps have been bad, it means you've never encountered anything but apps developed by the incompetent. Don't blame the tool for that. – Fled

Your suggestion to use MySql does not make sense here. How do you create a form or report in MySql? You confusing the development system called ms-access with that of a database engine like JET or MySql. With ms-access you build applications and you are free to use Oracle or MySql for the back end. You can not use your suggestion of Mysql to build an form like you can in access. The security issues are not that of ms-access, but that of oracle or MySql or whatever database engine you pick to use with ms-access. If you need a secure data engine then choose one but that not ms-access issue. – Patagonia