Loading Access DB over slow WAN 1

[Prathmesh]

Hi All.
I recently deployed a BE-FE version of a new Access Database in my firm, after restructuring the old one which was just a standalone mdb file. We have a normal 100Mbps LAN. Its recently deployed and so far it seems to work fine. However, now my firm wants me to try out and deploy the same thing over the WAN (its in form of VPN) that they have got over their 1 other remote office. The data transfer rate is 1.5Mbps/512kbps (download, upload on the remote office side). I tried to deploy the FE at the remote office keeping the backend at the main office, but that's VERY SLOW. It takes minutes to open up forms and reports. Also, I read somewhere that in order to process queries, Access sends all the data accross to the client machine, instead of just the required data, and all the data is processed on the client side. (Well, that's the whole point of having a FE-BE structure to decrease the load on server, and make use of client machine resources, isnt it?) Now I am in a bit of a fix under two conflicting scenarios. For Local office I need a FE on client machine that does all the processing and for the remote office I need something that will do the processing on server side and just send the required data across. What do I do now? I have thoguht of some ideas which I am highlighting and would appreciate some advice on those.

1. Create a similar FE and BE on the remote office and then link the two BEs. But here also I guess the same problem will arise of ALL the data transferring back and forth during each query run or loading forms and reports.

2. I dont want to use MSSQL since it is very expensive and MSDE because it is limited to 5 users connecting at a time simultaneously. (An off-the-track question for this section, but supposedly in line with the solution) Would mySQL suffice?

3. If I use a combo of mySql and Access and use pass through queries, will it solve the problem? I am asking this because Access mgiht not be able to handle the running of multiple queries simultaneously as it will seriously mar the performance of database.

3. Is there anything else I should be looking at or I have missed? I would really welcome if there are any more suggestions, which I am sure, will be.

P.S. All the tables are linked and normalized. However, the company requires a lot of forms and rpeorts and hence the Front end is almost 250 MB in size. (This is just for reference).

Sorry to post such a long question, but did not want to miss out on any facts. Thanks to all in advance for taking time to answer my queries.

Regards,
Prathmesh

 

[willir]

Prathmesh

My experience of using Access over a WAN still leave me with bad memories.

The Rx Medicine was to implement "Replication".

Replication is a neat idea - distributed database, but it is definitely an advanced topic....
- Sequential autonumber for the primary key will not work. Either use randomized autonumber or GUID.
- If you have a high transaction table(s), you may have still problems.

When you replicate a database...
- Make a backup before you start
- You will have a Master replica - protect this copy. This is the "Big Daddy"!
- Slave or secondary databases will be deployed to sites across the WAN.

Replication is a big step. There is some risk. Make sure you have backups and a contingency plan.

The front end can be deployed to each site - performance will improve immensely.

...Alternative. ADP. You get away from the risk of replication, and from what I have seen, performance is acceptable.

Richard

 

[faeryfyrre]

Wow, that's an absolutely freaking massive file size for just a frontend. This may seem like an obvious question but i have to ask, have you tried importing ALL the frontend objects into a Blank database? This should dramatically reduce the size of the FrontEnd. I suggest this only because any computer would take a performance hit if it has a 250MB file loaded into memory. This may not help with the speed of the application over the WAN, but it couldn't hurt to try could it?

To answer one of your questions, yes, mySQL would certainly suffice as an alternative to MS SQL Server, unless you require stored procedures. The performance benefit you would receive over Access as the BE would definitely be noticable.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[Prathmesh]

Hi Faer & Willir.
Thanks for the advice. Faer, I tried doing what you suggested to me regarding importing objects in blank database, but there seems to be no change in FE file size. I copied over all the linked tables, forms, reports, queries and macros. That's all the stuff I am supposed to copy over, right? Suggest me if I am missing something or not doing it right.

