Questions That Should Be Asked More Frequently

[Previous] [Next]

Q. Does an Access QueryDef correspond to an ADOX View or Procedure object?

A. Access considers all queries to be QueryDefs, but many database systems categorize queries as either views or stored procedures. So, is an Access QueryDef a view or a stored procedure? The general rule is that the QueryDef is a view unless it's an action query (UPDATE, DELETE, INSERT, and so on), or unless it accepts parameters, in which case it's considered a stored procedure. If you add a QueryDef to an Access database, you can use Views.Append or Procedures.Append regardless of whether the QueryDef corresponds to a view or a stored procedure. In the Make Northwind sample on the companion CD, I add all QueryDefs to the database through Views.Append.

Q. I can't seem to add a QueryDef to my Access database. I don't get any errors, so I think I'm creating it correctly, but I don't see the QueryDef when I open my database in Access. Am I doing something wrong?

A. In this case, if you're not generating an error, you're not doing anything wrong. This behavior is the result of a problem in the Jet 4.0 OLE DB Provider. This problem is not isolated to ADOX. If you generate your own action query to create a QueryDef, you will not see the new QueryDef when you open the database in Access. However, the QueryDef does exist. You can see it in the Visual Basic DataView window, and you can call the QueryDef with ADO.

Q. The sample code in the "ADOX Security Sample" section of this appendix was handy, but I'm having problems with it. I was able to run it once, but when I ran it a second time, I got errors saying that the account name already exists. What went wrong?

A. Actually, nothing went wrong. The behavior is by design, but it's also worth explaining. Let's take a quick look at the first line of code that generates this error:

 cat.Users.Append "Al", "" 

In this code, it appears that you're adding a user to your database. However, what's actually happening is that you're adding the user to the Jet security database. When you run the code a second time, you've already deleted and re-created your database, but you're not deleting and re-creating the Jet security database. For that reason, the account still exists in the security database and you receive an error.

If you are simply using the sample code to learn a little about Jet security but don't plan to use it in your application, this answer is probably sufficient. But if you want to use Jet security in your application, let me again recommend that you read Chapter 10 of the Microsoft Jet Database Engine Programmer's Guide, Second Edition.

Q. Replication seemed like a great idea to me until I came to the end of the section on replication in this appendix. It seemed like you left out the resolution that would help me understand how to resolve conflicts easily. Did I miss something?

A. The replication discussion in this appendix ends abruptly because replication that involves two-way synchronization (in which the replica databases allow updates) is extremely complex. If a simple solution to resolving conflicts existed, I would have included it. My advice is to use two-way synchronization only if you can structure your database and your application in such a way that conflicts can be avoided or easily resolved.

For example, allowing the replica databases to modify a balance due field in a customer table would be unwise if you plan to use two-way synchronization. Say that you have a number of phone operators, each entering orders into a local replica of your main database. If two operators receive orders for the same customer, log their orders in an orders table, and update the customer's balance due locally, at least one of these changes will generate a conflict when the operators synchronize their databases. A better architecture for this scenario would be to have no balance due field in the customer table. The balance due could be generated instead by a query that examines the customer's order and payment histories.

Q. When I marked my database for replication, the auto-incrementing field started to behave differently. It looks like it's now generating random numbers rather than generating sequential numbers. Why is that?

A. The auto-incrementing field is behaving this way to prevent primary key violations. If the database continued to generate new values in the standard sequence (1, 2, 3, and so on), you would run into serious problems if you employed two-way synchronization and based the table's primary key on the auto-incrementing field. Suppose you create a master database and two replicas. And suppose you have an Orders table that uses an auto-incrementing field as the primary key. If you place a new row in the Orders table of each replica, each new row will have the same value for its primary key field, and the second database to submit its changes to the master database will generate a primary key violation. To decrease the chances of such violations occurring, Jet automatically changes auto-incrementing fields to use random numbers instead of linear sequences.