Migrating back-end to MySQL (1 Viewer)

[bongbang]

I have a working Access DB application with a fair bit of VBA code. It's meant for a single user on one computer (me) and it's not very large (under 10 MB at this point, although it will get bigger when I start adding photos and perhaps even videos to it). There's no compelling need to migrate the back-end right now, but I'm considering it for the following reasons.

1. MySQL Workbench's SQL editor is so much more enjoyable to work with than Access's. Plus Access often adds superfluous parentheses to my code that not only look messy, but also make them more difficult to modify w/ VBA.

2. Separating the front-end and the back-end is supposed to be more stable than putting them all in one big file. I'm not experienced enough to evaluate the claim myself, but I intend never to find out it's true the hard way.

3. I'm still developing this application actively, and if I'm ever to migrate, I figure it will be better now (when things are functional but not Rube Goldbergian) than later.

What I want to know is:

1. Is the pain worth the gain?
2. How much time should I budget for the migration?
3. Many a form of mine has an embedded query as recordsource, which can then be modified by VBA-powered switches on the form. Will these continue to work through ODBC?
4. What are other pitfalls of migration that I should look out for?

From my internet research, Access-MySQL integration is painful to some and painless to others. The information is pretty dated, however. What is the landscape in 2018, with Access 2016 and MySQL 5.7? Thank you.

 

[jleach]

I'll definitely agree the FE and BE should be separated, for sure. However, the first step would be to split it out into two separate Access files (that's the easiest, and gets you a lot of bang for your buck, so to speak).

As for the IDE, yes, Workbench is better. But... have you considered SQL Server? Far more robust DB engine, and really tends to work a lot better with Access FEs anyway (and various other applications in the ecosystem). SSMS (SQL Server Management Studio) is a top of the line tool: better than Workbench, IMO, but that's arguable. As a dbengine, MariaDB/MySQL are kind of... not that great, really. You'll have to go through a few (relatively minor) extra hoops linking to MySQL from Access than you will to SQL Server. Drivers aren't maintained as well as they are for SQL Server, data type mappings are a little more wonky, etc. etc. It's hardly impossible, but you get a lot of little pebbles tossed at your head sometimes.

There's a whole host of things you can do to help with a "swappable" backend. In a perfect world, you'd write your FE to be usable against any BE (JET/ACE, MSSQL, PostgreSQL, MySQL/MariaDB, SQLite even...). However, to do that requires a lot of experience and having done a lot of the hard way first... years' worth, really.

To your questions:
1) I don't think so, myself: not without compelling reason (of which the only I can think of is that there's already a MySQL database that you are forced to work with). Split it out to an Access BE and you'll be far better off than you are now in terms of architectural concerns.
2) A lot. Your post implies a lack of general experience in app architecture ins and outs (no worries, we've all been there), and this is a job best done having perhaps some simplier structures under your belt first.
3) Yes and no... ODBC will do what you tell it to, but be careful. I wrote an article on the topic of ODBC w/ Access FEs (specifically for SQL Azure, but it's equally applicable to any ODBC source): https://dymeng.com/azure-series-05-database-performance/. Small differences in queries can mean the difference between 2 seconds and 2 hours to process. Know what you're doing.
4) Hard to enumerate them. It's really a matter of experience: first time through is a headbanger, second is slightly less, third is sometimes ok, etc etc.

Where do you plan to host the MySQL database? Windows (yuck)? *nix on a network with a Windows machine to run Access (yuck)? Remotely? Storing images and video? Will you stream the video? Is the server going to support that level of bandwidth?

There's a lot of factors that ultimately make the best decision for an application architecture. This is another reason I suggest SQL Server, being as it can easily enough run on a local machine or on a typical Windows network (if you were talking some other FE besides Access, then we'd have more options, but Access only works (and will likely only ever work) on Windows boxes).

 

[The_Doc_Man]

it will get bigger when I start adding photos and perhaps even videos to it

Watch out and DON'T embed these visual things as OLE objects. Make them external. Then your split FE/BE will have less bloating. Remember that an Access file has an inherent 2 GB size limit. Making it SQL raises the size limit but (depending on how it is done) adds bandwidth to your expected traffic (if the BE is on another machine.)

Jack also notes that how you handle the visual stuff affects SQL BE performance. We both probably agree that you need to spend some up-front research time on optimum solutions for what you intend to do. Which includes how often you touch the visual imagery, how big the files can get, and how good your network would be.

There are many good reasons to migrate to an active SQL engine for the BE but the question is, WHY did YOU think you want to migrate? I.e. are you worried about sizing issues? If so, what part of the issue is related to storing imagery of any sort? Because if you keep the files external to the DB, there IS such a thing as activating a hyperlinked file where the text of the link is inside the DB but the linked object is not. And in that case, you don't care nearly as much about sizing issues.

 

[bongbang]

JLeach,

Thank you for your detailed response. After reading, I'm inclined to just split the FE and BE as you recommend. Still, since this project is supposed to be educational as well as practical, I would like to explore the possibility of SQL Server some more. If for a little extra work, I can get some experience w/ a "real" database engine plus a nicer SQL editor that allows comments and highlights syntax, then it may well be worth it.

