Concurrency Access 2007: Some Design & Implementation Questions (1 Viewer)

[flyinghippo99]

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

 

[Mr. B]

First, let me just say straight out, you should split your database. This is a best practice and will be benificial down the road.

Now to address the issue you asked about. I would suggest that all you have to do is to keep the ViewDataOrdered table as a native table in your frontend file. You can have linked tables (tables in the backend file) and native tables (tables that are in the front-end file. I know this is not a "normal" practice, but if you want each user to have their own version of the information in the "ViewDataOrdered" table, then having that table in the front-end and distributing a copy of the front-end file to each user is the way to avoid the problem you are having.

 

[boblarson]

So, what is the best way to solve this?

You have the ViewDataOrdered table exist in the FRONTEND and not the backend. And when we say it needs to be split, the backend (tables only) is on the server and then EACH USER needs to have a COPY of the frontend on THEIR MACHINE. Therein lies how you don't have a problem.

 

[flyinghippo99]

Mr B & Bob,

thank you! So you guys are saying the only way around this is to split the db to FE & BE? And then the FE would store the ViewDataOrdered table which is unique to each individual users selection.

OK. It makes sense if that's the only way to go. I understand how this would work if both FE & BE are Access 2007. IF the BE is SQL Server, then what? The ViewDataOrdered tbl would still sit in the Access FE? What about if people who write custom db app who use VB or C# to write their app? Where would their ViewDataOrdered tbl sit if not in the Sql Server backend. Just curious.

Thnx!

flyinghippo

 

[boblarson]

IF the BE is SQL Server, then what? The ViewDataOrdered tbl would still sit in the Access FE?

Yes, yes it would.

What about if people who write custom db app who use VB or C# to write their app? Where would their ViewDataOrdered tbl sit if not in the Sql Server backend.

For those types of apps, since they don't carry their own tables, so-to-speak, like Access does then they would have to find an alternative. One alternative is to use SQL Server for the table but include a username field which can be populated by the query (and it would have to be a delete and then append query and not a make table). All data would have to be filtered in a base query using the username so that they got only what was for them.

Or they could make use of SQL Server temp tables.

You could do the same with Access too but it will take a bit more work than just using a local table in the frontend.

Just curious.

Thnx!

flyinghippo[/QUOTE]

 

[flyinghippo99]

Yes, yes it would.

For those types of apps, since they don't carry their own tables, so-to-speak, like Access does then they would have to find an alternative. One alternative is to use SQL Server for the table but include a username field which can be populated by the query (and it would have to be a delete and then append query and not a make table). All data would have to be filtered in a base query using the username so that they got only what was for them.

Or they could make use of SQL Server temp tables.

You could do the same with Access too but it will take a bit more work than just using a local table in the frontend.

Just curious.

Thnx!

flyinghippo

[/QUOTE]

Bob,

thx for the clarifications. I just did the Database Splitter on Access 2007. But ironically, the BE is SMALLER than the FE? I don't think the auto-splitter process did it right.

So, what now? Does this mean I have manually delete and move the tables/queries I want from FE to BE and not rely on the Splitter?

flyinghippo

 

[boblarson]

Unless you have a ton of records, the frontend WILL be larger than the backend. The frontend, remember, has all of the forms, reports, queries (more objects and code take up more room).

You should not have to redo this.

 

[flyinghippo99]

Unless you have a ton of records, the frontend WILL be larger than the backend. The frontend, remember, has all of the forms, reports, queries (more objects and code take up more room).

You should not have to redo this.

OK. Makes sense. But it put my "ViewDataOrdered" table to the BE. I saw that in the Linked Table Manager. hehe. So, I should rewrite my code from the click event to make it write to the FE? And how easy it to move things back and forth if you don't agree with the Splitter autosplitting choices? And in the font end Module codes, does this mean I have to tell the path to the backend for my queries? Or the Linked Table Manager handle all of that? And I just write embedded SQL code as normal as before.

 

[boblarson]

OK. Makes sense. But it put my "ViewDataOrdered" table to the BE. I saw that in the Linked Table Manager. hehe.

Yes, because it doesn't know what you need to do. So you just delete the link to it in the frontend and then use IMPORT to import it into the frontend and then delete it from the backend.

And how easy it to move things back and forth if you don't agree with the Splitter autosplitting choices?

Just like I said in my previous sentence.

And in the font end Module codes,

You need to do nothing with this.

does this mean I have to tell the path to the backend for my queries?

Nope, because they are linked and have the same name as before everything will work the same as if it wasn't split.

And I just write embedded SQL code as normal as before.

That is correct.

 

[flyinghippo99]

Bob,

Cool! Let me try this. Now, does this mean this will solve my locking issue with ViewDataOrdered table?

 

[boblarson]

does this mean this will solve my locking issue with ViewDataOrdered table?

Yes, it will.

 

[flyinghippo99]

Yes, it will.

Cool. I'm resplitter to the network drive now...

Now, how would it work if I want SQL Server to be the BE? Would I have to manually import the tables into SS(or thru some scripting since there are hundreds of tables)? Then each embedded SQL code I have in my VBA modules I have to specify the connection string to SS and do a pass-thru SQL? Just curious in case we have to upgrade it to SS BE...

 

[flyinghippo99]

Yes, it will.

It WORKS! Whoohoo! Thanks Bob!

 

[SteveH2508]

Just as an addition to this - I would consider having this 'Temp' ViewDataOrdered table in a separate mdb file linked to the front-end but ion the local machine. This will help avoid database bloat.

 

[boblarson]

Just as an addition to this - I would consider having this 'Temp' ViewDataOrdered table in a separate mdb file linked to the front-end but ion the local machine. This will help avoid database bloat.

Yes, that is a good idea. It would avoid database bloat in the frontend but it isn't necessarily a problem. It all depends on how often you give the users a new frontend. Where I am currently working we use Citrix and whenever someone "opens" a database, they get a copy of the frontend copied into their directory and it opens for them. We never have frontend bloat that way because it is always a new frontend every time.

Now, you would need to keep an eye on the side db as well, just to make sure that it doesn't get too fat too fast. Which users might not be all that wise to. So, an even better way to use the side db is to create it dynamically when you need it and destroy it upon exit. But that is getting into more complex stuff than some people here are ready for.

 

[Mr. B]

If you set the temp table to be in a seperate database and have everyone linked to that dB you will find yourself in the same situation that you were in to start with.

As for "bloat", all you need to do is go to the settings for the current database and set the database to "compact" on close. That will take care of the bloat issue.

 

[boblarson]

As for "bloat", all you need to do is go to the settings for the current database and set the database to "compact" on close. That will take care of the bloat issue.

Take care of the bloat issue and potentially add other bad things into the mix. Running Compact and Repair too often can hose things. But I guess it doesn't matter in this case because it would be one person's frontend and they can just get issued a new copy from the master.

One issue with Compact on Close is if it takes too long it will potentially irritate your users.

 

[SteveH2508]

If you set the temp table to be in a seperate database and have everyone linked to that dB you will find yourself in the same situation that you were in to start with.

As for "bloat", all you need to do is go to the settings for the current database and set the database to "compact" on close. That will take care of the bloat issue.

No - each front end would have its own copy on the local machine so only one user is linked to it.

I use this technique for users to store their preferences/settings individually which can be set to 'survive' an update of the front end.

I deploy the front ends by means of a log-in script which checks the version and downloads the updated version and associated files if necessary to a folder on C:.

As Bob said setting Compact on Close is a bomb waiting to explode. With the user having their own copy of that 'temp' db it could be easily coded to compact it if and when necessary.

 

[flyinghippo99]

Hi Bob & Steve - Thanks for the suggestion. I was thinking of just deleting it each time it's run. So, I'm still keeping that table in the front-end. Is that advisable? Hmm...

On a separate note, I have an Access 2007 combo box question. So, I have this form and there's a combo box whose ROWSOURCE is a Select stmt from a table. However, the problem is that user can EDIT the Combo box drop down items! I even tried setting
"Allow Value List Edits" to No. And I can still edit that combo box list(?!), which is odd
considering this is what MSDN recommends. Coming from the horse's mouth here.

I tried Locked to Yes, but then it just locks everything. Any idea?
BTW, I just tried to set "Limit to List" to yes. Which sorta works in the sense that if I tried to edit it then it prompts an error message. But what I was really looking for is to make it uneditable period. Not even have the system flags an error mesg.

thnx,

flyinghippo

 

[flyinghippo99]

Unless you have a ton of records, the frontend WILL be larger than the backend. The frontend, remember, has all of the forms, reports, queries (more objects and code take up more room).

You should not have to redo this.

Bob,

It stopped working?!! I added more data to my original single unified db. Then I did the split the db into FE and BE again. This time the FE is the SAME size as the BE?!

Did I miss something?

Thanks,

flyinghippo99