Re-design a wellworking data base (1 Viewer)

[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.

 

[Mark_]

Along with what other's have posted, you will want to identify what is ESSENTIAL for business at each site. Work out "What happens when our ISP goes down for a day" as part of your planning. Often, having one site down because of internet issues is not an option. This may require redundant connections at both sites.

Alternate would be to work out a subset of your system that can handle short term "Off line" operations and a way to integrate "Off line" entries back into your original system. When I had to deal with that we simply added an extra 2 bytes to our indexes for "Site ID". Allowed us to integrate data from multiple sites without conflicts. This was back in the 90's when trying to keep a reliable WAN up wasn't something a lot of businesses could count on.

Also talk to your upper management to see what parts of the new site will be kept and what will be let go. You don't want to work on supporting a portion of a business that won't exist in a month.

 

[petko]

Thank you very much for your valuable thoughts.

In our case two independent sites is not an option, the essence of the company acquisition was to meet the needs of the same customer scope and taking advantage of the mutual resources, so it is essential to have a common database as soon as possible.

Based on this point and what I understand from what you have written, there are basically two options to consider: go for Terminal Server or an SQL server.

As I have no experience with either one, please help me thinking further to make the right decision. Here is some more information:

The two companies are involved in technical project management, servicing, and parts sales. At the original site (call Site A), where the data tables are physically located now, there are 8-10 users, all with Microsoft 365 licenses. The front-end accdb perform significant calculations for almost every operation. These calculations are based on opening the right record sets. After roughly 10 years operation - including continuous development and expansion- the size of the accdb file, containing the data tables, is 500 MB today. The related documents (pdf, msg, jpg, etc. files) are contained in a structured folder system having 20-30 folders. Files are managed (stored, retrieved, deleted) by Access. The total size of this folder system is many GB, but the size of the individual files is small, and their handling is fast and problem-free.

The new location (site B) also means 8-10 new users. The total number of users is unlikely to increase significantly in the next 5-10 years.

Based on these info do you have any thoughts on which option would be the better one to take? If you need more information, I send gladely.

Thanks a lot

Peter

 

[Edgar_]

Have you given any thoughts to the possibility of moving that application to a web environment?

 

[CJ_London]

where the data tables are physically located now, there are 8-10 users, all with Microsoft 365 licenses.

To clarify- you have an access backend with all the tables in a shared location on a server together with all the related pdfs, jpgs etc. and each user has a front end accdb on their local machine which links to the backend?

do you have any thoughts on which option would be the better one to take?

It comes down to data security, licensing costs, time and cost to implement and business priorities/requirements. Only you or your company can answer that. You also need to consider does your app interact with ms office e.g. send/receive emails, create excel reports, import txt and xlsx files, create pdf's etc.

1. Terminal Server - easy to implement, no changes to the existing app other than moving to terminal server and relinking. Good security via TS login, fast performance - cost is typically licence per user. Small potential benefit - can be run from any device that supports terminal server - including iOS and Linux.
2. Install remote access software on the remote users machines to connect to a designated computer at the home base (one for each user). Similar performance to TS. cost of acquiring additional computers, potential licensing costs for the remote access software.
3. on-premises sql server with VPN - requires modifying access to work with sql server, good data security via sql server login, often slower performance for remote workers and possibly local workers as well if queries are not well designed. One time cost of purchase, ongoing maintenance costs.
4. off premises sql server/azure - benefits and mods to access per on-premises, typically slower performance (due to the WAN spec) costs - licensing costs per user plus transaction cost (how much you send/receive data from the tables)
5. website - start from scratch, long development time, costs of hosting/maintenance
6. Find and use an existing web based solution - time to upload existing data, will probably have to change or drop some existing business practices - licensing costs

Personally I would look at 1 or 4. But whichever route you go, there will be a relatively high cost, whether it be in the cost of transitioning or the ongoing licensing costs. You are probably used to pretty much zero cost at the moment. With say 40 users with a licensing cost of say $10/user/month that is $400/month

If it is relevant, many years ago I had a client who ran their whole business on Citrix -a higher spec terminal server, nothing significant was kept on their local machines. They did not have an on-prem server and on Citrix each user had a full copy of Office. Can't remember if they had any issues with sending documents to a local printer but it never seemed to be a issue

 

[dalski]

I know nothing so bear with - I hear few suggestions these days relating to Access & MySQL. One usually only hears of Access & MSSQL & I wonder why that is?
I've used systems that use MSSQL with a local installation at the business feeding circa 15 users & it has been good, about + 1/2 slower comparing to a local installation of only a single user. Very expensive for the developer version I think.

The pricing model in all software as a service now is just parasytic. The costs are big, & what you used to pay to buy the application outright you are paying more now each year; for the rest of your life. I think you can buy a decent enough server for £10k, then host locally?

To clarify I know very little & in no way to be compared of the input of those whom have commented; it's to gain their input as I'll be doing similar in a year or two's time.

 

[CJ_London]

with regards moving to sql server/azure (or mySQL or any other rdbms), take a look at this thread. The reasons for upsizing are different, but the things to review remain the same

addressing performance issues

As your db expands and there are more active users, it is quite possible you will experience performance issues and may be considering upsizing to SQL Server or MySQL. Before considering such a transition, you should review your current design: Overview From a performance perspective, Access...

www.access-programmers.co.uk

 

[gemma-the-husky]

Even if you use SQL server, you still need terminal services. If users are running the database at the remote location, they won't get an adequate response over a WAN. So they need to connect to HO and run on the TS at head office, assuming you still want some work to be carried out at the remote location.

 

[CJ_London]

Might be talking at cross purposes but with terminal server and Remote Desktop the only thing traversing the WAN is mouse and keyboard events one way and screen refreshes the other. All transactions are handled on the server/remote device. So performance is high.

 

[Pat Hartman]

Others have suggested RD or Citrix and I agree. Using either RD or Citrix is the least work as a solution. You can leave the BE as ACE and don't have to convert to SQL Server, which depending on how the app was designed originally could be trivial or a PITA. Look at the costs. Can the client run a Citrix server on their LAN? If they have to run it off site, they can use the Runtime engine if the app doesn't need any of the Office products. Otherwise, each user will require an Office subscription for the Citrix account. RD you would normally run in house. If your PCs have a professional version of Windows as the OS, that comes with a RD seat license included. Otherwise, I think they are $100 per seat. I don't know if they need to buy an RD license for their server or if that is automatically installed.

Neither RD nor Citrix requires a VPN and in fact, a VPN may slow them down. The other advantage of either of these products is that they run within a browser and cause very little network traffic which is why they are generally super fast plus, you can run them on any equipment that runs a browser that supports RD/Citrix so a table or phone could work in a pinch. I would never want to try to use an access app on a phone (too little real estate, no mouse) but you could design a few inquiry forms that could function OK on a phone. A tablet probably has a large enough screen that the app would be functional but it could be awkward without an external keyboard/mouse.

 

[gemma-the-husky]

In passing, note that if the processing takes place at HO, but users are working from a remote location, you need to consider some issues.

Namely, providing things like printing, saving output and providing integrated email and internet services at the remote location where those functions need to use data at the HO. I think this is non trivial, or at least needs knowledgeable input - certainly not something I understand completely. There will be a cost, I'm sure, not least the internal expertise to manage the topography of a wide area/distributed network.

 

[Pat Hartman]

In passing, note that if the processing takes place at HO, but users are working from a remote location, you need to consider some issues.

Citrix and probably RD allow for local printing. But if you are using Citrix or RD to use all Office products, then the client can have access to whatever is on the LAN.

 

[GPGeorge]

At the risk of offending some of my fellow developers, th

Others have suggested RD or Citrix and I agree. Using either RD or Citrix is the least work as a solution. You can leave the BE as ACE and don't have to convert to SQL Server, which depending on how the app was designed originally could be trivial or a PITA. Look at the costs. Can the client run a Citrix server on their LAN? If they have to run it off site, they can use the Runtime engine if the app doesn't need any of the Office products. Otherwise, each user will require an Office subscription for the Citrix account. RD you would normally run in house. If your PCs have a professional version of Windows as the OS, that comes with a RD seat license included. Otherwise, I think they are $100 per seat. I don't know if they need to buy an RD license for their server or if that is automatically installed.

Neither RD nor Citrix requires a VPN and in fact, a VPN may slow them down. The other advantage of either of these products is that they run within a browser and cause very little network traffic which is why they are generally super fast plus, you can run them on any equipment that runs a browser that supports RD/Citrix so a table or phone could work in a pinch. I would never want to try to use an access app on a phone (too little real estate, no mouse) but you could design a few inquiry forms that could function OK on a phone. A tablet probably has a large enough screen that the app would be functional but it could be awkward without an external keyboard/mouse.

While I agree with 99% of what you say, Pat, I tend to disagree about using a tablet. It's sort of like getting used to using a smart phone to send texts. Sooner or later, one can become proficient using the on-screen keyboard to type. Celluar-enabled tablets, IMO, are the sweet spot for remote, "wandering around a construction site" using a handful of inquiry screens kinds of requirements.

In fact, when I visit my local grocery store, I see their in-house shoppers running around loading up carts with orders from their on-line customers. I see they use a device roughly the size of a smart phone to complete customer pick lists. They appear to be thicker than most smart phones, but are not much taller or wider. I image they are only using them to check off items as they add them to their carts.

Anyway, all of that is largely a matter of circumstance, though, and nothing that can be measured in the way per user license costs or download speeds.

 

[gemma-the-husky]

Citrix and probably RD allow for local printing. But if you are using Citrix or RD to use all Office products, then the client can have access to whatever is on the LAN.

I am not completely familiar with everything invoiced. Do the remote users not need any sort of special set up and/or training?

 

[Pat Hartman]

I am not completely familiar with everything invoiced. Do the remote users not need any sort of special set up and/or training?

Citrix (and maybe RD) can be set up in one of two ways.
1. a desktop icon that opens a standard desktop from which you can open your Access apps or anything else you want.
2. a desktop icon that opens a specific application.

The user doesn't do any setup. The icons come as part of your windows login.