Split DB slow in development mode

[RenaG]

I am using Access 2007.

I have noticed that since splitting my db, it is extremely slow in the development mode. Both FE and BE live on a network and in the same folder (for development). The DB has always been on a network drive, so that didn't change after the split. This DB consists of a main form that has many tabs. On some of the tabs there are many subforms. When I click on a subform or a tab or a field, I have to wait a long time before the object I clicked on has the focus. This is really annoying (especially when I click on the wrong object ) and takes me forever to get anything done. Do you have any suggestions on why this happened after the split and what I can do to speed things up?

TIA!
~RLG

 

[JANR]

Do you have a persistent connection to your backend?
http://www.granite.ab.ca/access/performanceldblocking.htm

To speed up loadingtime for forms here are some tips (same site):
http://www.granite.ab.ca/access/performanceforms.htm


JR

 

[vbaInet]

Both FE and BE live on a network and in the same folder (for development).

Don't develop in your production stream.

 

[davea300]

Don't develop in your production stream.

Good advice, develop on an offline copy. I take it your FE just has forms in it?

Apart from that though, if you are finding particular forms or sub forms slow to make changes to try compiling them. This can make a big difference if you delete redundant code from any forms.

You may find there will be a lot of code hovering around if you have made a lot of changes to each form.

It has certainly worked for me in the past.

 

[vbaInet]

And here's a good comprehensive article on performance tips:

http://www.granite.ab.ca/access/performancefaq.htm

 

[RenaG]

Thank you for all your replies!!

I didn't mean to imply that I am developing in my production db. The production version lives on a shared drive that the users have access to. Everything at this company is done on network drives. We can't even get to our C: . So I set up the users db to have the BE live in the root folder. Then I created subfolders for each user that has a copy of the FE. I don't touch these files except to install updates. On my personal network drive I have the BE and FE in the same folder. I originally developed the db as a single unit (forms, reports, tables, etc all together) but after doing some research split it into a BE and FE then deployed these to the users. So now, I am making changes in a split db (in my development environment). Now is when I am noticing the slowness in working on the forms. I have the form opened in Design View and click on a tab or subform or field and any click I make takes a really long time to bring that object into focus. It is in this development environment that I need help in speeding things up.

Will compiling the code make it faster to modify forms? There isn't a lot of code on my forms but I can certainly try compiling them. Can I just go into the Microsoft Visual Basic window, Click Debug then Compile? And will that compile everything? Do comments make any difference? I tend to just comment out code rather than delete it just in case I need it later. Does that make any difference?

What about the Compact and Repair? I was reading about that too and wondered if it is something I should be doing when I deploy updates. Thoughts?

Thank you for the articles. They were very informative!

I look forward to your replies.

~RLG

 

[vbaInet]

Is your Back End (i.e. I mean the back end of the db ) the same name as your production BE?

Are you running on a super fast LAN?

Compact & Repair, Debug > Compile are good tools for helping to increase db performance.

 

[RenaG]

Yes, the production and development db (both BE and FE) have the same name. They are on different network drives.

I don't know if I am "running on a super fast LAN". But before I split the db, I didn't notice this lag time when clicking on forms, fields, etc.

I assume that the compile is done while in the FE and the compact and repair is done while in the BE. Correct?

I feel like I am asking about performance while developing in the FE and it seems like the replys are addressing performance in using the db. Am I asking the question badly or am I not understanding the replies. Sorry, I don't mean to be dense and maybe it is my novice Access experience that is getting in my way. Is the issue I need help with being addressed and I am just not getting it ?

I am very thankful for the time you have spent sending replies. Truly, I am!

~RLG

 

[RenaG]

If what I am experiencing is unusual, then I may have found the issue. When I compiled the db it came up with an error. It's on an update form that is attached to a combo box (in the List Items Edit Form). I haven't figured out yet why it has a problem (it is set up the same way another update form on the same form is) but maybe when I do, it will speed things up. Stay tuned. I'll let you know the results when I get this figured out.

Hopin' and wishin',
~RLG

 

[spikepl]

On my personal network drive I have the BE and FE in the same folder.

As long as both FE and BE are on the network you get one hell of an overhead, and related "slowness". You (and each user) needs to have the FE locally! Otherwise the data gets dragged back and forth multiple times, since the FE executes onb your local machine.

 

[RenaG]

Well, I figured out what was keeping it from compiling and got it fixed but that didn't solve the slowness problem.

spikepl - I don't have any control over that issue. I realize that it would be better to have the FE on the local machine but they have the local hard drive locked down and that's that. The users haven't complained about it being slow, thankfully. It's just me. Are you saying that even in development, the response will be slow because I have the BE and FE on a network drive? If that is the case, can I put it back together for development and then split before installing updates to the production FE?

~RLG

 

[spikepl]

local hard drive locked down and that's that.

Ok - if that is the case then Access would be best on a terminal server or Citrix, and then the IT-crowd would probably be happy. It really really makes no sense to access FEs on a network drive (personal or not).

If that is the case, can I put it back together for development and then split before installing updates to the production FE?

Does your business have an Access-police? Otherwise, would could stop you? But there is little point in reassembling it. You could still have an FE and BE, just on you local drive, while developing.

 

[RenaG]

The only thing that could stop me from putting it back together again is knowing how to do it . Once it's split, how to you put it back together again?

~RLG

 

[spikepl]

Why bother? What would be the point?

 

[RenaG]

It didn't seem to be so slow to work in when updating forms and such when it was together. Now that I have split it, updating forms in design view is painfully slow. I am just looking for a fix to that. I thought that from reading your post that it might help to put it back together. But I just re-read it and evidently I quit reading after the question "who could stop you?"

~RLG

 

[gemma-the-husky]

the slowness may occur if you are sharing the backend data, and you don't maintain a persistent connection.

ie a permanently open form or recordset. if you don't do this, then each time you access the dbs, windows has to negotiate the connection for you