Using Microsoft Access with Borland Delphi: The article assumes that the reader knows how to create a basic Microsoft Access database and has some knowledge of. MICROSOFT ACCESS Questions and Answers pdf free download,MS ACCESS objective type Questions and Answers,multiple choice interview questions,viva,online quiz. How to convert the data in small- to mid-sized Access JET database projects to SQL Server 6.x.
Setting up an MS- Access DB for multi- user access. I find the answers to this question to be problematic, confusing and incomplete, so I'll make an effort to do better. Q1: How can we make sure that the write- user can make changes to the table data while other users use the data?
Do the read- users put locks on tables? Does the write- user have to put locks on the table?
Does Access do this for us or do we have to explicitly code this? Nobody has really answered this in any complete fashion. The information on setting locks in the Access options has nothing to do with read vs. No Locks vs. All Records vs. Edited Record is how you set the default record locking for WRITES. No locks means you are using OPTIMISTIC locking, which means you allow multiple users to edit the record and then inform them after the fact if the record has changed since they launched their own edits. Optimistic locking is what you should start with as it requires no coding to implement it, and for small users populations it hardly ever causes a problem.
All Records means that the whole table is locked any time an edit is launched. Edited Record means that fewer records are locked, but whether or not it's a single record or more than one record depends on whether your database is set up to use record- level locking (first added in Jet 4) or page- level locking. Frankly, I've never thought it worth the trouble to set up record- level locking, as optimistic locking takes care of most of the problems. One might think that you want to use record- level pessimistic locking, but the fact is that in the vast majority of apps, two users are almost never editing the same record. Now, obviously, certain kinds of apps might be exceptions to that, but if I ran into such an app, I'd likely try to engineer it away by redesigning the schema so that it would be very uncommon for two users to edit the same record (usually by going to some form of transactional editing instead, where changes are made by adding records, rather than editing the existing data). Now, for your actual question, there are a number of ways to accomplish restricting some users to read- only and granting others write privileges.
Jet user- level security was intended for this purpose and works fine insofar as it's "security" for any meaningful definition of the term. In general, as long as you're using a Jet/ACE data store, the best security you're going to get is that provided by Jet ULS.
![Microsoft Access 2003 Show Database Window In Access Microsoft Access 2003 Show Database Window In Access](https://www.techonthenet.com/access/database/images/display_navigation_pane2007_005.png)
It's crackable, yes, but your users would be committing a firable offense by breaking it, so it might be sufficient. I would tend to not implement Jet ULS at all and instead just architect the data editing forms such that they checked the user's Windows logon and made the forms read- only or writable depending on which users are supposed to get which access. Whether or not you want to record group membership in a data table, or maintain Windows security groups for this purpose is up to you. You could also use a Jet workgroup file to deal with it, and provide a different system. The read- only users would log on transparently as admin, and those logged on as admin would be granted only read- only access. The write users would log on as some other username (transparently, in the shortcut you provide them for launching the app, supplying no password), and that would be used to set up the forms as read or write. If you use Jet ULS, it can become really hairy to get it right.
It involves locking down all the tables as read- only (or maybe not even that) and then using RWOP queries to provide access to the data. I haven't done but one such app in my 1.
Access development. To summarize my answers to the parts of your question: How can we make sure that the write- user can make changes to the table data while other users use the data?
I would recommend doing this in the application, setting forms to read/only or editable at runtime depending on the user logon. The easiest approach is to set your forms to be read- only and change to editable for the write users when they open the form. Do the read- users put locks on tables? Not in any meaningful sense.
Jet/ACE does have read locks, but they are there only for the purpose of maintaining state for individual views, and for refreshing data for the user. They do not lock out write operations of any kind, though the overhead of tracking them theoretically slows things down.
It's not enough to worry about. Does the write- user have to put locks on the table? Access in combination with Jet/ACE does this for you automatically, particularly if you choose optimistic locking as your default. The key point here is that Access apps are databound, so as soon as a form is loaded, the record has a read lock, and as soon as the record is edited, whether or not it is write- locked for other users is determined by whether you are using optimistic or pessimistic locking. Again, this is the kind of thing Access takes care of for you with its default behaviors in bound forms.
You don't worry about any of it until the point at which you encounter problems. Does Access do this for us or do we have to explicitly code this?
Basically, other than setting editability at runtime (according to who has write access), there is no coding necessary if you're using optimistic locking. With pessimistic locking, you don't have to code, but you will almost always need to, as you can't just leave the user stuck with the default behaviors and error messages. Q2: Are there any common problems with "MS Access transactions" that we should be aware of? Jet/ACE has support for commit/rollback transactions, but it's not clear to me if that's what you mean in this question. In general, I don't use transactions except for maintaining atomicity, e. It works about the way you'd expect it to but is not really necessary for the vast majority of operations in an Access application.
Perhaps one of the issues here (particularly in light of the first question) is that you may not quite grasp that Access is designed for creating apps with data bound to the forms. Transactions" is a topic of great importance for unbound and stateless apps (e. For certain kinds of operations this can be problematic, and occasionally it's appropriate to edit data in Access with unbound forms. But that's very seldom the case, in my experience. It's not that I don't use unbound forms - - I use lots of them for dialogs and the like - - it's just that my apps don't edit data tables with unbound forms.
With almost no exceptions, all my apps edit data with bound forms. Now, unbound forms are actually fairly easy to implement in Access (particularly if you name your editing controls the same as the underlying fields), but going with unbound data editing forms is really missing the point of using Access, which is that the binding is all done for you. And the main drawback of going unbound is that you lose all the record- level form events, such as On. Insert, Before. Update and so forth.
Q3. Can we work on forms, queries etc. How can we "program" without being in the way of the users? This is one of the questions that's been well- addressed.
All multi- user or replicated Access apps should be split, and most single- user apps should be, too. It's good design and also makes the apps more stable, as only the data tables end up being opened by more than one user at a time.
Q4. Which settings in MS Access have an influence on how things are handled?"Things?" What things? Q5. Our background is mostly in Oracle, where is Access different in handling multiple users? Is there such thing as "isolation levels" in Access? I don't know anything specifically about Oracle (none of my clients could afford it even if they wanted to), but asking for a comparison of Access and Oracle betrays a fundamental misunderstanding somewhere along the line. Access is an application development tool. Oracle is an industrial strength database server. Apples and oranges.
Now, of course, Access ships with a default database engine, originally called Jet and now revised and renamed ACE, but there are many levels at which Access the development platform can be entirely decoupled from Jet/ACE, the default database engine. In this case, you've chosen to use a Jet/ACE back end, which will likely be just fine for small user populations, i. Jet/ACE can also be fine up to 5.
Microsoft Office help and training. Master the skills you need in minutes, not hours. Learn new cool things, work smarter, and get the most out of Office 3.