Basic Question for the uninformed Access newby 2

[ribhead]

Hello all.
I have a production entry system that I created in XL and I send everything to Access once the user submits the info. This is all done through SQL statements.

Q1. What are the major downfalls of Access? I hear so much stuff how IT won't support this or won't support that. Is it because there are bugs in Access or because most IT people aren't familiar with Appliction Software such as XL and Access?

Q2. If I'm using SQL statements am I better off using SQL or Oracle instead of Access? The scope of my project is multiple people(15-20) at time need to enter information once every 8 hours.

Q3. Is it true that Access has approx 2GB limit?

Q4. In my program I use the DMAX function for the highest entry number and assign the user that number. I then add one to that number and put it in the entrynumber field in case multiple users access the program at the same time. Is this the only way of making sure that my key field doesn't have duplicate records?

Sorry for being so verbose but since starting to learn Access I'm like a sponge.

Final question What is the best VBA book for Access? I have three VBA books for XL and I really enjoy them.

Any responses would be great

rib

I know a little about nothing and even less about everything.

 

[willir]

rib

I think you need to evaluate your needs.

Access was primarily built for a desktop situation. But developers and programmers have pushed the envelope. Oracle and other server based database engines on the other hand are heavy weights.

From my perspective,

Access pros
- Very affordable, about $300 to $600+ each user but this includes Office (can also use MDE executable which gets around this)
- Can run on a desktop, or server - hardware requirements are pretty light
- Simplified graphical approach makes it relatively easy for a person to put together a database
- Well suited for many "sub systems" in business, extracurricular (sports, volunteer, etc) and personal
- Less need for super technical abilities for support
- Extremely flxible. Work around solutions to resolve many problems
- Includes tools for compaction and integrity checks
- Large talent pool

Access cons
- Simplified graphical approach makes it relatively easy for a person to put together a database (meaning taht poor design, inexperience, etc can lead to problems)
- Not extremely good at networking, and record locking. (As stated, coding and strategies can address this limitation)
- Some concern about security, but security tools and strategies can address most issues

Pushing Access
- Depends on applicaiton and coding, but 10 or 25+ users can be problematic
- High density of transactions
- Can only recover from previous backup
- 2 GB database limit, but can use linked tables as a work around, but if you are at the 2 GB size, that is pretty big!
- 1 GB table size, but again - realistic?
- 255 concurrent connections max, but is this reasonable?

MS*SQL, Oracle, Informix, etc

Pros
- Can support large sized databases, TerraByte range (around 100,000+ or larger)
- User connections limited to memory on server
- Much kinder on network traffice
- Much more robust - online backups with archive logs (which means you can recover transactions AFTER a backup but before the prolbem occurred)
- Better security (MS*SQL integrated with NTFS)
- More tables, more relationshsips, etc
- Supports views, triggers, procedures, transaction loggin

Cons
- Much more expensive. ~ $5000 (standard) to perhaps ~$20,000 (enterprize) per processor or about $1500 for five user (standard) client access license to about $11000 for 25 user (enterprize) client access licenses BUT this is on top of the th epurchase of MS Office. (Prices will vary - based on some research last year)
- Requires a database administrator, and superior server support
- Serious hardware - Unix (or NT) for Oracle, NT / 2000 / 2003 server for MS*SQL

Oracle, Informix and MS*SQL are heavy weights suitable for enterprize applications. They will support large systems, but it will cost you - for original purchase and subsequent support.

Access is light weight in comparison but is still well suited for many sub systems, or smaller systems. It has much more flexibility and can be much easier customized to your needs.

My advice is to use the correct tool for the job.

Richard

 

[BNPMike]

Q1. What are the major downfalls of Access? I hear so much stuff how IT won't support this or won't support that. Is it because there are bugs in Access or because most IT people aren't familiar with Appliction Software such as XL and Access?

Access gets a bad name because it is so 'accessible'. This offends young techies who assume it must be uncool. In fact it is a reasonably good Visual Basic development environment that couples to the most powerful databases in the world. The Jet database is sophisticated but unsuitable for high workloads or critical transaction processing simply because it is exposed to multiple clients. There is no journalling or guaranteed roll-back etc.

Q2. If I'm using SQL statements am I better off using SQL or Oracle instead of Access? The scope of my project is multiple people(15-20) at time need to enter information once every 8 hours.

Access is an SQL system. It should support your usage profile. Remember you can always connect it to a a database like Oracle if that makes you feel more comfortable.

Q3. Is it true that Access has approx 2GB limit?

It is difficult to see why this would cause you a problem.

Q4. In my program I use the DMAX function for the highest entry number and assign the user that number. I then add one to that number and put it in the entrynumber field in case multiple users access the program at the same time. Is this the only way of making sure that my key field doesn't have duplicate records?

You can avoid duplicate keys as easily in Access as anything else. To get contiguous nembers (ie none missing in a series) needs a bit of programming. But that's true in anything alternative system.

Incidentally it would be useful to clarify what you mean when you say 'Oracle'. 'Oracle' compares with 'Microsoft' not with 'Access'. I can't see that Oracle products are essentially better or worse than Microsoft. Both can solve enterprise-level problems.

 

[ribhead]

Whufta !

A lot of info thanks a bunch. Thanks for the clarification Mike on Oracle and MS. And thanks Richard for the heads up on the expenses of other systems. Ouch! Sounds like for now Access is what I'll be using. So unfortunately for you both you'll probably be hearing from me more about Access and VBA. By the way are there any books you would recommend? Keep in mind I'm definitely not at your level.

Thanks again,
rib

I know a little about nothing and even less about everything.

 

[BNPMike]

You might want to look at Microsoft Office Access 2003 Inside Out by Viescas. It's a Microsoft book but perhaps has an advantage for you in that it goes through the various bits of Access from a Jet (the original database) point of view. Then goes through them again from the MS SQL Server angle. MS SQL Server comes free with Access (usually referred to as 'MSDE')and may be better for you if you are building a multi-user system. But don't worry if you don't want to use it, the book is very thorough anyway.

 

[nabiS2K]

There is one additional thing about Access. Since Access is not a client-server application, Access can unnecessarily overwork your network. With Access databases on a LAN, queries are noticeably slower for larger databases than for those on the local machine.

For interest sake, MSDE is the Microsoft SQL Server Desktop Engine (which is $$$ free $$$) is available at:

http://www.microsoft.com/downloads/...d1-a0bc-479f-bafa-e4b278eb9147&DisplayLang=en

MSDE also has a 2GB limit, but I have a SQL Server database with 4 million records which still is under the 2 GB limit. ;-)