SQL Server?

[mounty76]

Dear All,

I have a large DB which is currently split, I've worked on it over the last 5 years and as such it's quite disfunctional in that it could be designed much better in how it works (IE much less tables and queries etc.), but as you live and learn you figure out better ways to do things. Essentially it works well but it could be better.

I'm looking at possibly starting from scratch and making a new DB using better methods to achieve the same results, reason for this is that the DB works well and I've been considering selling it/marketing it (small scale to start with and just see how things go).

Really I suppose my question is.......I'm thinking this should really then be done using an SQL Server so both ships and land based offices have access to the same backend using their FE. I've never done anything with a SQL Server so a few questions would be: 1. Can I just make a better functioning DB of my existing one then upload the BE to the SQL Server....is this an easy process? 2. Is it better to develop it using the SQL server from scratch. or 3. Is it better to get a developer to do it and pay to have it done properly haha....

Anyway, any guidance on the best way forward would be very much appreciated!

 

[CJ_London]

Usual reason for choosing sql server as a back end is data volume, data security or large numbers of users. Performance alone is not a reason (it is unlikely to be faster)

you mention ships which shouldn’t matter unless user is on a ship and the BE is on land somewhere in which case what matters is speed of the communication method and optimising your app to minimise network traffic

 

[amorosik]

Not exclusively Sql Server but certainly any "db server" (Oracle, Ibm, Postgresql, ...)

 

[mounty76]

Usual reason for choosing sql server as a back end is data volume, data security or large numbers of users. Performance alone is not a reason (it is unlikely to be faster)

you mention ships which shouldn’t matter unless user is on a ship and the BE is on land somewhere in which case what matters is speed of the communication method and optimising your app to minimise network traffic

Thanks for this, communication speed shouldn't be an issue now with Starlink which many ships are using. Ideally I'd like BE on a cloud so can be accessed from any FE user regardless where they are in the world.....from what I've read best solution is SQL Server for the BE but any other 'easier' suggestions much appreciated. If I can run it so that the DB is designed and split the same as you would using a local server (but using a cloud server instead of a local server) that would be perfect

 

[Lightwave]

This is a long term project - so give yourself plenty of time e.g year or more...

Its a good idea to get the backend into an enterprise grade database... My personal choice is usually sql server or postgres although mysql and maria db are options as well.

If you already have an MS Access front end I would go with SQL Server.

Once you have it in SQL server you won't need to worry about size... so an immediate win.

Chances are you have a complicated backend if you have been working on it for 5 years.. Get yourself a copy of SQL Express and SQL Server Management Studio (both free) and firstly see if you can get it installed and running and then take your biggest table and learn how to recreate it in SQL express. Just do this locally initially. That will give you an idea of the complications involved of just recreating the backend in SQL Server.

It is easy to take a backup of SQL Express database and upload it to the cloud if you want in fact provided you have an Azure account you can do it in minutes directly from SQL Server Management Studio. Here is a link to get SQL Server Pro and Express (choose the express version). In theory you will be able to link your existing access front end to the new SQL express database BUT you will have to be exceedingly accurate in the way you recreate the SQL backend to do that. Here's the link to download of SQL Server Express and Pro versions

SQL Server Downloads | Microsoft

Get started with Microsoft SQL Server downloads. Choose a SQL Server trial, edition, tool, or connector that best meets your data and workload needs.

www.microsoft.com

I believe there might be tools that will partially automate transition from Access to SQL Server but you should expect that they will not work... I personally have never used them and haven't investigated them but briefly so can't give you advice on that... But automatic transformation between platforms is usually a myth.

 

[mounty76]

Thanks very much for this, much appreciated. I had already given myself a year plus to develop this so all good for that, I'll do some research based on the above and more than likely revert on this forum! Thanks again

 

[Pat Hartman]

You can't just link your Access FE to a BE "in the cloud". It will be like watching paint dry.
If you have a poorly designed BE, a straight convert to SQL server will make everything slower.

1. Can I just make a better functioning DB of my existing one then upload the BE to the SQL Server....is this an easy process?

