Performance issues occuring in Access Split Database

[sarsukanth]

Actually i have one Excel file and one Access DB with macros in both files.
Here the Excel macros is calling the Access macros at the runtime
As the excel file users are more than one, i split the DB and planned to share the Excel file and front end DB to all users
here its working but a performance issue occurring, each user is taking 60 minutes to complete the excel macros to run(including access macros)
so please help us in getting best performance here
we heartfully appreciate the solutions
advance thank you...

Best regards,
Raghukanth

[jimatqsi]

What version of Office are you using?
How long does the process take without the split?
Can you determine which part(s) of the macros are slower than before?
Does it behave the same for every user? And for the developer also?

I have seen a problem where if the developer is running in 2007 and the users are running in 2003, the loading of the Access file takes a very long time, one time only. After that, for each user that already ran the program once the problem is no longer present. But that was with an Access front-end and a SQL back-end, so your case could possibly continue after subsequent loads of the program.

Your problem could be Access related or network related. Give us some more information.

Jim

PS: Be sure each user has their own copy of the front-end on their local machine. Don't let them all make a shortcut to the same copy of the front-end.

[sarsukanth]

Hi Jim,

thank you for your reply, and as you asked
all users are using MS-Office 2010
and i did the split of database and i am using MS-Office 2007
and before the split it was working fine without any delay
and there is no slow in any macro(Excel or Access)
and we place the back end database in one server
and placed Excel file and database front end file in each user local systems
so now we tested as two users using 2010 versions run the excel macros at a time (as we need multiply users use the database at a time)
but its taking 60 mins each to complete
so please help us
and please let us know if any queries

thank you...
Raghukanth

[Rabbit]

We need to know what the macros do. You should also be aware that Access is not the greatest when it comes to multiple users, it's very slow compared to other backends.

[zmbd]

Wow, what a nightmare setup!

Let's get terms correct first:

• Excel 2003 and newer, macros are VBA code.
• In Access, there is a Macro language AND a VBA language. The two terms are NOT interchangeable within Access. I know, silly of MS to do this; however, it's history :)

As for Excel or Access... why are you using both programs to manipulate things? A brief step by step of the workflow will be required. It shouldn't take an hour to run VBA code unless you are having a lot of conflicts in how the data is being accessed (no pun) by both programs.

Networked files are also occasionally a thorn in the side.

You also need to move everything to ONE version of Office. There have been some subtle changes between 2007 and 2010.

[sarsukanth]

Hi All,

thank you for reply, and the macros will do check the input data is available or not first, then copy and paste the data each cell wise from the Excel sheets and create a flat excel work sheet. then from this flat file the data will go in to the access tables (holding tables).

in the access macros the data from holding tables will go into processing tables then back to excel sheet as out put.

in between some calculations and checking will happen.

and this is already developed by one team and i am doing the supporting part if any changes required. so we have to use this , so please help how to increase the performance of the split database.

thank you
Raghukanth

[Rabbit]

Please be specific. We need to know exactly what each macro/code does. Step by step, line by line.