Microsfot Access Question

Hello! I’ve been lurking here far longer then I’d care to admit (since March), and finally decided to start posting a bit… Anyhow, I was wondering if someone could help me out with MS Access. I’ve been trying to create a database for something, and have been getting confused by Functions. What I would like to do is set the default for certain fields to do a few things. One of them is to automatically read the text or number from the previous entry in that field, and enter it by default into this one, and the other is to read the number from the previous entry, add one to it, and enter it by default into this field. If anyone could help me here, I would greatly appreciate it. Thank you!

In the properties field of the control (field) that contains the data that you want to use to populate the next field, use the after update event. You can run a query based on that value, and populate the next field. In general, this is rather basic. FWIW, check out this site for beginning to mid-level Access help (Dev Ashish’s site). Good luck.
http://www.mvps.org/access/

I wrote a similar message in a recent thread, because Access is the frequent subject of GQ posts. Please refer to Dev Ashish’s Access Web. Also refer to the MS Access Newsgroup. If you don’t have access to an NNTP server, visit Google portal to comp.databases.ms-access. You might also be interested in the Access 2000 Developer Handbook. Although some of us have been developing with Access for years (like me), your best source of information are those three items I’ve listed above. You’ll learn more, too.

oh yeah, and Welcome to the Straight Dope! :slight_smile:

Thank you for all your help! And the typo makes me feel QUITE embarassed on my first post…

Would you mind if I e-mailed you any additional questions if I can’t find them from your links (thanks, by the way!)? It’d help me out a lot!

Thanks!

Sorry for the faux pas on my part on not welcoming you to the board. You may email me but I cannot promise an alacritous response, work you know (bwah-hahahahaha). Good luck.

Please, no, not another Access user!

Wha? Is there something wrong with access? Or are you guys sick of answering questions about it?

I’m a database analyst. In my professional opinion, Access is little better than a toy and should not be used for any serious database project.

The engine underlying Access is buggy and weird. Access databases have a tendency to go “poof” without warning, and quite frequently once “poofed” they cannot be “unpoofed”. Factors that make your Access database more likely to go “poof” include the use of indexes, the use of related tables, and multiuser operations.

In addition, Access is completely devoid of any data security, relying entirely on NTFS for that, which gives you no granularity of access (you can give a user have all access, read-only access, or no access, but only to the entire database; you cannot grant varying access rights by table or by column).

For these and other reasons, I strongly disrecommend the use of Access for applications which are mission-important or where multiple users will be accessing the data simultaneously.

The other reason I dislike Access is it leads to people developing databases on their own in Access, who then (when they get stuck) dump them in my lap with a “Please fix this so it works”. Since database design is one of those things that is very easy to do badly, usually these databases are pathetically designed and I end up spending a substantial amount of time redesigning the database properly and writing DTS packages to populate the new design with the data from the old system.

KellyM is right on most of her technical points. I am a professional software developer specializing in large-scale distributed applications which includes engineering, software, developer, and database administrationn.

For your average computer user or small business, Access is more than a toy - it is an effective and efficient tool when the solution is designed by a competent developer. I, too have had my share of “fix this” clients, but I always recommend Access as a good starting place for someone who wants to wrap their mind around the modern requirements of application development before moving on to more sophisticated technologies. Many of the people I see coming into professional development have begun with Access, discovered it’s limitations and want to do more. When I walk into a client’s officess and analyze their current process model - it’s usually a mismash of mainframe applications, small databases like Access, and spreadsheets.

Incidentally, KellyM, in one mission-critical application my company built about 3-years ago for a large corporation, we used Access as a front-end and stripped out Jet, making it a three-tier, distributed application with SQL as the back-end. Not only did it save the client large sums of money, but it significantly reduced our lead time. It’s a technique we have used from time to time in new applications because we can leverage the skills of less-skilled (and cheaper) developers so our senior staffers can concentrate on the business and data layers. You can do quite a bit more with the product than the book limitiations suggest. Recently, I wrote a utility to completely convert Access database applications to web applications that retain the identical look and feel of the original while migrating the data to proper databases. With only a few tweaks and performance enahcnes, it’s been quite popular with many of my mid-level clients who can’t afford total rewrites of their outdated software. I still prototype with Access for other more complex applications.

Don’t discount Access so easily. It is by far the easiest and most useful development product for new developers and home users with the smallest learning curve.

ugggh. Please forgive my atrocious spelling and grammar - I’m still a bit woozy from my wisdom tooth surgery. :slight_smile:

I’ve been considering doing this as a rapid migration path for at least one of our existing database “applications” at my current job. I managed to get Access to access a remote database once before, but I can’t remember for the life of me how I did it, and I can’t find documentation on how to do it (although I haven’t tried looking very hard yet). Any pointers you care to share? :slight_smile:

Well, the easiest methodit is to create linked tables to the backend database. Right-click in the Tables window and select “Link Tables”. You’ll see a file manager-like browser. Under file types, select “ODBC Data types” and set up your connection to the database. That’s a two-tier solution, and much better than Access alone, but it isn’t ideal. If you use MS SQL, ther is a built-in wizard that will do this for you with fairly good results.

The method I use is to encapsulate my data access and business functions within DLLs and use DCOM from within Access classes to populate forms and reports. That greatly increases your scalability and concurrency. To fill listboxes and combo boxes, you’ll have to have some familiarity with callback functions, though. Fortunately - it’s a write-once function. It’s a bit complicated to explain in depth here, but in practice that’s about all there is to it. If you can write COM components that can handle ADO objects like recordsets, the rest is trivial. And the best part is that JET is no longer necessary. :slight_smile:

I forgot to mention the best part about the migration wizard - for smaller companies, the migration is quite enough to continue business as usual with more users. Even better, in environments where the application is continually under stress, it can buy you significant time to develop the more complex solution.

Time for a nap …

I like Access, it’s a very useful and powerful tool for small business applications, it’s not so good for large data sets, but it’s a good place to learn the basics of relational database design and SQL.

A site Ii didn’t notice mentioned up there is Access World Forums - the folks there are very friendly and helpful.

Well, that gives me something to work with, at least. Of course, my experience is with programming in a UNIX environment, and so far the Microsoft development environment has left me rather perplexed (I swear that they designed it to be as confusing as possible) so it may be some time before I’m implementing DLLs using Visual C++.

looks at the more educated discourse and shutters

Anyhow, from what I see here, Microsoft Access isn’t exactly a first-rate database program, but one can learn from using it, and gain knowledge to apply to other database programs later, so it still might be worth my while… Or will it create more bad habits then it’d be worth? My current project is strictly personal and nothing at all important, but I wanted to know if it’s something worth continuing to tinker with. Thanks for the responses!

Netbrian, I am also a professional developer and I agree completely with evilhanz. Access is a fine database for a beginner and you can do some very sophisticated things with it. Also, it can later be “upsized” to SQL Server with relative ease, but I recommend you spend time trying to learn proper design techniques. However, note that KellyM is also correct in stating that you wouldn’t want to use Access for a large database or one that had more than 3 or 4 simultaneous users.

I don’t want to quibble with you, but the practical limit is a little higher than that: 10-15 on a single databse. If you split out the tables to a separate database, the number of concurrent users can jump to 30-50 read-only, with about half as many more editing users. The theoretical book limit of 255 is indeed overly optimistic. It depends on the business requirements and the overall design of the database. Once your database reaches 10-15 simultaneous editing users, it’s time to migrate to a more robust solution. There’s only so much you can do with a file-based database.