Willir, I like the idea of replication, but am not sure how to go about it. Can you provide me with more guidance on how to go for the same or refer any URL which has info for the same? I tried searching MS Access help, but could not find anything proper. On Internet also, I could not hit upon something that explained properly about replication. Also, for ADP (I dont have much knowledge about ADP) I will need a backend server, right? I mentioned that I dont want to go for MSSQL or MSDE, however am ready to go with mySql. Would that go with ADP? Also how would a front end .mde file differ from an Access project .adp file?

Many thanks to all for their replies in advance.

Regards.

 

[faeryfyrre]

When you say that you "copied" over all objects, what do you mean? That you "Copy & Pasted" from old into new database? Or that you used the import function of Access? You may not see a change in file size if you copy & paste.
I assume that you compact the database regularly.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[nat1967]

I agree with Alec. 250 meg is VERY large for a front end. Here is a great resource for speeding up Access and some general advice on maintainence:

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

It sounds as if your WAN is over a DSL connection. I have VPN setup so I can work from home sometimes and my database is very slow also. Access likes to bring all the records with it when you open a link form or when executing non-pass through queries. If you have a lot of records, this is a reason why it is so slow. It's not just a d/l but a two way communication between the FE/BE.

Checkout the link I referenced. I have implemented most all of the suggestions and have seen TREMENDOUS improvements.

Good Luck.

Have A Great Day!!!,

Nathan
Project Manager III

http://www.skylineprints.com

 

[LittleSmudge]

I'm doing a lot of work on MySQL Back End and Access 2k Front End at the moment.
The reason the client when to this solution is because of the same senario as you describe.
A database worked fine over the LAN yet all but stopped when it was used over the VPN.

We now have 6 remote offices using Access Front Ends to a MySQL BE.

Access is a File-Server database and splitting the FE-BE has nothing to do with sharing workload in a File-Server dbs.

With care you can set up MySQL BE to Access FE to work in a Client-Server structure which will put a chunk of the workload back into the server AND limit the VPN traffic to the required data.


As for your 250MByte Front End - When was the last time you UNcompiled the code ?

It is a little known fact that an Uncompile, Compact, Recompile sequence can make Hugew savings in db size.

( So can removing large image files out of Form/Report designs and linking to them rather than embedding them


'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[psemianonymous]

The link above is a page on Tony Toews' (Access MVP) website. It's excellent; check it out.

No one has yet mentioned Terminal Server/Citrix connections. I've used it and it works as fast as a local (fast LAN) connection because it is in fact running inside the LAN. If it is already set up by your IT/IS department, then use it, because the only problem will be configuration headaches. There will be zero programming fixes required to get it working, which is a huge bonus if you have a large amount of code/forms written.

Then again, you could switch backends to a server-based database like MySQL or whatever, but I'll let everyone else tell you the relative painfulness of such a move.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

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

 

[LittleSmudge]

Yes foolio12 - we're actually running Term Services here as a stopgap measure for users to run some databases until they are converted to MySQL BEs.

However, licencing costs and problems with a large number of users running concurrently are a big issue.

It's not a way I'd go by choice.







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Prathmesh]

Many thanks to all for taking time to answer my query and providing valuable guidance. Much appreciated.

Fair, sorry, used the wrong word "copied". I did import all the objects in another blank database, but size turned out to be the same. I would appreciate any more suggestions here. I was actually worrried about the file size and tried to implement what u said, but dont know why size turns out to be same.

Nathan, I will surely follow the suggestions. I would really like to optimize as much performance as I can.

Foolio, I am also looking into that terminal services/citrix thing. Its actually quite good since at both my remote offices, only 1 person each uses the DB so it is actually a feasible solution.

LittleSmudge, I am not sure what u mean by "UNcompile"? Compile and Compact is fine, but it would be great if you could please provide me more info on that. Also, what is the difference between an ADP (Access Data Project) and an Access FE? Can they be used interchageably?

Thank to all in advance for their replies.

 

[faeryfyrre]

Prathmesh,
If you actually imported all the objects rather than copied them and still find no change in file size then the suggestion by LittleSmudge to uncompile compact recompile, and check how many embedded images are utilized in the design of forms are about your only options.

