Getting started with Access?

[rjbull]

I shall shortly be expected to develop some databases in Microsoft Access, which I've never used before.  Can anyone recommend any good books and other resources, please?

TIA...

[f0dder]

Ho humm, do you have any prior database knowledge/experience? Access itself is pretty darn easy to use, with all the clicky GUI goodness... but you obviously need some relational database knowledge in order to put the click-and-play goodness to good use.

Don't know of any resources, so this was just a "don't worry too much" heads-up

[J-Mac]

IMO, it seems easy enough when you start setting up a database. However I always run into problems as soon as I actually try to start using a form or table. Inevitably a VB edit window opens and I am immediately lost. Or maybe just intimidated. But I never do well within the VB edit window. If I perform any editing there, I apparently mess something up enough to make the database not work well at all anymore. Of course if I do nothing in the VB Editor, then I cannot use the database again, because it insists on throwing me back in there until I do something.  And as I already said, if I DO anything...  On and on, a vicious cycle has then started and another database attempt is abandoned.

Odd thing is I used to work with dBase III+ fairly often and with much success back in the mid to late '80's.  But MS Access -- which granted has a much nicer UI and looks as if it will be a snap -- just seems to confound me, all because something always opens the VB Editor!

Really frustrating!!

Jim

[Perry Mowbray]

As F0dder said, Access is pretty simple, but to do something specific that's not provided out-of-the-box, you'll generally need to write some code to automate forms, etc (which J-Mac seems to be having some issues with - I know I did when I first started with Access).

There's lots of stuff that's on-line that will be of use: you've probably already found those though...

Like most people here, I don't mind answering questions When I first started with Access I bought one of those Teach Yourself books:, that I wouldn't really recommend (well, if you buy a used one for $0.37 it's hard to go wrong - and at the very beginning it can be better to be reading from a physical book rather than the screen). I generally find them not much more than a reformatted help file... and I find that Google provides good enough answers.

[PPLandry]

Access is an excellent environment. I've developped tons of applications with it. If you need any specific help, I too can help out with the VBA code that is required (as others have said, this is required)

But if you want a easier database, check out sqlnotes. As superboyac said, it is like access but made easy. And your data is stored the same database format, so Access can read it. Discussed here:

http://www.donationcoder.com/forum/index.php?topic=10432.0

www.sqlnotes.net which is in DNS transfer state, so use the following IP for the next few days: http://98.130.34.115/default.aspx

Pierre

[BigJim]

I've found http://www.vtc.com/index.php to be very helpful and worthwhile for this sort of situation. You can sign up for the monthly unlimited access to all of their tutorials for $30 with no minimum required. I've used it one month at a time on a few occasions with an "I'm going to learn this application" as a 'project of the month'. Depending on what price you put on your own time, I think that it's pretty good value.

[rjbull]

Ho humm, do you have any prior database knowledge/experience?

No. 

My only database experience is with PC-File 5 for DOS, which is flat-file.  Actually, the relational aspect concerns me as much as using a new program.

Don't know of any resources, so this was just a "don't worry too much" heads-up

What, me worry? 

[rjbull]

Access is pretty simple, but to do something specific that's not provided out-of-the-box, you'll generally need to write some code to automate forms, etc

So I've heard, to the extent that someone described it to me as not so much an application as an application development environment.

There's lots of stuff that's on-line that will be of use: you've probably already found those though...

I admit I haven't looked yet.  This is something I'll be expected to do at work, and I'm still home on Christmas break.

I bought one of those Teach Yourself books: [...] that I wouldn't really recommend

I tend to wonder about all those rank upon rank of paving-slab tomes on various Microsoft applications...  I'll have to take a look when I next get to a bookshop.

[rjbull]

But if you want a easier database, check out sqlnotes.

Thanks, but sadly it has to be Access because the intention is for an application at work, and Access is what the company has licenses for.  Getting it to cough up for a license for something else, especially when the application has to run over a network, is not likely to be met with a yes.

[rjbull]

Thanks, all, especially for the offers of help! 

[CWuestefeld]

For applications of, say, 1-5 users, Access is a great DB platform. As a RAD platform, if you can do the job within its confines, without writing code, it's great. But if you do need to write code, the restrictions of Access's forms model become frustrating very quickly.

If you expect the amount of custom coding that will need to be written is non-trivial, and if you have any programming experience, I would consider a different route. Continue to use Access as the database engine, but forget about its RAD development capabilities. Instead, download one of the free Express editions of Microsoft's Visual Studio, and you'll have a free development environment for VB.Net or C#. They can interact with an Access database just as well as code you write in Access.

These are much more sophisticated (which is the flip side of more complicated ) but in my opinion they offer a much more coherent experience -- things will make more sense, as opposed to Access's platform, which has evolved into a Frankenstein monster without any real unifying philosophy (making it impossible for me, at least, to intuit how any operation ought to be accomplished).

I have the book that Perry suggested above, and I thought that it's pretty good, at least using it to quickly look up how to do a particular operation.

[Perry Mowbray]

For applications of, say, 1-5 users, Access is a great DB platform. As a RAD platform, if you can do the job within its confines, without writing code, it's great. But if you do need to write code, the restrictions of Access's forms model become frustrating very quickly.

I both agree and disagree here: I think the trick is not to fight Access, but to think in the "Access way". It's a toss up between flexible and frustrating... but I'd go with flexible. My most adventurous Access development was a local DB that integrated with a web enabled DB off-site via Web Services (sounds odd I know, but at the time there was not the budget to get the developers to implement everything in the web site so I did some bits locally in Access)... but it worked a treat! That's flexible!! 

If you expect the amount of custom coding that will need to be written is non-trivial, and if you have any programming experience, I would consider a different route. Continue to use Access as the database engine, but forget about its RAD development capabilities. Instead, download one of the free Express editions of Microsoft's Visual Studio, and you'll have a free development environment for VB.Net or C#. They can interact with an Access database just as well as code you write in Access.

This is another good suggestion if it fits your restrictions.

[PPLandry]

One big advantage of using the Access UI is that it gives the car keys to users. They are free to create queries, new/modify forms, create reports. But always, always use 2 databases, one uniquely for data and one for UI (and temporary data)

[CWuestefeld]

One big advantage of using the Access UI is that it gives the car keys to users. They are free to create queries, new/modify forms, create reports.

That may also be an important disadvantage. If I had a dollar for every time I had to unravel the bad data resulting from someone who didn't have the knowledge to properly model the data relationships then, well, I'd have a lot of dollars.

But always, always use 2 databases, one uniquely for data and one for UI (and temporary data)

Yes!

Next to proper E-R modeling, this may be the most important of all Best Practices for Access development; I can't believe that books (not to mention Access itself) don't make more of a big deal of it.

Doing this is absolutely essential to ongoing maintenance of your application. It allows you to let your users keep on using while you work on bug fixes and enhancements; when you've got your code layer complete you can simply swap it in. If you've got one monolithic .MDB, you have to dance around people using it; they've got to be locked out while you work on it.

If you're going to have multiple users, consider using replication rather than giving all access to a single physical file. Access's replication features are really remarkable, in some ways even more advanced than SQL Server.

[PPLandry]

One big advantage of using the Access UI is that it gives the car keys to users. They are free to create queries, new/modify forms, create reports.

That may also be an important disadvantage. If I had a dollar for every time I had to unravel the bad data resulting from someone who didn't have the knowledge to properly model the data relationships then, well, I'd have a lot of dollars.

True but if you split the DB into 2, and lockup the data-database, then if users mess-up the UI database, it can easily be restored. I never let user modify the data-database