multiple users using same access database with excel macro?

[donalde]

I have excel marco, which does sql -queries to access base (in snapshot mode)

Problem is that I have to manipulte data in access: 1st I pick every item for certain product from databases into one auxialiary table (there is about 20 different products which all have their own table). 2nd, I start to move items from auxiliary to another auxiliary table, narrowing amount of items according users selections in excel userform. (selecting items in userform creates sql queries which move items) When user has gone trough multiple userforms selecting narrowing terms, items are listed into excel sheet.

Problem is that although I have locked my access database, I cannot mark it as read only (macro in excel stops working). When multiple users are using same excel macro same time, they sometimes get wrong listing, as moving itmes from auxiliary table to other is done byt multiple users.

So, is there way to allow multiple users to access same access database without them interrupting each other? Do I need to modify my excel VBA -macro, or do some tricks into access database. (which is saved in excel '97 format, since some of users are still using pretty old versions of windows)

 

[SydneyGeek]

It sounds like the read/write is your main problem. Do you really need to write to temp tables before downloading data?

Say you have tblMain, where everything lives: you select the ProductID and pass the value to strProduct (text), then filter by another variable (intYear), writing to different tables as you go. Maybe you could just pull directly from tblMain using SQL like:

sSQL = "SELECT * FROM tblMain WHERE ProductID = '" & strProduct & "' AND PurchaseYear = " & intYear

Then create a recordset direct on that SQL, and use CopyFromRecordset to return it to Excel. (If you need help on the specifics, post back).

In this case, you may get a warning about the database being locked, if multiple users try to pull data at the same time. However, once they successfully run the code they will get the data they wanted.

Denis

 

[donalde]

It sounds like the read/write is your main problem. Do you really need to write to temp tables before downloading data?

Thank you Denis from your post. Yes, I need to write temp tables before downloading data. And I think you are correct about that read / write prolem. Since products and items handled by this application are very complex, users sometime need to go asking help for selecting narrowing terms. If user really knows what he/she wants, selecting narrowed list is done in one minute. But if user needs to do narrowed selection for multiple products, it migh easily take 5 to 10 minutes. Nad if user is uncertain of selections, he/she must verify selections, and that takes time. Also, as it is tool for sales persons, their working is ofter interrupted by phone calls, thatswhy most common time is roughly 20 minutus. And on that amount time, it is quite possible that other users want to use same application and thus same access database. If database is locked from other users, they tend to complain that application doesnät work. And I need to have apllication whics is on network drive, applications in local drive are seldom updated by users.

 

[SydneyGeek]

Is there any chance that your users could use a pivot table instead?

You can base the pivot table's data source on the main Access table. Users then use the pivot table to filter the data they want. If they want to analyse that data they can copy it as values to another sheet.

Alternatively, it may be possible to set up a UserForm in Excel to preview the selected data in Access, so that users can refine their search before downloading. This is relativeley strigthforward to do in Access itself, although you need to write some dynamic SQL; I will need to check up on how to do it from Excel.

Denis

 

[donalde]

Maybe I should be more specific nature of my tables, and what is inside them:

there is 3 tables for each product: data-table, where all items are listed, in datatable items also have kind of 'configuration table' eg rules how item will be visible are marked with x. Take example item, it has its basic information, like name, technical information, identification code (8 digits), and into which larger system unit it belongs. Then there is these narrowing items, like voltage, item type (like fuse, fan, module, etc).

Maybe narrowing is wrong term in here.. but other 2 tables for one product are option base where these narrowing itmes are listed and code base where these larger systems are listed.

When user starts to use excel application, he 1st selects product from excel. With product seletion sql query is made to acess, query returns options for userform 1. When user has selected selects options from userform 1, sql -queries (multiple) are formed, which start for fill auxiliary tables. 1st auxiliary table has all produts items, then quories which are formed according selections from userform 1 copy items into 2nd auxiliary userfom. After all this has been done, userform 2 is shown to user, there is more selections which move items according selection from auxiliary table 2 to auxiliary table 3. Also, in some cases, items from auxiliary table 1 are copied directly into auxialy table 3. 3rd userform list larger systems (mentioned before in code base). Again sql -queries are formed, this time narrowing down selections accordin what kind of bigger systems user has.

After this narrowed data is stored into 4th auxiliary table, where in some cases prices are added according identification code. Way I see this, is that data in this 4th auxiliary table changes every time someone uses this tool, problem is that normal sql -queries only add data into 4th auxiliary table, but user can also start re-selecting product, in that case he uses command that clears all auxiliary tables.

As more and more people start to use this tool, I have to find out way to handle this problem. And I really don't know how to do this... Any suggestions?

I cannot store data into excel, since it would slow excel. Currently compressed and repaired database is well over 20M.

 

[SydneyGeek]

Here's one suggestion.

I guess there are times when writing to temp tables has its place, and this may be one of those. You have the main database on a shared drive. When the user creates the temp tables in that shared database. Is there any reason that they couldn't create the temp tables in a local database on their C: drive, and therefore isolate it from anyone else's selections?
That way, you get to keep the structure you already have (you will probably need to move some queries into the new databases) but if they get interrupted, the earlier work is not lost. Also, once the users have the data on their local drives, performance will improve -- and you won't have to compress the shared backend database so often, because you won't be creating and dumping thousands of records.

Denis

 

[donalde]

Here's one suggestion.

I guess there are times when writing to temp tables has its place, and this may be one of those. You have the main database on a shared drive. When the user creates the temp tables in that shared database. Is there any reason that they couldn't create the temp tables in a local database on their C: drive, and therefore isolate it from anyone else's selections?
That way, you get to keep the structure you already have (you will probably need to move some queries into the new databases) but if they get interrupted, the earlier work is not lost. Also, once the users have the data on their local drives, performance will improve -- and you won't have to compress the shared backend database so often, because you won't be creating and dumping thousands of records.

Denis

Thanks for excellent idea. This solves all my prolems. Now I don't have regulary compress database, and perfomance has increased significantly. Also, it was easy to change code, edit/replace was friend. Only change I need to do my macrocode was adding funtion which copies database into local disak, and that was easy. Many thanks! ray: