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

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.

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.

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

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]

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

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.

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.

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.

Bob,

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

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

Yes, it will.

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...

Yes, it will.

It WORKS! Whoohoo! Thanks Bob!

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.

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.

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.

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.

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.

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

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

Well, first of all, it is a good idea to split it BEFORE you add data. It should only be done once.

Second, how did you split it?

Well, first of all, it is a good idea to split it BEFORE you add data. It should only be done once.

Second, how did you split it?

I just went to Database Tools menu and did a split. I closed all forms and tables. After the split, I looked at the FE db and most of the tables are LINKED to the BE. But the file size is IDENTICAL...

Strnage..

Even after running Compact and Repair on both?

Even after running Compact and Repair on both?

I take it back. The BE is SMALLER than the FE. HOWEVER, the FE is the SAME SIZE as the original unsplitted db file. Hmm... I thought after the split the FE should be smaller than the original size..

Bob et al,

The source of the complexity is this. I might as well lay it out here since this is afterall the Theory and practice of db design forum. So, here it goes:

1) I got Fieldname that needs to have heterogenous data values. For example, Field1 is usually
numeric("Standard" as Access would call it) but every once in a while it needs to show "na" or some other alphanumeric designations. Now, we are all know that's it's NOT possible to mix data types in Access for a particular field.

2) My workaround is to create 2 tables for each Field: Table1 and Table1NA. Now, you can see why it runs into the hundreds of tables quickly(hehe). BTW, the Key in both tables is months (mm/1/yyyy). Table1 has all the field values that are numeric. Table1NA has the "na" string in the months where the Field1 is supposed to be "na" and if a particular month has values then there will no "na" string for that month. Table1, on the other hand, has all the Field1 values but for month(s) where it doesn't have a value that PARTICULAR MONTH will NOT be present in Table1. You'll see why in a minute.

So, to do view on various Fields, I ended up create hundreds of these slim table pairs(Table1,Table1NA, Table2,Table2NA,..,TableKth, TableKthNA). Then I do a supernasty SQL query to combine these into one view. I know it's a complex design. But it was the only way I could display both "na" and the Field values.

And this is probably where all the overhead and bloating are happening. Any ideas?

thanks!

flyinghippo99

The real workaround is to just use a text field for both.

The real workaround is to just use a text field for both.

Yea, I thought about that from Day 1. HOWEVER, the Data Entry forms, the user
reqs is that they can enter the data in their original format($, %, text, decimals, integers,etc.) Ack. I guess I could let them enter into a local table with the right data types then do a conversion into text to store in the backend.

I mentally voted against that thinking it will be too wieldy then ended up with this supercomplex nbasty sql and ADO/DAO thing. LOL.

So, what do you think of my original solution of a temp storage for each Fieldname1 to N with the right data type and then do a the conversion to string to put in one big table?

BUT, the problem with converting everything to string in the big backend table is that then user can't do math operations on the historical data and do derived fields (F1/F2 + F3 etc.) If I do then I have to an on the fly conversion from string back to true data types? If that's the case, then I'm not sure which one is the uglier solution...

flyinghippo99

The BEST way is to let numbers only be entered and NOT allow N/A or stuff for certain things. My question is what is the data and why can't it be entered as it should be? Where is it coming from? The way you have it now is not going to make getting the data easy anyway. And storing it all as text isn't going to be a good answer either.

If meaningful data is to be reported on, then it has to come in as meaningful data. So why can't that be entered that way?

The BEST way is to let numbers only be entered and NOT allow N/A or stuff for certain things. My question is what is the data and why can't it be entered as it should be? Where is it coming from? The way you have it now is not going to make getting the data easy anyway. And storing it all as text isn't going to be a good answer either.

If meaningful data is to be reported on, then it has to come in as meaningful data. So why can't that be entered that way?

This is the business reqs. :) They want to be able to view a biz data series(let's called
it F1 for fieldname1) that looks like this:

1/1/2008 534530
2/1/2008 na
3/1/2008 na
4/1/2008 4353409
5/1/2008 nr
6/1/2008 34590
...

you get the idea. That's why it's so complex...

Bob - Would it help if I use multi-value fields? Also, I was thinking using class objs in Access, but not sure if it will really help in this case...?

