Access Good or Bad 3

[nszczepanski]

Hello all,

I just took on a new job with a new company and they seem to use MS Access for a variety of functions. I am not an Access expert by any stretch but I need to know the negatives of using Access. If we upgrade windows can this cause Access to crash? Also, does Access have the ability to run a normalized DB that enforces referential integrity? Can an Access DB be backed up easily? My plan is to migrate off Access to something more stable. I guess I don’t trust Access and Microsoft. If anyone has any suggestions or any information with your experiences with Access it would be appreciated.

Thanks….

 

[fneily]

Sounds like you're also new to Microsoft Office.
Changing an Operating System version of Windows shouldn't have any effect on the Application programs(Access, Word, Photoshop, Freecell, etc.).
Access is a relational database management system (RDBMS). That means it must follow certain protocols which include normalization and referential integrity.
As far as back ups, just copy and paste the file.
What are your problems with Access? It's the most convenient RDBMS around. One file, one database. All the objects (tables, forms, queries, reports, etc.) go into only one file. Some DB's need multiple files.
I'm sure you'll find someone reading your post who agrees with you.

 

[jrbarnett]

Hello,

MS Access is a desktop relational database product based around the Jet database engine, same as Visual Basic and other Office applications (unless you go for an Access Project, which uses SQL Server or MSDE as its database and Access just provides the user interface - forms, reports, toolbars etc).
Access does indeed have the ability to run a normalised database with referential integrity and check constraints, just as you would expect in client/server products. The files can be easily backed up by copying the MDB or MDE file to another location.

You seem to be approaching this from a negative point of view, which suggests to me that you may have reasons behind wanting to change and so are therefore using this to obtain evidence to support your quest.
I am therefore going to give both sides of the story:

Access applications can be very stable and operate for years without problems, indeed many members of this forum make their living developing and supporting applications in Access.
Access is superb at quick development for small to medium size database applications, or to use it as a front end for client/server databases such as SQL Server, MySQL or Oracle.

Where it is less good is when the applications become larger. The MDB file format has a size limit of 2Gb, so if you have a lot of data - or use BLOBs to store files inside an MDB/MDE file, you will hit that limit very rapidly. If you are getting anywhere near that limit, I'd strongly advise moving to something else as a backend store anyway.
I've mentioned that the data can be backed up just by copying the MDB file - if you don't secure the data properly, a database can easily be copied by using the operating system copy command to a CD or USB flash drive, resulting in data confidentiality to your employer. This can be circumvented by using Access purely as a frontend to a client/server database so the data are not stored within an MDB file.
Finally, a pure Access database file doesn't cope well with multiple concurrent users. If you need to have lots of concurrent users on the system, then use an SQL back end to an Access frontend using unbound forms (not linked tables as this is very slow - search the forums for more information as to why).

Operating system and Office version upgrades on your client PC's shouldn't cause Access databases to stop working, provided that the version of Access you run supports the Access file format version and any features you use, plus external references to libraries have been installed on your PC.

I've been developing applications in Access since 1999 and have three in use commercially within the UK; I've also written a free addin tool to document the objects within Access. In the right hands, Access is a very capable tool with a lot of potential, but just as with any tool, it can be misused and i've seen some absolutely dire applications over the last few years.

One last piece of advice: Bear in mind that as a relatively new employee, your employer won't take kindly to messing with business critical applications without very good reasons. I strongly suggest that you search these forums, the MSDN and Technet libraries on the Microsoft website, use Google to look for pages and sit down and use it in depth before putting any requests for such changes to your employer.

John

 

[TheAceMan1]

How are ya nszczepanski . . .

Its very brave of you to take on something you know not! [red]Its the equivalent of a student cheating on a test![/red] . . . If info works you'll be basking in it, as if you were an expert! If you fail . . . we all know what you'll be thinking!

Is it not an [blue]ulterior motive[/blue] to act like you know something you don't! . . . and we here at [blue]Tedk-Tips[/blue] have to take into account your putting this [blue]ulterior motive[/blue] on us!

On a somewhat positive note, if your gonna do better than Access, you'll have to move to [blue]Oracle![/blue] . . . [purple]PERIOD![/purple] . . . and when you tell the boss what Oracle licensing cost [blue]he/she will probably pull out a gun and shoot you![/blue]

My Advise! . . . [blue]backout as eloquently as you can[/blue] before this project jumps up and bites you in the . . .

See Ya! . . . . . .

 

[Ed2020]

On a somewhat positive note, if your gonna do better than Access, you'll have to move to Oracle! . . . PERIOD! . . ."

Errmmm....

Better for what? Surely this depends on your project?

What about SQLServer? MySQL?

Ed Metcalfe.

Please do not feed the trolls.....

 

[TheAceMan1]

Ed2020 . . .