You're right that I'm very much inexperienced. This is my very first DB application, and I'm my own client. I'm not at all familiar with architectural considerations. As for the app itself, however, I must say it is already a huge success. I'm just trying to make it better.

At the risk of sounding silly (please say so if I do), I was just going to install MySQL on my local Windows 10 desktop and use it that way. In fact, it's already done. Before its current incarnation, my app started out several months ago on LibreOffice Base, where I was learning the very basics of DB development as I went. After Base crashed and lost data on me twice (despite the database being already "split"), I migrated the BE to MySQL. It was relatively painless. The crashing stopped, and I fell in love with Workbench.

I quickly found out, however, that Base is not ready for prime time as a front-end and migrated to Access as a result. I couldn't be happier with the transition. Access is excellent as a FE tool. Still, I keep MySQL in mind when I consider the need to split the BE and my desire for a competent SQL editor and a more mainstream flavor of SQL (JET is weird). Hence the question.

Now that I know that SQL Server would be a better choice, my questions are now:

1. Any limitations on SQL Server Express that would undermine my app's functionality? (I doubt it, since my dataset is pretty small, and that DB is only accessed by one computer.)
2. How much work is it to migrate to SQL Server Express?

Thank you so much.

 

[bongbang]

Jack also notes that how you handle the visual stuff affects SQL BE performance. We both probably agree that you need to spend some up-front research time on optimum solutions for what you intend to do. Which includes how often you touch the visual imagery, how big the files can get, and how good your network would be.

Visuals will not be a salient feature of my application. They won't be accessed very often. It's just that sometimes, pictures and even videos are required for documentation.

A good analogy might be the police's records. Normally, they deal with texts and dates and usual database stuff (I think), but now and then, it's desirable to put images and videos in a case file. In fact, my visual needs will be far less frequent than theirs in my estimation.

As for my reasons for wanting to migrate, I gave three of them in my original post.

 

[jleach]

SQL Express gets you 10gb of db size. You can't do automated backups and such through SQL Agent (not available on Express), and you won't have access to advanced features such as Transparent Data Encryption, etc - but most people migrating from Access don't need that anyway (and if you're concerned about backups, you can easily run a backup via T-SQL and SQLCMD from the Windows task scheduler). All in all, nothing really undermining here.

As to how much work to migrate, there's really no way to answer that. Running a development company, we look at things like this fairly often, but each case needs to be looked at in detail before any sort of ballpark can be made. In many cases you can just migrate the Access DB using SSMA (SQL Server Migration Assistant) and relink the FE tables to point to the SQL Express database, and continue on from there without much side effects. Sometimes, it can require partial or in extreme cases even a total rewrite of the application (not usually that, but depending on how strict performance requirements are in conjunction with how poorly the current Access code/object structure works in terms of performance...). Anyway, it sounds like you don't have a mission critical application that will cripple millions of dollars worth of industry per month if things aren't spinning smooth as a top, so you're probably on the lower end of the scale in terms of effort, but still near impossible to throw any particular number at it.

Re: MySQL on Win10 - IME, works good enough for development, but long term stability running MySQL on Windows has traditionally been a bit shaky. Been quite I while since I've done it though, so maybe things are better (but it was never really meant to be run on Windows: primary use of a MySQL database is on Apache, usually with some pile of PHP garbage thrown on top). Between that and its open source history, Windows hosting just isn't as solid as it could be as most efforts went into hacking things together enough to prop up the next big requirement for the webservers.

Re: IDEs - SSMS can handle all the comments and whatnot. Even stores them so when you recreate you still have them in place. See attached for a quick example output from a "Script as Create" command against an existing sproc I happened to have open from the workday. Pro-level stuff. Also, for quick ad-hoc querying (and for Access queries), I highly, highly recommend Notepad++ with the Poor Man's T-SQL Formatter plugin. That same sproc attached shown in np++ formatted accordingly (this is a can't-live-without tool for me working with Access: even with a SQL backend, some queries are still done on the FE, and especially for inherited projects). It's particularly nice with Access because you can copy a pile of unformatted SQL from the query designer, hit your formatter shortcut key (mine's mapped to CTRL+SHIFT+W, but whatever), and there ya go: formatted SQL (still need to remove comments before pasting back into Access though).

 

[arnelgp]

Im using mysql comnunity edition much faster than mssql express. The connector is very stable. I use mySqlog for migration and maintenance. It can open any database.

 

[jleach]

much faster than mssql express.

I think it depends on what you're doing with it. The engines tend to be build in a way that makes each one a little better at this type of thing, where the other is a little better at that type of thing, etc.

I don't typically notice any performance difference between various engines on a typical day to day basis: it's usually when we have to really start fine tuning things that this seems to come into play.

Cheers,

 

[The_Doc_Man]

I'm going to step away from this one on the grounds that my knowledge is strictly theoretical in this case, whereas Jack and Arnel have hands-on experience. If the question returns to theoretical issues, I might step back in.

 

