Solved

Export MS Access tables to SQL files

Posted on 2013-12-04
6
426 Views
Last Modified: 2013-12-05
I have a MS Access database on a PC. I want to export some (not all) of the tables in Access to a sql formatted file so I can sub-sequentially import them to a MySQL database on a web server; I know all bout doing the import part.

How can I export data from Access to .sql files?
0
Comment
Question by:Richard Korts
  • 3
  • 3
6 Comments
 
LVL 36

Expert Comment

by:PatHartman
ID: 39696836
.sql is not a standard file format.  You're going to need to tell us what you want it to contain.  In any event, since it isn't standard, there is no built in export for it so you would need to write code to make it happen.

Can't you import .csv or .txt files into MySQL to transfer data?  Those are standard and can be created using point and click in the GUI or with a single line of VBA code.
0
 

Author Comment

by:Richard Korts
ID: 39697214
PatHartman

Yes, I can use txt tab delimited. Can you point me in the right direction?

Thanks
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39697326
Use the External Data tab if you want to do this through the GUI.  To do it in code, you would use the TransferText Method.  Help is very good once you know what you need.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Richard Korts
ID: 39698765
PatHartman

See attached. It thinks I want to IMPORT not EXPORT. How do I tell it (Access) that IT want to EXPORT?
export-access.jpg
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39699618
Sorry, I do mostly imports so that was a reflexive answer.  

To export, right click on the table or query and choose Export and then the file type.  The export dialog will open and you may need to push the "Advanced" button to change certain options.
0
 

Author Closing Comment

by:Richard Korts
ID: 39699912
Works great, thanks.

Years ago I worked with Access a lot; seems odd that Export & Import are not "mirrors" of each other.

But it works!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question