.mdb to mySQL 3

[brownfox]

I have a table in an access2000 database(.mdb). Does anyone know how I can convert it and upload it to mySQL?

 

[DRJ478]

Probably the easiest is to go through a comma delimited file .csv and import it into MySQL. MS Access has several options to export the table data to a csv file. Choose the option that has column headers.

PHPMyAdmin has several import options that make import easy. If you have PHPMyAdmin use it.

It might be necessary to adjust the column types after importing the data.

 

[sleipnir214]

Here's a comple of possibilties:

Export the Acess tables as CSV files, and import them into MySQL using the LOAD DATA command.

Create the MySQL tables, link to them in Access, and transfer the data via ODBC.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[thevillageinn]

I recently did a similar thing, except instead of simply exporting the file as CSV, I created it more like an SQL Dump File so that I could easily link it as an external SQL file in the phpMyAdmin tool.

While my source DB is Access 97, and the code is probably a bit sloppy, I'd be happy to share it, and if you are familiar with programming in VBA, you'd be able to customize it to suit your needs if desired.

 

[brownfox]

Unfortunately I've never programmed in VBA but then I never used php until a week ago, so we never stop learning I guess. I would be interested in your code. I converted the db I was using (access 2000)into csv, but using LOAD DATA was a nightmare - only portions of the file were accepted into mySQL. It took me 2 days to get the whole thing in, and there were only 174 records to begin with. Any help would be greatly appreciated!

 

[thevillageinn]

Here's the code that works from the "Click" event of a form's command button. The code relies on some things to work accurately since my data was the basis I coded for it.
1. There are no " ' " (single quotes) in the data, that will error mySQL.
2. The table name that is called for has no spaces in it.
3. You are not specifying specialized data types for any of the fields in the new mySQL table.
4. You have a text box on the form where the command button resides (txtTableName)
5. You are on a system with User profiles (NT, 2000 or XP) since the dump file will be created to your desktop.

I can forward the entire MDB if you desire. Also in order for this to work in Access 2000, you will have to included DAO in the references in the VBA editor.

I tried to add a few comments, but it's pretty ugly. If all this is totally unfamiliar the MDB is probably a better choice. (e-mail: 'thevillageinnATdanjcraig.com')

Private Sub cmdSqlDumpFile_Click()
On Error GoTo Err_cmdSqlDumpFile_Click

Dim rs As Recordset
Dim db As Database
Dim i As Integer
Dim txtDumpName As String
Dim strTableName As String
Dim strHeader As String
Dim strDataLine As String

strTableName = txtTableName
If strTableName = "" Then
    MsgBox "Please enter the name of the table which contains the data to dump into an SQL file"
Else

'Get the desired file name from user input
txtDumpName = InputBox("Please enter the desired name of the SQL dump file... ")
If txtDumpName = "" Then
    MsgBox "The value you have entered is invalid...please try again"
    Exit Sub
Else

Set db = CurrentDb
Set rs = db.OpenRecordset(strTableName)

rs.MoveLast
rs.MoveFirst

'prepare values for the Header
Dim tdfTable As TableDef
Dim iNumFields As Integer
Dim strFieldNames() As String
ReDim strFieldNames(rs.Fields.Count) 'declare the strFieldnames array with the correct number of values for the table

For iNumFields = 0 To (rs.Fields.Count - 1)
    strFieldNames(iNumFields) = rs.Fields(iNumFields).Name
Next iNumFields

'create the header
strHeader = "CREATE TABLE " & strTableName & "(" & vbCrLf
    For iNumFields = 0 To (rs.Fields.Count - 1)
        strHeader = strHeader & strFieldNames(iNumFields) & " varchar(" & rs.Fields(iNumFields).Size & ") NOT NULL"
            If iNumFields < (rs.Fields.Count - 1) Then
                strHeader = strHeader & &quot;,&quot; & vbCrLf
            Else
                strHeader = strHeader & &quot;); &quot; & vbCrLf
            End If
    Next iNumFields
    
'open the text file and begin writing to it line by line
Open (&quot;C:\Documents and Settings\&quot; & Environ(&quot;username&quot;) & &quot;\Desktop\&quot; & txtDumpName & &quot;.sql&quot;) For Output As #1
    Print #1, strHeader
    For i = 1 To rs.RecordCount
        strDataLine = &quot;INSERT INTO &quot; & strTableName & &quot; VALUES (&quot;
            For iNumFields = 0 To (rs.Fields.Count - 1)
                strDataLine = strDataLine & &quot;'&quot; & rs.Fields(iNumFields) & &quot;'&quot;
                    If iNumFields < (rs.Fields.Count - 1) Then
                        strDataLine = strDataLine & &quot;, &quot;
                    Else
                        strDataLine = strDataLine & &quot;);&quot; & vbCr
                    End If
            Next iNumFields
        Print #1, strDataLine
        rs.MoveNext
    Next i
    
    'close the text file
    Close #1
    
rs.Close
Set db = Nothing

End If
End If

MsgBox &quot;File Creation complete...&quot;
    
Exit_cmdSqlDumpFile_Click:
    Close #1  'make sure the text file is closed or incur more errors
    Exit Sub

Err_cmdSqlDumpFile_Click:
    MsgBox Err.Description
    Resume Exit_cmdSqlDumpFile_Click
    
End Sub

 

[binky]

I too am in the middle of converting from Access to MySQL. The easiest way I have found is...

1. Download and install MyODBC.
2. Create the empty database in MySQL
3. Create an ODBC to this empty database
4. Open your access db and choose -> file->export In the file dialog open file types and choose ODBC(). From those listed select the one you created in step 3.

That's it. Make sure to index, and auto-increment where necessary.

HTH

Binky :
><>

 

[sleipnir214]

There are also a couple of good MySQL management tools that run on Win32.

Take a look at MySQL-Front (well-designed and free, but no longer underdevelopment:

http://mysqlfront.sstienemann.de/)

or EMS MySQL Manager (well-designed and still under development, but not free. Does have a 30-day free trial.

http://ems-hitech.com/mymanager/)

Either of these will open a Access database and dump the data into a MySQL database.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[thevillageinn]

I had tried using myAccess long ago, and never had very good results with it. It was buggy and slow.

MyODBC didn't give me the results I was desiring either...also slow. Plus many people don't care for ODBC.

In my experience, loading the data from an .sql file proved to be quick, accurate and very easy providing the host supplies a simple GUI front end to mySQL, phpMyAdmin is what I'm familiar with. Creating tables and filling them with data is quite easy with SQL.

Thanks for the additional links though.

 

[sleipnir214]

MyAccess?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[hisham]

i use AccessDUMP, it is a simple Graphical User Interface that creates MySQL Dump files (*.sql) from MS Access databases. and it is free.

http://www.intranet2internet.com/public/default.asp?PAGE=software&DETAILS=DUMP

 

[thevillageinn]

myAccess...

http://www.accessmysql.com/

it appears to be in a new version since I had used it, maybe it's better, but I believe it's shareware so you get prompts at timed intervals or after a certain # of records affected.

 

[thevillageinn]

regarding Access DUMP...had I known it existed I probably would have used it, seems like a handy utility, since it's what I did, but better executed.