I can convert any application I built myself to SQL Server in a few hours - and that is primarily testing time to make sure that nothing got overlooked. But, that is because I build my FE to take advantage of client/server technologies regardless of what BE it is connected to. It might take days or weeks to convert an app built by someone else depending on how poorly designed it is. You have to start with a properly designed schema. Believe it or not, Jet is frequently faster then SQL server due to its close connection with the Access FE.

2. Is it better to develop it using the SQL server from scratch.

Whether you start with Jet/ACE or SQL Server directly is irrelevant. What matters is did you design the database schema correctly? For an Access application to use SQL Server effectively, it needs to be designed with that in mind. People frequently design forms bound to tables or to queries with no selection criteria and which select all the columns even when the form uses only some of the columns. They then use filters to let the user select which of the thousands of records he wants to work with. This technique forces SQL Server to download the entire table (and any other tables in the query) when the form opens which defeats the entire purpose of using SQL Server. To use SQL Server or ANY RDBMS effectively, you have to make the server do the heavy lifting and you want to bring as little data as possible across the LAN to your local PC. ALL of my forms are bound to queries and the queries select only the columns needed and include selection criteria to reduce the rows returned to the absolute minimum. For most forms, users aren't working with more than a single record at a time, so why bring down all that extra data. This causes unnecessary traffic on the LAN plus extra work for Access to manage concurrent updates when all the users have all the data in their possession. Even when the users are technically updating different records, if a record is part of the recordset bound to the form, Access has to keep each user apprised of the update status of all records bound to the form. Fixing this alone will speed up most applications.

Then you have to understand how queries work and understand what can and what cannot be handled by the RDBMS and what has to be handled locally. Access makes every effort to make every query "pass through". That is try to let the server do ALL the processing to return the selected records. YOU can defeat this effort if you don't know what the server cannot do. For example - the server cannot process VBA functions or user defined functions. Access can convert some VBA functions to SQL equivalents but if there is no SQL equivalent, then Access has to process the function locally. If the function is in the Select clause, that won't be a problem. However, if the function is in the Where clause, it is a huge problem because it will prevent the server from selecting records and will force Access to request all records and then process them locally against the function. So, there are many things you can do in your Access FE that will interfere with letting the server do the heavy lifting so you need to have an understanding of how to achieve your goals most efficiently. Sometimes (rarely) it takes a stored procedure. Sometimes, you need to create a real "pass through" query. Sometimes just creating a view and linking to the view rather than the underlying table will make the process more efficient.

It is NOT necessary to use unbound forms or to convert all your queries to pass through or to write all your code as stored procedures. I've been using Access FE's linked to a wide variety of RDBMS since the early 90's and have never had to build an unbound update form. Sometimes reports are very complicated and benefit from stored procedures. Certain types of bulk queries work better as pass throughs but once you make your forms work efficiently, there will be very little else that needs to be rewritten.

3. Is it better to get a developer to do it and pay to have it done properly haha

Depends on how much money you have to spend. You will find that the majority of the cheap developers who say they know "Access" don't have a clue.

The best options for sharing Access applications between users who are not on the same LAN are Citrix and Remote Desktop. Cloud storage drives CANNOT be used to share Jet/ACE BE's. PERIOD. Azure has some promise.

 

[mounty76]

Thanks very much Pat, much appreciated. Complicated stuff. I guess locally stored on a server with remote desktop for an external office would be the way forward in my circumstance.

Thanks again

 

[Pat Hartman]

Yep, that's the simplest option to allow remote users. Access does not play well over the internet.

 

[Gasman]

SQL Server, which was originally created by Microsoft, is a highly advanced and widely-used relational database system. It provides a robust framework for storing, managing, storing, and retrieving highly-structured data, making SQL Server a fundamental tool used by both businesses and organizations. SQL Server provides a comprehensive set of features that allow for efficient data management, data manipulation, as well as data analysis capabilities. Its scalability is complemented by performance optimizations that allow for processing large volumes and assisting high-demand applications. SQL Server features advanced security to safeguard sensitive information, as well as support for integration with Microsoft technologies, resulting in seamless data integration and interoperability. Overall, SQL Server works to create robust and secure data-driven solutions across a range of industries and use cases. View attachment 108654

Another ChatGPT lookup?