uncompile is a command-line argument you can use when starting up Access.

From memory, If you create a new shortcut on your desktop and edit the target line so that it reads:
"Path to MS Access program" "Path to Access database" /uncompile

Note that you only have to enclose both paths in quotes if you have spaces in your file or folder names.

Save the changes, then rename the shortcut to [Uncompile "My Database"] or something similar.

The other issue you need to look at is the number of embedded images used in the database, if you link them rather than embed, the size will reduce.


Just out of curiosity, How many objects are there in the database?

Also, What version of Access are you using. I don't think 97 has the /uncompile option.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[Prathmesh]

Hi Faer.
Here is my object count.

Forms: 141
Queries: 389
Reports: 105
Macros: 118

I am using Access 2002 Version. And I also tried that uncompile suggestion. However, Access comes up with an error saying "The command line option you used to start contains an option that MS Access does not recognize". After the error, it starts the database, though. I tried to change the slash ("/" to "\"), but same thing. Then I interchanged the file names. I first put in the name of database and then the path name where MS Access resides on my PC and then the "/uncompile" switch. This time it started without an error, but nothing came up and the database started smoothly. So am I doing it correctly? What am I supposed to look for or what will happen then?
I appreciate your time and effort.

Regards.

 

[PHV]

Have you tried /decompile ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[faeryfyrre]

Ah,
Thankyou PHV

I was going by memory and mistakenly used uncompile instead of the correct decompile

Prathmesh, If you follow my above instructions again but substitute in /decompile you should be fine.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[Prathmesh]

I did as instructed with "/decompile" option. It worked this time, but did not give any message of any sort. Once database opened up that way, I compacted it and then again Compiled it by going in the VB editor and choosing
"Debug"-->"Compile Filename.mdb"
But all the changes it gives me is around 4 to 5 MB of reduction, which I used to get previsouly everytime I compacted the database. So there is really as such no visible change. Am I doing it right folks or am still on the wrong path?

Regards.

 

[faeryfyrre]

Well, If the /decompile didn't produce the results we were after, all i can suggest is to look into the Embedded Images issue.


How many of those Forms, Reports and Queries etc are actually used? Are any of them legacy objects from prior design changes? You could go through the laborous task of seperating the wheat from the chaff.


Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[LittleSmudge]

Forms: 141
Queries: 389
Reports: 105
Macros: 118


Well in anything more advanced than Access v1 ( Best of breed in 1992 ! ) thats 118 more macros than you should have in any modern database.

And I'm amazed at the excessive number of Queries.
( Although queries themselves to not take up a lot of space I'd be amazed if anything more than about 10% of them were actually necessary. )


However, thay are organisational issues and won't be the cause of your size problem directly.

Are you sure that when you split the database BE-FE - you have got rid of all of the old tables out of the FE?
There aren't any tables directly in the FE anymore are there ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Prathmesh]

Hi LittleSmudge,
Yes, there are no other tables directly in the FE anymore. There is just 1 table that holds the version information. I have done that for convinience purpose. Each time the client loads up the FE, it will check whether the client FE version is similar to the FE version on server. IF not, the new FE will be copied over to the client side. I have done this to automate the updation of changes each time the client starts the FE. In this way, I dont have to go around telling the users, each time I make a change, to update the changes. As soon as they restart the application, changes are automatically updated. Apart from that table which contains version information and consists of only 3 fields, there is no other table except linked tables. I am also planning to shift from macros to VBA code soon. I wanted to get the application up and running fast, hence used macros. Now I am planning to fine tune the performance and conversion from macros to VBA code is one of the "To-Do" on my list.

 

[Prathmesh]

Hi FaeryFyrre and all,
I am shocked. I followed Faer's suggestion of linking the images rather than embedding them and then compacted the database. To my utter surprise and joy, the database FE went from 260 MB to 17.4MB. That's amazing!!! Thanks a ton guys. It really helped me.

Regards.

 

[LittleSmudge]

NOW remember - that if you ship the database to other servers or to other user's machines then you'll need to make the image files locally available too.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.