migrating from MS Access to SQL Server or Mysql

[simonm]

Hi,

My access backed is going to need upgrading to a more robust solution in the next few months. Can anybody recommend a resource(s) that deals with this upgrade.

Eg. Installing SQLSERVER on a VPS, then transferring and maintaining the database. Or much the same for MySQL?

Any pitfalls, recommendations?

thanks in advance

Simon

[Gtal]

Considering your needs, to transfer all of your data and then maintaining it on your database, you could use different solutions.

Best thing would be to use an ETL tool to do the migration. You will also be able to maintain your database and import more data to it later on.
There are open source ETL tools, meaning you could download your software for free.

[ronchalice]

If you are using the basic Access database, the simplest way to begin the conversion is to install MSDE (Microsoft SQL Desktop Engine, available as a free download for licensed MS Office users) and convert the Access Database to an Access Project (the internal conversion process will automatically do full conversion of the data and most queries to MS SQLServer tables and views.)

MSDE is limited to a 2GB database with a number of constraints, but it it very simple to migrate to a server-base SQL Server environment.

[smo]

I was using MyODBC to shift tables from access to mysql, Now not tested with new versions of MySQL but it should work. All details are here.
Exporting or Converting Access Excel data to MySQL using myodbc

[wonn1377]

I use data loader for migrating almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. In my view this is a best Data Migration Tool

Download Free : dbload

[wonn1377]

I think u should use a third party help to resolve this problem, i use dbload to solve it when i was migrated my data, it can migrate almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. i found it on google search dbload.

[jean]

Here is a tool to convert mssql to MYSQL database. That i found on google search it says it can convert almost any database try and tel me is that worth or not.

Find Here : www(.)convert-db(.)com/mssql-to-mysql(.)htm

[redcar]

Access 2007 has a built in tool to upgrade your database to a SQL one.

[freezea]

A Process for MS Access Migrating

The consensus of MySQL users is that automated conversion tools for MS Access do not work. For example, tools that translate existing Access applications to Java often result in 80% complete solutions where finishing the last 20% of the work takes longer than starting from scratch.
Instead, the best practice for Access migration is to rebuild the schema, cleanse the data and then rewrite the application. Although this is time intensive, it is the only way to ensure that the resulting application is of sufficient quality to be maintainable.
This lays out a step-by-step process to migrate an MS Access application to MySQL:

1. Rebuild the schema: create a new schema in MySQL that reflects SQL best practices rather than trying to simply recreate the MS Access schema in MySQL. Ensure proper definitions for the following elements
   1. Primary keys
   2. Indexes for common search and join columns
   3. Foreign keys for all relationships, along with cardinality constraints and delete propagation constraints
   4. Default values for columns
   5. Null-allowed columns
   6. Views
2. Clean the data: extract the data from the MS Access database, using the MySQL Migration tool or a simple .CSV export. Before importing the data, perform data cleansing:
   1. Ensure primary key uniqueness
   2. Ensure referential integrity: check that primary key exists for all foreign keys, ensure foreign key uniqueness for 1..1 relationships
   3. Ensure that non-null columns have a value
   4. Ensure that data types agree, particularly for date, integer, decimal data types
   5. Import cleansed data into new MySQL schema
3. Rewrite the application: review the forms, reports and queries of the Access application and re-design them rebuild the application forms and reports using web tools rather than trying to convert the existing application and scripts.
   1. Import MySQL data schema into visual builder tool such as ActiveGrid
   2. Create new web pages that provide graphical interface for application using the ActiveGrid page editor
   3. Define actions that provide needed functionality for application using the ActiveGrid action editor, custom Java or Python code, or web services.

In summary, best practices for Access to MySQL migration require careful migration of data to a new schema along with a requirements-driven rebuilding of the application forms and reports using web-based development tools. MySQL is an increasingly attractive database solution for companies trying to improve the security and data quality of their departmental applications. However a successful migration from Access requires pairing MySQL with a web development tool.

[deanhanson]

In order for Joomla! to work properly, you will need to have a functioning MySQL Database prior to beginning the install process.
Joomla! will generally (depending upon the actual settings of the MySQL Database Server) create a database and the associated tables, provided the details are correctly entered, during the installation process. On occasion however, it may be necessary to create the database ahead of time. Just make a note of the appropriate settings before proceeding with the installation.
When installing it for the first time, Joomla! will ask for the following information:
* The name of your MySQL database - (this may be pre-set on some hosted server set-ups)
* The name of the MySQL host - This is usually called "localhost" if you are installing on a PC or a local server. However, if you are using shared hosting, check with your hosting provider to be sure this is the case.
* A MySQL username - This may have been allocated by your server provider - a local MySQL installation generally has the default administrator username set as "root".
* A MySQL password - This may have been allocated by your server provider - a local MySQL installation generally has the default administrator password set to a blank field, unless this was manually changed during installation of the database server.

You should ask your web server provider if you are not sure about how to go about installing or configuring a MySQL database. They will generally be helpful in this respect. You must obtain the above details before beginning the installation of Joomla!
NOTE: Please check to see whether your server provider has set your hosting account to Safe Mode (this is a setting within the Apache web server. You will be unable to install Joomla! with the Joomla! automatic installer if your server is set to "safe mode".
If this is the case, you will have to manually install Joomla!.

[bkort]

Don't use MySQL. We were going to switch to save licensing cost. We found MySQL to not super stable. Microsoft has a free version of SQL Server now, so save yourself some trouble.

[DaveSawers]

Originally Posted by bkort

We found MySQL to not super stable.

Really? Details and evidence please.

[bkort]

It was my personal experience. We host about a hundred web storefronts. Using MySQL, we'd occasional have corrupted tables and databases. We just don't get that with SQL Server.

[inclus12]

On converting data from MS Access. I've done this several times myself, and
found the easiest way is to:
1. Create your mySQL database tables
2. Define a DSN (ODBC Connection) from your workstation to the mySQL
database
3. Link the mySQL tables into your MS Access database
4. Copy the content over to mySQL

Hope this helps!
Inclus.net - We provide individual and corporate IT training
Educate, Learn & Serve