flyinghippo99
Registered User.
- Local time
- Today, 13:41
- Joined
- Feb 28, 2011
- Messages
- 53
Hi All,
I just ran across this Access World Forums. Looks like a great resource. I have some questions about design & implementation to handle concurrency in Access 2007. And I did a search on the keyword concurrency in the forums.
From what I've read and other resources(books,etc.), it seems like the optimal way is to split the database into FE and BE(which could still be Access 2007 or potentially SQL Server).
But before we get to splitting the db up, I have an even more fundamental question. Though I've developed in both Access 2003 & 2007, concurrency is a relatively new issue for me. So, here goes the question(s).
1) Let's assume that the db is already split into FE and BE(it hasn't been split at the present moment). I say this to get to the heart of the matter that I'm stuck on conceptually before I go and do all these various things suggested.
2) I have a FE which has many forms and buttons. One of the button is a View Data button that calls a VBA proc(event click). This proc does a lot of complex, detailed data manipulations and at the end of this long sequence of steps it does a "SELECT INTO ViewDataOrdered" into this table named obviously ViewDataOrdered
3) User#1 logs into the db and goes to the FrmViewData. Clicks this button that invokes the VBA proc. Then the VBA proc goes thru these crazy steps then does a select into this ViewDataOrdered table. All is good. This works now perfectly on my single user db without splitting anything.
4) Now User#2 logs into the db and goes to the same FrmViewData. Everything is fine. Clicks the button that invokes the VBA proc. But then Access 2007 pukes and said about the table is locked by User1 on machine blah blah error message.
So, what is the best way to solve this?
I feel like there's something fundamental I'm missing here even before trying to split the db.
Here's what I've tried:
1) Instead of selecting into a table, I tried to do a DoCmd.RunSQL but apparently that only works for Action query and not Select queries..
2) I tried to save it as a query with a definite name. Then DoCmd.OpenQuery QueryName. Still doesn't work since User2 will click and run and the named query is already taken.
Then it dawn on me that the problem is have it assigned a name. Can't I just run the final step of this long sequence of data manipulation as a select? And view the select dynamically without a definite name for query or table? Yes, I tried creating a "view" too but it ran into the same problem of name collision.
Just a little background. The VBA event click proc has do nontrivial data operations
to get the data into a particular form and that's based on what the user selects in the frontend form. User2 most likely will not select the same options so the vba proc will go thru nontrivial data operations returning a different set of results. It's NOT as easy as having one big table and different users are selecting different columns or rows from this big table. There are many many tables with lots of intermediate steps to get to this final table(ViewData) which is then "select into" the ViewDataOrdered table(basically just does the final sort by).
This question is so fundamental that I wonder if splitting the db to FE and BE would even matter at this stage. I feel like I need to rewrite my VBA event click proc to accommodate this first BEFORE even contemplating splitting the db. But how?
How does one get rid of this name collision issue ?
thanks!
flyinghippo
I just ran across this Access World Forums. Looks like a great resource. I have some questions about design & implementation to handle concurrency in Access 2007. And I did a search on the keyword concurrency in the forums.
From what I've read and other resources(books,etc.), it seems like the optimal way is to split the database into FE and BE(which could still be Access 2007 or potentially SQL Server).
But before we get to splitting the db up, I have an even more fundamental question. Though I've developed in both Access 2003 & 2007, concurrency is a relatively new issue for me. So, here goes the question(s).
1) Let's assume that the db is already split into FE and BE(it hasn't been split at the present moment). I say this to get to the heart of the matter that I'm stuck on conceptually before I go and do all these various things suggested.
2) I have a FE which has many forms and buttons. One of the button is a View Data button that calls a VBA proc(event click). This proc does a lot of complex, detailed data manipulations and at the end of this long sequence of steps it does a "SELECT INTO ViewDataOrdered" into this table named obviously ViewDataOrdered
3) User#1 logs into the db and goes to the FrmViewData. Clicks this button that invokes the VBA proc. Then the VBA proc goes thru these crazy steps then does a select into this ViewDataOrdered table. All is good. This works now perfectly on my single user db without splitting anything.
4) Now User#2 logs into the db and goes to the same FrmViewData. Everything is fine. Clicks the button that invokes the VBA proc. But then Access 2007 pukes and said about the table is locked by User1 on machine blah blah error message.
So, what is the best way to solve this?
I feel like there's something fundamental I'm missing here even before trying to split the db.
Here's what I've tried:
1) Instead of selecting into a table, I tried to do a DoCmd.RunSQL but apparently that only works for Action query and not Select queries..
2) I tried to save it as a query with a definite name. Then DoCmd.OpenQuery QueryName. Still doesn't work since User2 will click and run and the named query is already taken.
Then it dawn on me that the problem is have it assigned a name. Can't I just run the final step of this long sequence of data manipulation as a select? And view the select dynamically without a definite name for query or table? Yes, I tried creating a "view" too but it ran into the same problem of name collision.
Just a little background. The VBA event click proc has do nontrivial data operations
to get the data into a particular form and that's based on what the user selects in the frontend form. User2 most likely will not select the same options so the vba proc will go thru nontrivial data operations returning a different set of results. It's NOT as easy as having one big table and different users are selecting different columns or rows from this big table. There are many many tables with lots of intermediate steps to get to this final table(ViewData) which is then "select into" the ViewDataOrdered table(basically just does the final sort by).
This question is so fundamental that I wonder if splitting the db to FE and BE would even matter at this stage. I feel like I need to rewrite my VBA event click proc to accommodate this first BEFORE even contemplating splitting the db. But how?
How does one get rid of this name collision issue ?
thanks!
flyinghippo