Front End VERY slow (1 Viewer)

[christakis]

I created a FE/BE database, the FE runs very fast at out local office but extremely slow from our other office. Its around 300 miles away. I don't think its a bandwidth problem as when we send large files between the two sites its rocket fast. Note that the network between sites is an intranet not an internet.

I've looked this up before posting. People suggest to have a ghost table to keep the FE/BE connection live. My main menu queries the BE to populate a listbox so I suspect the connection is active. It appears that the less data the FE retrieves the slower it is. For example, a simple query to populate a combobox takes around 15 sec. A query to filter 100s or records takes around 5 seconds to complete. The lag is unacceptable. Waiting for 30 sec for a form to open is a nono.

I am even wondering if the intranet traffics network. What protocol does the database use to communicate FE with BE? Is there a chance the firewall has something to do with it?

It is so bad I am thinking of having two back ends (one at each site) and synchronizing them. Do you have any other suggestions?

 

[speakers_86]

It is so bad I am thinking of having two back ends (one at each site) and synchronizing them. Do you have any other suggestions?

I believe that is what the more experienced users will recommend.

 

[christakis]

I believe that is what the more experienced users will recommend.

Is there a simple code I can plug in to allow two way sync? I would program it on my own but im too busy with other things now. A suggestion was made to only edit/add records on one size and synchronize the other by replacing it. Not ideal but not as bad as it sounds either, data are not modified or added often the database is more of an inventory search database.

 

[speakers_86]

Is there a simple code I can plug in to allow two way sync?

Not that I know of. I would say your best bet would be to move your backend to a mysql database. Your front end can connect via ODBC (?). From what I understand, it is not that difficult, but I have never done it. Terminology of field types is different with mysql, and I haven't got the experience with it to say what is what.

 

[irish634]

Will replication work in this instance?

 

[christakis]

I believe we have a MS SQL Server but I doubt its worth moving to SQL for such a small database

 

[christakis]

Will replication work in this instance?

Irish, sorry I didn't see you message earlier. I haven't come across replication before. It looks promising though. Any of the big guns have an opinion on this?

 

[speakers_86]

What is the format of your database? Replication does not work on .accdb files, which is the format of A2007.

 

[christakis]

What is the format of your database? Replication does not work on .accdb files, which is the format of A2007.

.mdb back end (2003 version)... Another thing is that the briefcase method wouldn't work on servers would it? I didn't quite understand the other method. I need to investigate this further.

 

[boblarson]

I think you may want to read this. And David Fenton should be by soon to explain about replication since that is a keyword he searches for (he's one of the more knowledgeable in the forum about replication).

I would probably say that you will want to use Terminal Services or Citrix for use with users that aren't on site.

 

[christakis]

Bob, thanx again for your input. I will investigate this office when I get back to the office. I noticed that SharePoint can be used for Access 2007. I know for sure we have SharePoint but we work with Access 2003. Do you by any chance know if Access 2003 can be used with SharePoint?

 

[dfenton]

In a situation with two fixed offices, Windows Terminal Server is by far the easiest way to deploy an Access app.

Replication over a WAN requires Internet or indirect replication, which is complex to implement and requires lots of care and feeding to keep running reliably. It works very well, but is not easy. This is why I recommend Terminal Server, because it's so much easier.

And don't be tempted by trying to use DIRECT replication across a WAN -- this is a recipe for disaster, as the same problem that causes your app to be slow will cause the direct synch to be slow and that endangers your data file (direct replication opens the remote replica across the wire).

Redeveloping for a SQL Server back end is an option, but I wouldn't recommend it -- it's simply far easier to host the app on Terminal Server.

(noticing a pattern here?)

Sharepoint is an option, but is very limited in what it supports with A2003. A2007 integrates far better with it, but the real innovation with Sharepoint is still coming, in A2010 and a new version of Sharepoint server that provides a set of very powerful Access services for publishing an Access app. It looks like it might be a very good solution to your problem, but it's still only theoretical, and what I've heard that it can do may actually be impractical in reality.

 

[christakis]

In a situation with two fixed offices, Windows Terminal Server is by far the easiest way to deploy an Access app.

Replication over a WAN requires Internet or indirect replication, which is complex to implement and requires lots of care and feeding to keep running reliably. It works very well, but is not easy. This is why I recommend Terminal Server, because it's so much easier.

And don't be tempted by trying to use DIRECT replication across a WAN -- this is a recipe for disaster, as the same problem that causes your app to be slow will cause the direct synch to be slow and that endangers your data file (direct replication opens the remote replica across the wire).

Redeveloping for a SQL Server back end is an option, but I wouldn't recommend it -- it's simply far easier to host the app on Terminal Server.

(noticing a pattern here?)

Sharepoint is an option, but is very limited in what it supports with A2003. A2007 integrates far better with it, but the real innovation with Sharepoint is still coming, in A2010 and a new version of Sharepoint server that provides a set of very powerful Access services for publishing an Access app. It looks like it might be a very good solution to your problem, but it's still only theoretical, and what I've heard that it can do may actually be impractical in reality.

What exactly is a terminal server?

 

[boblarson]

What exactly is a terminal server?

Perhaps THIS will help explain it.

 

[christakis]

ahh... no that doesn't look like a good idea...

 

[dfenton]

[in re: Windows Terminal Server:]
ahh... no that doesn't look like a good idea...

Why not? Windows Terminal Server is included by default in all versions of Windows Server starting with Windows 2000, so if you have a Windows server, you need do nothing but buy the CALs for Terminal Server access and make sure you've got sufficient bandwidth and RAM for supporting the number of simultaneous users you're going to need. It's remarkably easy to set up a Terminal Server -- much easier than setting up Jet Replication, or reprogramming your app to use SQL Server.

It also puts all the administration in a central location to which you as developer have easy access (it's a Terminal Server, so you've got access to it remotely).

 

[christakis]

Why not? Windows Terminal Server is included by default in all versions of Windows Server starting with Windows 2000, so if you have a Windows server, you need do nothing but buy the CALs for Terminal Server access and make sure you've got sufficient bandwidth and RAM for supporting the number of simultaneous users you're going to need. It's remarkably easy to set up a Terminal Server -- much easier than setting up Jet Replication, or reprogramming your app to use SQL Server.

It also puts all the administration in a central location to which you as developer have easy access (it's a Terminal Server, so you've got access to it remotely).

We get charged per user to get access to the terminal server. I doubt it is cost effective for what the database is used for.

 

[RuralGuy]

Note that the OP stated "that the network between sites is an intranet not an internet." Do you know the speed of the LAN?

 

[christakis]

I am not sure of the speed. If I were to guess based on the file transfer results I would say around 1.5MB/s-2MB/s. What I meant by intranet vs internet, is that its a closed intranet access network. If you are not logged in the intranet you cannot access it. IT guys claim its a leased direct line but I am not sure about it at those speeds.

 

[RuralGuy]

Keep in mind that LANs run at usually 100Mb which could easily be 100 times faster than your intranet. I doubt you will achieve acceptable performance unless you take some of the other advice in this thread.