Access 2016 Export to MariaDB ( WAMP installation )

[DaveLembke]

Trying to figure out a way to migrate my Access 2016 database to MariaDB which is in a no-password WAMP installation. Its nice that after all these years Microsoft Access still doesnt have a easy export ( dump ) of database to something like alldatabases.sql for easy import to MySQL.

I found a tool that is free called Access to MySQL as seen here http://www.bullzip.com/products/a2m/info.php  , but its intended for Access 2007 and it breaks with Access 2016 asking for AccessDatabaseEngine installation. I went to Microsoft and attempted to install the x64 version of Access 2016 Redist - Access Database Engine and it gives an error message that my Access 2016 installation is the wrong version stating that my installation is 32-bit. So I then downloaded the x86 version of it and attempted install of the Access 2016 Redist - Access Database Engine and it gives an error message that my installation of Access 2016 is 64-bit and wrong version.

The Access 2016 install I got through College where I am taking a modern database management course, since the last college course in databases was back in 2000 and I figured I should be brought up to date on databases and security, as well as passing data to them from php. I am not sure if there is an issue because maybe its a student license for Access 2016 and maybe they dont allow the Access Database Engine installation for fear that someone might use a Student Edition for Commercial Purposes, but the error message isnt of much help to determine whether this is the case or not.

Here is the course I am taking ( Sharing so you all know your not doing my homework for me by sharing how to migrate  . Course description states: The Course will be taught using MS-Access 2016. ) : https://andromeda.ccv.vsc.edu/Learn/Grid/SectionDetail.cfm?SEC_NAME=CIS-1360-VO01&grid=Summer

Chatted with the teacher and he is basically teaching the rest of the class at a different level then myself where I cover the same stuff that everyone else does but to get my $858 worth out of it, he stated he will give me more challenges that the others who are new to this arent required to perform, however the migration of data from Access 2016 to MariaDB's MySQL isnt a challenge of his, its a challenge of my own. I want to find a way to easily migrate databases we make in Access 2016 to my WAMP installation, so that scripting and programming that I am familiar with doing with MySQL can be achieved to do stuff beyond the limitations of MS Access. I havent used MS Access since Access 2003 many years ago. Looking on Google there are all sorts of methods but it seems like each one I try I hit a wall.

So far I have tried:

-  Access to MySQL and that doesnt work because I cant get the Access Database Engine to install to support a dependency of the software.

- From Access connecting to MariaDB's MySQL Database via from Access 2016 ->External Data ->ODBC Database ( Export selected object to an ODBC Database, such as SQL server )... other options are Excel, Text File, XML, PDF or XPS, Email, Word, SharePoint List, HTML Document, or dBASE File.  *When trying the Text File it only created a data file with no schema and schema is needed in order to reconstruct it on MySQL where the database structure doesnt exist yet to take the data. I tried other methods such as XML where XML creates a schema file, but its not in a format that is easily loadable into MySQL.

After 6 hours working on this off and on today I figured it was time to run it by you all and see where I am going wrong. Might be something pretty simple. I never realized until I got here that Microsoft as far as I know doesnt make migration easy for those using Databases other than Microsofts own products.

Total worse case scenario is that I dont bother migrating the database as a whole and reconstruct it all from scratch in MySQL but there has to be a way to make this work without accepting defeat and going the path of least resistance to just reconstruct these small databases. 

[BC_Programmer]

Unfortunately, Going from one database to another usually requires custom-made tooling. Even if Access could dump an sql file, you wouldn't be able to simply run that sql file in MariaDB/MySQL/Postgres because they use slightly different languages and data types. It <could> be done with 100% SQL Compliance but Access doesn't use an SQL engine, it uses the Custom "Jet" Database Engine Microsoft constructed back in the day, so I doubt any SQL it outputs would be compliant.