What do NA; NR etc. actually mean?

Could they be a 'replacement' for Null or Zero?

Are the figures entered always within a certain range (i.e. are they always positive for example?) If that were so you could perhaps use certain numbers (negatives maybe) to designate NA; NR etc.. and convert their display in queries; reports etc.

What do NA; NR etc. actually mean?

Could they be a 'replacement' for Null or Zero?

Are the figures entered always within a certain range (i.e. are they always positive for example?) If that were so you could perhaps use certain numbers (negatives maybe) to designate NA; NR etc.. and convert their display in queries; reports etc.

Steve - Thanks a great point. It's all positive. So, I can map na = -1 and so forth. Is there an easy way for this in SQL? Or would I have to do it through ADO/DAO and stepping through the recordset looking for -1,-2 etc and replacing them with the strings "na" etc. just for the viewing/exporting part?

thnx

flyinghippo99

Steve - Thanks a great point. It's all positive. So, I can map na = -1 and so forth. Is there an easy way for this in SQL? Or would I have to do it through ADO/DAO and stepping through the recordset looking for -1,-2 etc and replacing them with the strings "na" etc. just for the viewing/exporting part?

thnx

flyinghippo99

Bob, Steve, et al:

I was celebrating the last few days with this new data representation since I can eliminate hundreds of tables and collapsed them into 1 big table with -1 and -2.

HOWEVER, I just remember why I had to my complex methodology. It has to do with Access 2007 limitations of 255 columns per table. I mean the -1/-2 will definitely reduce some of the complexity, but not all of it. But perhaps a bit of db design thinking can get me out of this mess...

So, here's the deal. I have hundreds if not potentially thousands of business FieldNames. My original methodology is breaking each into individual tables and combining them with a supernasty sql statement. That can be potentially eliminated
with the -1 and -2 representation.

However, I'm running into a problem. I can't put them all in one big table because of the 255 column limit in Access. So, the big table is supposed to look like this.

Index: Date
Field1
Field2
...
FieldN where N is arbitrarily BIG (bigger than 255)...

I even thought about breaking this big table into a few smaller ones by CATEGORY
but still 255 column size is NOT enough! And if I just arbitrarily break the big table into smaller tables to accommodate the many fields, then doing queries will be hard since I wouldn't know a particular FieldName is in what smaller tables unless I introduce even more machinery of an intermediate table, which might not be any
better or more efficient than my current messy but fully functional design...

Am I missing something with db design here? Or is time to consider upgrading to
SQL Server? I'm seriously considering telling my boss to upgrade to SS.

thanks!

flyinghippo99

Since this is a db design forum, perhaps, the better question is what is the best/optimal table design for the problem I described above. Instead of trying to fix the symptoms, let's get to the source. I know needing more than 255 columns seem like something is wrong with the normalization process. But that's the best I could do else I'll end up with thousands of tables. I'm not sure which one is more evil.

So, here's the detail:

1) I have close to 500 business metrics(FieldName1...FieldNameN)
2) The data is collected monthly
3) That's why I want to create one big table with Dates, Field1,..., FieldN
4) Then I have a MasterAttributeTable which has the KeyNum(primary key/index) with a list of attributes. KeyNum, Attrib1, Attrib2, ... , AttribN
5) Then I have another series of tables(current design) or one BIG table(haven't decided on this design approach yet). The table name(s) is the KeyNum and it has the following FieldNames: Dates,KeyNumFieldA,KeyNumFieldB,KeyNumFieldC,KeyNu mFieldD, which are different than the MasterAttributeTable

what's the best normalization approach? Or am I stuck with hundreds of tables approach?
================================================== ======
Also, I've been thinking if I were to use Access as FE and SqlServer2008 as backend, and a user select a bunch of items in a listbox, then how can I send it to a stored proc in SS since SS does not accept array of values? Or is there some other simpler way to do this?
================================================== ======

thnx

Hi All,

I figured out a significantly better data schema already. It reduced the number of tables from hundreds to just a few. And the SQL is simpler for update, insert, delete. Ironically, the view(select) is still complicated. It's OK. I can live with that.

This forum is a great resource thanks to all the users contributions.

flyinghippo99