Problems of migrating from Access to MySql or MariaDB (1 Viewer)

Etxezarreta

Member
Local time
Today, 14:55
Joined
Apr 13, 2020
Messages
175
Hello all,
I had a general question: compared to a Access FE-SqlServer BE model, what are the mains challenges of using MsSql or MariaDB as a BE?
For exemple, are there any functions that need to be adapted ?
Many thanks in advance.
Etxe.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:55
Joined
Oct 29, 2018
Messages
21,357
Hi. At least with MSSQL, you will have to learn T-SQL. Some VBA functions are transferrable, but there are some you'll have to convert. Probably the same with MariaDB and others too.
 

Isaac

Lifelong Learner
Local time
Today, 06:55
Joined
Mar 14, 2017
Messages
8,738
Don't take this as encouraging people not to utilize t-sql, as I think it's a very good idea to utilize it, once you have sql server as a BE.

But there are certainly people out there who use SQL Server as a back end and have never written a single line of T-SQL. They just keep writing queries (using Access functions, Access SQL, and Access VBA functions) against their ODBC table links and never go beyond that.
 

Minty

AWF VIP
Local time
Today, 13:55
Joined
Jul 26, 2013
Messages
10,354
There is core "functionality" in most versions of SQL, however each DBMS has subtle variations on the same theme.

I suspect

SELECT Field1 From Table1

would work in most though.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:55
Joined
Feb 19, 2002
Messages
42,970
Ben's articles are always informative. I've used quite a few different BE's to Access. The most stable and flexible was actually IBM's DB2. The worst was Sybase which is what I think SQL Server is based on.

I don't think that MySQL is much different. I'm not sure that MariaDB is a relational database but if it is, it will also work. That is why Access is called "Access". It works with ALL RDBMS' that support ODBC. You can make a lot of work for yourself and do everything with unbound forms and pass through queries and stored procedures, or since you committed to using an Access FE, you can use Access as the RAD tool that it is and do things the "Access" way with linked tables and querydefs and bound forms/reports. You won't get the optimum performance but you will get the optimum development time and optimum flexibility and if you are not sloppy with your code or use old style Jet/ACE techniques (like form filters) rather than good client/server techniques, you will get performance that no one will complain about. If I were going to eschew the use of bound forms/reports and querydefs, I would never use Access. As with typical development projects, it's a pick two out of three. Whenever you develop with Access as the platform (Access is NOT a database engine so I'm only talking about building the FE), you give up the ability to twiddle bits and that means you are giving up a certain amount of execution speed for dramatically increased development speed and flexibility.

Access is infinitely extendable provided you don't use Jet/ACE BE's and don't need web access. Once you use RDBMS for the BE, you are limited to seat licenses for your RDBMS of choice rather than the hard limit of 255 or practical limit of 50-100 concurrent users with Jet/ACE BE's
 
Last edited:

Users who are viewing this thread

Top Bottom