loading mysql dump or text file into microsoft access 2003

Question

Hello, I am not an expert in MS Access, I have been given a text files basically with over hundred thousand records in it and have been asked to load that text file into the MS access database. As I know MySQL, I uploaded that text file into the MySQL database first using normal Load text file command and it worked as expected. However, when in Access 2003, I go to file menu to Get external data and browse the text file, it just don't do anything after I select the file.

I have tried loading through SQL dump, I created a table, switched to the SQL query but I can not copy paste all of those insert commands in it, as a matter of fact it says value is too big to be entered and I can not paste them one by one as the record number is huge.

Any one help? I will be grateful.

Answer 1

You probably won't be able to import a mySQL dump directly into Access due to syntax differences.

One way would be to use a graphical tool like HeidiSQL to export the mySQL data as CSV that should be easy to get into Access.

Another idea would be importing the dump into mySQL, and then accessing mySQL from Access using the ODBC Connector.

Answer 2

Is this a text file, such as tab delimited, or comma delimited?

Or, is this a file of sql commands?

Access should have no trouble with the text exported files from mySql. However, access can't directly consume a text file of sql commands to insert data.

You can either get the files exported as tab delimited, or if possible, you can even link the access database directly to the mySql server, and use some append/make tables.

Another approach would be to install mySql on your local machine, have it read the sql scripts, and then either export as tab or comma text files, or simply link access directly to that local running instance of MySql and use append queries to pull that data.

However, if that sql dump has each sql command separated by a ; then a simple bit of VBA code could read those sql commands. Something like this could work:

  Sub SqlScripts()

     Dim vSql()     As String
     Dim vSqls      As Variant
     Dim strSql     As String
     Dim intF       As Integer

     intF = FreeFile()
     Open "c:\sql.txt" For Input As #intF
     strSql = Input(LOF(intF), #intF)
     Close intF
     vSql = split(strSql, ";")

     On Error Resume Next
     For Each vSqls In vSql
        CurrentDb.Execute vSqls
        Debug.Print "--->" & vSqls
     Next


  End Sub

I suggest the tab or comma delimited approach. When you do a file->get external data, you should see a wizard startup that allows import of a text (delimited) file.

Answer 3

It's not clear to me what would cause you to be unable to import the text file directly. You'll have to give more details on what you're attempting in Access and where it's going wrong.

But if you've got the data in MySQL already and want to import from there, create a DSN for your MySQL database, then use IMPORT from ODBC, point to the DSN, and you'll be able to import the table from MySQL into Access. It also works with multiple tables at a time.