Question Duplicate backend on a different server.

[micko1]

Not sure where to go with this one. We have offices in other states that need to be able to open our data base. Due to the line speed it is extremely slow linking to our backend. Is it possible to have duplicate backends that can be merged, lets say over night or at the push of a button.
Hope this makes sense and any and all help would be appreciated.

 

[James Dudden]

It depends how big and complicated the database is. If you were talking about one table then running code to merge them both wouldn't be so bad but if you're talking about many tables with many relationships then I don't think it can be done. At least not without some major coding/headaches!

I would suggest using remote hosting, either an in-house server or rented. That way you can all share the same backend.

 

[micko1]

James
Thanks for the assistance. Yes the data base is rather large with many tables and relationships. I will talk to our IT people to see if they can apply your fix.
Thanks again for taking the time to help.
Mick

 

[Pat Hartman]

If the database is Jet/ACE, it would be better to convert it to SQL server. You will also have to do a little client/server optimization to take advantage of that. The point of switching to SQL Server is to reduce the network traffic. Access is a notorious network hog. However, if you modify your forms to be bound to queries with selection criteria that limit the rows selected from the server, you can reduce the network traffic dramatically.

A simple solution that requires no programming changes is to use Terminal Services (Remote Desktop)/Citrix/PCAnyware. All of these products allow remote users to connect to your server and instantiate a desktop to run your apps. It is generally as fast or faster than LAN access and much faster than WAN connection.

 

[micko1]

Pat
Thanks heaps for your input and suggestion. I know our IT guys use virtual machine for some users when they VPN in to the server. I will send your sugestion to them and see what they have to say. I created the database in access 2010 as I was sick of having to open multiple excel spreadsheets to access data. Every manager had their own spreadsheet and no data was shared. It is now accessible in one database and works fine at a local level but obvioulsy slow from other states. This needs to be fixed in some way to ensure continued ease of use and prevent managers from resorting to using their own spreadsheets again.
Sorry about raving on and thanks again for your assistance.
Mick

 

[Pat Hartman]

A Jet/ACE BE via a VPN will be slow. If you go the VPN route, you will probably need to convert to SQL Server and make some changes to your FE forms. If they are currently bound directly to tables or to queries that have no selection criteria, they are bringing back too much data and that is what will make the app slow over a VPN. Changing the forms to use queries with selection criteria that severly limits the rows returned will provide far better performance. No user needs to be presented with a recordset of a 100,000 rows or even 50. Have them identify what they want up front. It is far better to retrieve 100 records one at a time from a 10,000 row table than to bring down the whole table and let the user scroll or filter locally.