Solved

Export MS Access tables to SQL files

Posted on 2013-12-04
6
433 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 37

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 37

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 37

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access query 16 59
SQL Function 4 38
Access VBA How To Check a Table To See If The Field Value Is Null 6 40
Outlook mail to Access 8 21
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

732 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