Moving from Microsoft Access to MySQL

Transferring from Microsoft Entry to MySQL

Corporations that use MS Entry finally begin fighting its restrictions. Proper now they arrive with another: switch information repositories right into a system that gives higher efficiency and consistency and that is extra versatile in the way in which it permits data to be utilized. MySQL provides the options you’ll require to comply with this program. On this article you will notice why you must consider using MySQL somewhat than Entry for information administration, and provides pointers for transferring data out of Microsoft Entry and into MySQL as you put together to essentially make the swap.

Why you must Transfer from Microsoft Entry to MySQL Utility of MySQL as a storage supervisor for Entry provides a number of benefits. The primary is that you need to use your information in extra methods when it’s not locked into Entry. Different variations pertain particularly within the case that you simply plan to maintain utilizing Entry because the person interface to your data. Deployment of knowledge. In case your data resides in MySQL, you are at liberty to maintain utilizing it from Entry in order for you, nevertheless a a lot of different potentialities open up on the similar time. Any sort of MySQL shopper could make use of the information, not solely Entry. For example, different people could make use of the data inside the usual MySQL shopper applications or from GUI-based functions. Your database additionally will get extra accessible over the web. MySQL integrates simply with Internet servers like Apache by any of plenty of languages, together with Perl, PHP, Python, and many others. This allows you to supply a Internet interface in your database with all of the language which you resolve on. Moreover, the interface might be accessed by browsers on numerous sorts of machines, offering a platform-independent entryway to your data. Each certainly one of these elements may be discovered without charge. A number of-user entry. Though Entry supplies some information sharing capabilities, that is not actually its energy. It has the texture of a single-user information supervisor created for native use. MySQL, alternatively, simply handles many simultaneous customers. It was developed from the ground-up to function in a networked atmosphere and likewise to be thought of a multiple-user system that is very efficient at servicing giant numbers of shoppers. Coping with giant databases. MySQL can handle quite a few megabytes of knowledge, and way more. Want to strive that with Entry? Safety. When Entry tables are saved regionally, now you can stroll near your Home windows machine, launch Entry, and acquire your tables. It is easy to assign a database password energy, nevertheless, many individuals routinely fail to take action. At any time when your tables are saved in MySQL, the MySQL server manages safety. Anybody making an attempt to entry your data ought to know the suitable username and password to connect with MySQL. Backup administration. Once you work with an organization that helps many Entry customers, migrating information to MySQL provides a profit for backups and information integrity. With Entry databases centralized in MySQL, they’re all supported with the common MySQL backup processes that exist already in your web site. If particular person Entry customers every retailer their information regionally, backup might be tougher: 50 customers means 50 database backups. Whereas many websites handle this difficulty with using community backups, others cope with it just by making backups the duty of particular person machine owners–which nevertheless generally means no backups in any respect. Native disk storage necessities. Native Entry database recordsdata grow to be smaller, just because the objects in tables aren’t saved internally, they’re saved as hyperlinks within the MySQL server the place tables reside. This results in decreased native disk utilization. And, if you wish to disperse a database, much less data must be copied. (Clearly, anybody you distribute the database to additionally might want to have quick access within the MySQL server.) Value. MySQL may be acquired without charge. Entry can not. Providing different methods of constructing use of your database (together with by a Internet interface) may help to eradicate your reliance on proprietary software program and reduce your software program acquisition and licensing prices. {Hardware} decisions. MySQL operates on a number of platforms; Entry is a single-platform software. If you have to use Entry, your accessible alternative of {hardware} is set for you. Methods of Transferring Databases from Microsoft Entry to MySQL Sometimes, to maneuver data from Entry to MySQL, you to begin with copy the data of your tables from an Entry database to the MySQL server. (To execute the entire technique of transferring the tables to MySQL, you possibly can choose proper from a number of methods, described beneath.) In the event you intend to hold on utilizing Entry for the interface to your information, the following factor after transferring the tables will probably be to exchange all of them with hyperlinks: Delete the tables saved in your Entry database, set up an ODBC connection from Entry to the MySQL server, and recreate the tables as hyperlinks to the MySQL tables. Except you dont intend to maintain utilizing Entry, you need not create any hyperlinks. Some switch strategies require making an ODBC hyperlink with the MySQL server. Only for this you need to use MySQL Connector/ODBC, the MySQL-specific ODBC driver. Telling Microsoft Entry to Export Its Distinctive Tables One technique of transferring information from Entry to MySQL is by utilizing the export characteristic provided by Entry itself to jot down out the contents of every desk as a textual content file. Every file then might be loaded into MySQL by utilizing a LOAD DATA assertion and even the mysqlimport command-line utility. Assume that you simply export a desk mytable proper right into a file mytable.txt utilizing CSV (comma separated values) format, and also you need to import it right into a desk named mytable in a MySQL database named mydb. You’ll be able to invoke the mysql program, then difficulty a LOAD DATA assertion to import the file resembling this: C:> mysql mydb mysql> LOAD DATA LOCAL INFILE ‘mytable.txt’ -> INTO TABLE mytable -> FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ -> LINES TERMINATED BY ‘rn’; On the other hand, use mysqlimport in the command line (type the command all on one line): C:> mysqlimport –local –fields-terminated-by=, –fields-enclosed-by='”‘ –lines-terminated-by=’rn’ mydb mytable.txt Everytime you present connection parameters together with the hostname, username, or password, listing them on the mysql or mysqlimport command line earlier than the database identify: C:> mysqlimport –local –fields-terminated-by=, –fields-enclosed-by='”‘ –lines-terminated-by=’rn’ -h some_host -p -u some_user mydb mytable.txt The good thing about this course of is that it requires no particular conversion instruments. It may be utilized to create information file sorts even on machines that possess no MySQL assist. (If you do not have the MySQL shopper applications arrange in your Entry machine, create the information recordsdata, then copy all of them to a different machine the place the MySQL applications are put in and cargo the recordsdata into MySQL from there.) The draw back is the truth that MySQL tables should exist already earlier than you possibly can load information into them, due to this fact it’s essential to difficulty the proper CREATE TABLE statements your self. For the instance simply proven, which means you need to have already got arrange the desk mytable within the mydb database earlier than utilizing both LOAD DATA or mysqlimport. Converters That Generate Intermediate Recordsdata This method requires a product or device that reads Microsoft Entry desk and converts it into a number of recordsdata containing SQL statements that create vacation spot MySQL desk and cargo information into it. Then you definately execute the intermediate SQL file or recordsdata with the mysql program. A number of free converters that really work this manner may be discovered, as a result of each variations takes the type of an Entry module: exportsql.txt Works along with Entry95, Entry97, Entry2000. Exports all tables in a database, producing one file containing DROP TABLE statements (for many who wish to take out MySQL tables created throughout an earlier information switch train) and the opposite file containing CREATE TABLE and INSERT statements for creating and loading the tables. The recordsdata are written to the C:TEMP listing. access_to_mysql.txt Exports all tables in a database right into a file C:TEMPmysqldump.txt containing DROP TABLE, CREATE TABLE, and INSERT statements to drop any present MySQL tables and recreate them. Much less subtle than exportsql.txt on the subject of sort conversion and dealing with of particular characters. mdb2sql.bas Entry97 solely. Exports chosen tables to recordsdata in a listing which you resolve on. Writes an information file for every chosen desk, plus one SQL script containing CREATE TABLE statements for creating the tables and LOAD DATA statements for importing the information recordsdata into them. Instantly after adopting the export strategy of a converter that generates intermediate SQL recordsdata from Entry tables, you will find yourself getting a couple of recordsdata that must be executed whereas utilizing the mysql program, as follows. Let’s assume that you have to create tables in a database named mydb, it’s doable to hold out a SQL file file.sql resembling this: C:> mysql mydb < file.sql Whenever you offer connection parameters, list them on the command line before the database name: C:> mysql -h some_host -p -u some_user mydb < file.sql Converters That Carry out Direct Switch Of Information Plenty of conversion instruments can transfer information straight from an Entry database into MySQL. That is, they create the MySQL tables to fit your wants and cargo the information into them additionally. This avoids some great benefits of any intermediate recordsdata. Alternatively, such instruments require that it is possible for you to to attach with the MySQL server within the machine which your Entry information is saved. This standards is certainly happy if you happen to occur to put in MySQL by yourself Entry machine. One amongst such instruments is developed by Clever Converters. It helps all variations of Microsoft Entry and MySQL operating on Linux/Unix/Home windows platforms. The software program permits to filter information for conversion through SELECT-queries, to change construction of the ensuing MySQL tables and likewise to synchronize MySQ