Access 2010 Macro Will Not Open Form

[swat]

I have a user that created a brand new Access 2010 database, no split...all components of tables, forms and queries are in the single db. It is a multi-user db residing on a corporate network drive with the possibility of 5 concurrent users.

I can create the following simple design that duplicates the problem. It contains a Main form that acts as a switchboard with buttons that open subsequent forms using the simple wizard to open a form in Normal mode. No VB, nothing fancy. The form is opens uses a query as a data source to access a single table. Sounds simple. Here's the problem:

When user #1 opens the database and clicks on one of the buttons, it opens the form correctly. When a subsequent user opens the database and clicks on the button to open the same form, they get a "You do not have exclusive access..." similar to what you would get if you tried to open a form in Design View when other users are in the database.

Here's what we've tried:
1. Users are associated to a security group that has full privileges to the folder. So they can Create, Modify, Delete the .accdb file.
2. If we downgrade the database and save it as an Access 2003 (.mdb) file, it works correctly with multiple users.
3. If I replace the button macro with VB code to perform an identical DoCmd.OpenForm...it works correctly with multiple users (thus proving security authorities are OK).

The Question:
Why do we get a violation when we try to use a Macro to open the form and, in the same database, in the same form I can create a button that opens the same form with Visual Basic and no problems?

The users will continue to use the Access 2003 version until the problem is resolved. I have confidence in this expert community that you will give me some insight and solutions!

Thanks, in advance, for your help!

 

[SOS]

I have a user that created a brand new Access 2010 database, no split...all components of tables, forms and queries are in the single db. It is a multi-user db residing on a corporate network drive with the possibility of 5 concurrent users.

Split the database to a backend and frontend and give each user their own copy of the frontend. That is the best way for multiple users and to avoid issues like this. This article would be a good one for you to read as it goes into why splitting is essential, not just a choice.

http://www.kallal.ca/Articles/split/index.htm

 

[swat]

Thanks SOS! I am very familiar with split databases and that is certainly an option. But I could just as easily Chang the buttons to open the forms with VB code, since that works as well. I'm not as interested in alternatives because I have several that will work. My interest here is learning why a simple macro doesn't work but the same VB code does.

 

[SOS]

Thanks SOS! I am very familiar with split databases and that is certainly an option. But I could just as easily Chang the buttons to open the forms with VB code, since that works as well. I'm not as interested in alternatives because I have several that will work. My interest here is learning why a simple macro doesn't work but the same VB code does.

I understand that. And I can't tell why it would do that for you like it is but I can tell you that you are playing with fire if you do not do like I said. Read the article I posted because there are very valid reasons why you need to do this. It has nothing to do with using a macro or vba. But if you do what you need to do with it, your problem will disappear - period.

 

[swat]

Thanks again, SOS. I did read your article as well as the additional article links and must admit that they are an interesting twist and explanation to why split databases are beneficial. I usually create my own databases in split format and may just recommend that they do the same.

However I would still welcome someone's ability to answer my simple question. No alternative implementations please. It's more of a curiosity to understand, rather than a resolution to the problem. I already have those.

I really do appreciate your willingness to help, SOS.