Re-design a wellworking data base

[petko]

Dear Fellows,



I haven’t visited the forum for a while as I’ve been enjoying the fruits of a 15 years long development. The system, worked out for our company, serves comprehensively from purchase to invoicing, or from labor date to car management. Tables are located on the server, front ends use them as linked tables.

However we hav purchased a similar size company recently at a different location (like 100 km away) and we have to involve their operation into our system. And this is the reason why I’m back again to ask your suggestion: I simply can’t serve the second site from the same database due to the Internet’s throughput capacity via vpn.

I’d like to ask your opninion, what solutions, options there are, which way I could start.
I heard sql server could be somehow combined with MS Access, enjoying the easy-to-develop feature of the latter one. The system is quite massive: number of tables is more than hundred, same stands for queries. I have absolutely no experience with sql server, please try to write in an understandable manner.

I do appreciate your help in advance!

Best regards

Peter

 

[CJ_London]

Depends on the number of users and therefore licensing costs by I would suggest using terminal server. Each user has a login and a profile. Put the back end in a shared folder and a copy of the front in each users profile and relink to the newly located backend.

You can install runtime access for the users, but make sure that works for you. Runtime gives access to a minimal ribbon, no access to the navigation window and short keys are disabled

Rename your development app as .accdr to see the impact. Accdr is used to experience the runtime environment for development purposes

Using terminal server should give much the same performance as if the back end was on the users machine

Also consider how your app communicates- linking to outlook/excel in particular

 

[The_Doc_Man]

First and foremost, you are correct to start by evaluating options. You would be surprised how many folks just start without asking a few questions. I cannot directly help you with SQL Server but many of my colleagues can.

Part of the difficulty associated with long distances is that you have to use wide-area networks (WAN) and they are not as reliable as local-area networks (LAN). Unreliable network connections can lead to corrupted databases very quickly.

Depending on how many remote users we are discussing, the cost of a remote terminal solution might be reasonable. If the databases representing the two sites have to be combined, a solution involving RDP or the CITRIX product might be helpful. Your DB would be at its original site but you could purchase an appropriate set of remote operation licenses for your remote site users to log in to your server. This IS a case where you would need to throw some money at this.

Our member Pat Harman is very familiar with this kind of remote setup and can offer more specific suggestions. You can also search this forum (using the upper-right SEARCH button) for CITRIX or for RDP (remote desktop protocol). Because RDP and CITRIX do not transmit as much data over the network as a remote query might do, your performance would not be as extremely affected as an Access/WAN connection.

 

[GPGeorge]

What does the newly acquired company have in place? Is it reasonable to evaluate adopting it? Let's not rule out anything at this stage.

I don't think you would end up doing that, but it's probably wise to at least look at it to see what ideas you could leverage if you have to integrate systems anyway.

Your basic choices, as others have said are:

Migrating data into a SQL Server database, either on premises or remotely hosted.
Some sort of RDP for the newly acquired site.

Both offer benefits. Both come with costs.

If you move to SQL Server, one benefit is a larger, more robust database engine to handle your growing data. Another might be the ability to leverage that same database for a website serving your customers.

There are also a couple of hurdles. One is the need to rewrite some parts of the Access interface to work effectively with SQL Server tables and data. How much that would entail depends to some extent on current design practices. The other is the potential for slower data transmission over the WAN connection. That can't be avoided, although it can be minimized somewhat.

 

[CJ_London]

not sure if sql server express can be installed on a terminal server, but if it could you would have the benefit of speed, greater data storage and improved data security (subject to how secure your front end is in the first place)

 

[gemma-the-husky]

One other thought is "duplicating" your system at the new location, and running it as a separate standalone company on the same software. If you intend to merge the two companies into a single entity, then this won't be such a good idea, but if you want to keep them separate you then need to decide whether reorganization (redundancies) might happen, and to move admin to your HO or leave it where it is.

You also may need to assess how and whether to bring the new acquisition into your method of working at all.