Just speaking as a problem solver myself if I had this problem I would probably try to write a function in Access VBA that would export the database schema and data to an SQL file targeted at the desired database engine. It would have to appropriately map data types and use the appropriate syntax for the desired target where applicable when outputting the data. It would certainly be a non-trivial undertaking.

As you've found there are some tools designed to make it easier- but, your installation is 64-bit, and the tools are 32-bit and require a 32-bit component for Access.

By way of direct example, The Database engine I/we use for the software through my Work is Postgres, and there wouldn't be any "automagic" way of say moving that database over to MySQL or MariaDB; if we wanted to work on those databases as well, we would need to develop the tooling to migrate ourselves (and of course update our software to also work with that database engine). Similarly, there was no Magic toolset we could use to automatically move date from our ancient software (ISAM database files on a Mainframe) to pretty much any modern database (in our case Postgres) so we had to develop that tooling over time. Meanwhile, it had to remain 100% compatible and work both ways,  Customers had to be able to sell in Windows and see it on the Reports they ran on the mainframe... ugh. so it wasn't just a one-time data conversion- it had to actively see new data in the ISAM and write it to postgres and see new data in postgres and write to the ISAM tables on the mainframe.

It's a one-time conversion now, but it still requires a custom tool we built for the purpose to do so.

I wouldn't expect it to be much different with most other database technologies. (Except Access to MSSQL I expect, since MS built tooling for that to encourage MSSQL usage)

[DaveLembke]

Thanks for your help on this BC. Interesting info too on the issues of old and new and having to make custom coded tools for cross portability of new-to-old & old-to-new ( Windows to Mainframe) for the systems that you work with. Also wasn't aware that Access uses "Jet" and not SQL engine.

I guess for my main project I am just going to avoid Access 2016 and use MySQL Workbench as well as SQL console prompt instructions for building the database. I also have used Community Edition of SQLyog for ease of GUI navigation in tables and to edit data manually.

I got until the first week of August to get my final project done, and eager to dive head first into it asap as for php is new to me and the teacher suggested that I get familiar with php vs going with perl that I am far more familiar with. I agreed that this would be a good time to dive into php where while the teacher isnt teaching php, I have played with it before to know my way around it but perl is my strength. However the teacher is right in calling me out to dig into something with more challenge vs taking the easier path as for I will get more for my money by also strengthening my php skills. Additionally the teacher has a masters degree and codes mainly in php and suggested that I should tap into his knowledge as a resource that I paid $858 for and get the most for my money. And I agreed

[BC_Programmer]

It was pretty insightful to me, in terms of customer needs. because, as it happens, when it comes to the "real world" you often don't have the luxury of building from scratch using the latest tooling, or using the latest whizbang technologies from Microsoft. If you have customers going back 30 years, providing a compatible, interoperable solution that works with your old software is one of the major advantages you can have over  any other competition. If you go "nope, you have to start over" than they could just as well find somebody else. But when it's, "You can train all your staff on the new staff, but all the old stuff can work for now, ol' Gilda Grunty in Accounting can still use the same reports on the Terminal she's been doing for 40 years until she retires 5 years from now" then it is far more enticing.

As to SQL/Jet, As I understand it, it allows access (heh) to data via SQL but it's a rather limited language compared to what you get in most "full" Database server programs. It also, as expected, integrated a lot of Microsoft-specific stuff as they tend to do. You <Might> be able to export and import the data after you build a schema- most database admin tools offter some capability to say import from CSV.

[soybean]

My knowledge of database stuff is very limited, so I hesitate to say anything on this topic.  But, in the vein of an export (from Access) and import (to MariaDB) approach mentioned by BC_Programmer, I thought I'd post a couple of links from mariadb.com.  One is titled CSV Overview; the other, How to Quickly Insert Data Into MariaDB

[DaveLembke]

cool Thanks soybean!

Had my college class today and it looks like I will end up creating the database on my wamp MariaDB. Then import the data to database from csv which is like what you have linked... Thanks for the links.