[Pat Hartman]

1. If you have used any Access only data types such as Attachment, hyperlink, and calculated or multi-value fields, etc, there is NO conversion path because neither MySQL nor SQL Server support them.
2. As others have asked, do you really need to embed the graphic objects? I would think long and hard about that regardless of which BE I was using.
3. The vast majority of Access applications that are using a "real" RDBMS as the BE use linked tables. That means that you will be using the Access QBE rather than your dream editor anyway. Unless you want to take on the pain of using pass thrugh queries and unbound forms, you will be working with the Access GUI almost exclusively although you may occasionally need to create a view or sproc to do some batch process that is simply too slow with linked tables.
4. If you have used good client/server techniques when building your application, you can easily convert from ACE to SQL Server in a very short time. I can typically do it in an hour and I have several apps that are sold to the public that can hot-switch between ACE and SQL Server depending on what the client wants to use as the BE.
5. Even a single user application should be split into FE and BE. It will make it easier to make modifications to the application.
6. I don't see anything in your problem statement that tells me that you need to switch the BE to a "real" RDBMS. That doesn't mean that you shouldn't do it, just that it probably isn't necessary.

 

[bongbang]

Pat,

Thank you for your reply. I know that this move is unnecessary, which is why I asked the question and indeed acknowledged as much in my original post. I'm just hoping for (a) an easier time when dealing SQL code (I really hate Access's "Notepad"), (b) an learning experience dealing with a "real" DB engine, and (c) better stability and scalability in the long run.

I'd appreciate clarification on some of your points.

2. Okay, most sources recommend storing file paths, but SQL Server's filestream data type intrigues me. Isn't that the best of both world? One problem it would solve is having to write a script to take the uploaded file and store it in the right place with the right name myself.

For what it's worth, Microsoft recommends blob for < 256 kb files.

https://www.microsoft.com/en-us/res...microsoft.com/apps/pubs/default.aspx?id=64525

3. I don't quite understand. Are you saying that I can only access the BE's tables and not queries? That is, if I make my queries and views on SQL Server, I won't be able to access them from Access? Can that really be the case?

 

[jleach]

You can access SQL Views from the Access FE, but it doesn't always make sense to do all of the querying on the backend as views: there's still a fair amount of FE queries you'll be writing as well. Hard to explain why, but it's a mix of both, IME.

Regarding BLOBs, that paper was written 12 years ago. Things might have changed since then. Regarding FILESTREAMs, I expect this is an additional complexity that's not necessary (there's extra work in setting up and maintaining these).

 

[Pat Hartman]

Access isn't up to date on newer data types. I don't know if you can even use the filestream data type. There was a recent change that allows A2016 to work with BigInt but it requires a setting at the database level that prevents the .accdb from being used by older versions of Access. Sometimes updating your SQL Server driver will give you new data types. For example the default driver which is what you are using unless you specifically downloaded and installed a new one is at least 15 years old and the only date data type it recognizes is datetime. All the others are rendered as text and act like text unless you get the newest driver.

The point of Access is that it isolates you from much of the nuts and bolts and provides a consistent interface so that you can hot swap your linked tables and one day they can be Oracle and the next DB2 and then back to ACE. That is only possible because of the QBE and the Access variant of SQL. If you want to use specific features of a RDBMS, you have to use pass through queries and those will not work for bound forms so you have to create unbound forms and you loose one of the other big benefits of using a RAD tool which is bound forms.

So, you need to decide whether you want to use Access, the RAD tool and give up on some more esoteric features of your RDBMS of choice or go with a different development platform where you can get up close and personal with your RDBMS but which doesn't give you the flexibility of hot swapping to a different RDBMS. The price you pay for the flexibility Access provides is having the bleeding edge of technology blunted so smooth the way for you.

3. I don't quite understand. Are you saying that I can only access the BE's tables and not queries? That is, if I make my queries and views on SQL Server, I won't be able to access them from Access? Can that really be the case?

Of course. All SQL implementations are based on an ANSI Standard that specifies what features must be implemented but all SQL implementations also include enhancements and you will also find syntax differences. The Access SQL syntax is similar to T-SQL but they are NOT interchangeable. You cannot simply take a T-SQL statement and pop it into an Access querydef. You can use it as a pass-through query but pass-through queries are NOT updateable. That's the price.

I don't like what Access does to SQL strings saved as querydefs but there are ways to coerce it into doing your bidding. For example, if you NEVER switch the query back to QBE view, Access will not reformat the SQL. But of course, you are still limited. The IDE is really hopeless and less than helpful but why would you ever want to type long SQL strings anyway? I learned SQL back in the eighties using DB2 embedded in COBOL so all I had was the COBOL editor. It was more painful than you can imagine. I used to dream of a GUI for building SQL strings and when I discovered Access in the early 90's, I thought I'd died and gone to heaven. I still prefer the Access GUI to SQL Server's version but you're right that the IDE is hopeless. Or as we say at the Bridge table - double-dummy hopeless.