No problem . . . have it your way . . . if you know SQL Server & MySQL to be better . . . so be lt! (apparently you've never used Oracle!) . . . with or without server! In as much as I would never pretend to be an SQL Server Guru!

So [blue]nszczepanski[/blue] I submit in lieu of [blue]Ed2020[/blue] . . . forget Oracle and turn your attention to [blue]SQLServer & MySQL[/blue]. Any info you need in this way will be explained by [blue]Ed2020[/blue] . . .

See Ya! . . . . . .

 

[Ed2020]

AceMan,

I don't recall saying anything at all about forgetting Oracle. Nor did I say either of the two other options I mentioned are better than Oracle. They *may* however be more appropriate than MS Access.

Just pointing out that there is more than just the one option and it depends on what nszczepanski is looking to achieve.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Pampers]

Hi nszczepanski (MIS),

You can make great applications in MsAccess, you can also create monsters in it. You can create great apps in MsSql, and you can create garbage in MsSql... and so on. It all depends how you are gonne use your product and how well you develop and design your application. But anyway, I think the time that MsAccess is a just a crappy home dbase-tool are long passe.

So don't look right away at the product your dbase is made in, but look at the design (bound or unbound forms, front-end and back-end, normalization, ref.int, business rules, levels where int. is used, cascading, table design, query design, indexing) and how it is used (number of users, intensity of data entry, concurrent users, speed in which the dbase is growing). If all is well, you stay with the product. If not, modify dbase, maybe change product... or change jobs.





Pampers
Just back from holiday...
that may it explain it.

 

[TheAceMan1]

Ed2020 . . .

No problemo . . . I mentioned Oracle brcause I know it well. Not so with SQLServer or MySQL . . .

See Ya! . . . . . .

 

[Ed2020]

Righty ho! Problem solved.

Ed Metcalfe.

Please do not feed the trolls.....

 

[missinglinq]

I think one of the most crucial points here is that nszczepanski says that Access is used for a "variety of functions" and:

"My plan is to migrate off Access to something more stable"

yet he doesn't give a single instance of a problem or of instability that he's having with these apps! The questions:

"does Access have the ability to run a normalized DB that enforces referential integrity? Can an Access DB be backed up easily?"

shows that he has almost no knowledge of Access. A quick scanning of Access for Dumies would have answered these questions and many more.

And jrbarnett raised a very important point: diddling with business critical apps that are working, in order to replace them with apps that may or may not work as well, and will surely cost $$$$$$ is not likely to endear him to his new employer. My advice would be the same as jrbarnett: take the time to do a little basic research to learn about Access before dismissing it out of hand!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

Howdy missinglinq . . .

I couldn't agree more . . . [blue]very suspicious this![/blue] . . .

See Ya! . . . . . .

 

[Ed2020]

AceMan,

I think I may just have realised the gist of your point of view on this one.

Ed Metcalfe.

Please do not feed the trolls.....

 

[TheAceMan1]

Ed2020 . . .

Which one?

See Ya! . . . . . .

 

[Ed2020]

Your suspicions....

Unless I've completely misread your posts, in which case I think I am going to shut up and go away.

On a completely separate note I've just realised how interesting it can be looking at peoples' profiles on this site. Some very interesting information on them...

Ed Metcalfe.

Please do not feed the trolls.....

 

[TheAceMan1]

[blue] . . . I've just realised how interesting it can be looking at peoples' profiles on this site. Some very interesting information on them . . .[/blue]

Yeeeeees! . . . [blue]espcially when its the start of school season and there here for the 1st Time![/blue] . . . unless I've dealt with the post originator before . . . I always check 1st.

Adding to my suspcions, [blue]nszczepanski[/blue] has been back to give out stars, but not to say a word! This is against Tek-Tips policy. The stars are great but Tek-Tips management asks for a verbal confirmation.

Anyway Ed, I'm sure our paths will cross again.

In the meantime, lets see if we can find those worthy of Tek-Tips!

[blue]Have a great 3day weekend . . . Ya Hear! . . .[/blue]

See Ya! . . . . . .

 

[Ed2020]

Well a star for you for spotting it before me Aceman. I shall keep an eye out for similar situations in the future. ;-)

Ed Metcalfe.

Please do not feed the trolls.....

 

[PHV]

nszczepanski has been back to give out stars
FYI, nszczepanski gave NO stars

 

[TheAceMan1]

Howdy PHV . . .

Unless you gave them out?

In any case he has not returned . . .

See Ya! . . . . . .

 

[ProgramError]

This thread is starting to look like the Milky Way!

Personally, I don't like the basic access usage. I like a little more control and with the help of the chaps on here (Tek-tips) I've gained that control and knowledge thro VBA.
Thanks

I would, however, like to take things further and try oracle (on the advice of our IT team). I've already played around with MYSQL and found it an interesting way of working. To be able to use the access front end for form development with a MYSQL backend is very useful.

For a little background information our university uses Access, Oracle, MySQL and an SQL server for various projects. I think it all stems down to what the application requirements are.

Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.