Autoincrement field handling in MySQL to Access conversion

Ref: question 23134292

With much help I was able to convert a MySQL database to Access.

As I tied to update the Access database in VB6 I discovered that the MySQL autoincrement keys are not transferred with the autoincrement behavior.

The keys in My SQL are defined as follows - in MySQL Export script:

 `itemID` int(6) NOT NULL auto_increment

Is there an easy way to resokve this?
pfefferlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
On the basis that I don't think access will let you change it from a Number to an AutoNumber then the only other way is to create the access tables with AutoNumber fields, link the MySQL tables rather than importing then APPEN from MySQL tables to Access tables.
Cheers, Andrew
0
pfefferlAuthor Commented:
Is APPEN a manual step (ivia the UI) or a method in VB6?

Alternately, would this work?

1. import the MySQL database twice into into database A and B
2. write script in VB6 to drop all value in each table of B (or drop rows via Access UI)
3. modify via Access UI keys of each table in B from INTEGER to COUNTER
4. write script in VB6 for each table to read each row of table in A and add it to same table in B
0
TextReportCommented:
If this is a one off excercise then I would do it cia the UI, anything (almost) is possible with coding but probably not worth the time and effort.

I do have standard code that works for MS SQL Server pasted below and you run this by passing the ODBC Connect String

    ImportTablesAndViews "ODBC;DRIVER={SQL Server};SERVER=PackardBell;APP=My Application;WSID=COMPUTERNAME;DATABASE=Northwind;USER=sa;PWD=", True

You will need to work out your ODBC connect string and the SELECT in MySQL that will return the tables and views.

Cheers, Andrew
Function ImportTablesAndViews(pstrODBC As String, Optional pbooSilent As Boolean) As Boolean
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rst As DAO.Recordset
Dim td As DAO.TableDef
Dim booSilent As Boolean
 
    If IsMissing(pbooSilent) Then
       booSilent = False
    Else
       booSilent = pbooSilent
    End If
    
    Set db = CurrentDb()
    Set qd = db.CreateQueryDef("", "SELECT [name] FROM sysobjects WHERE xtype IN ('U','V') ORDER BY [name]")
    qd.Connect = pstrODBC
    
    Set rst = qd.OpenRecordset()
    Do While Not rst.EOF
       
       DoCmd.TransferDatabase acImport, "ODBC", pstrODBC, acTable, rst!Name, rst!Name, True
       rst.MoveNext
    Loop
    rst.Close
    
    Application.RefreshDatabaseWindow
 
    If Not pbooSilent Then
       MsgBox "All tables and Views have been imported"
    End If
    
    ImportTablesAndViews = True
    
End Function

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

pfefferlAuthor Commented:
Thanks = I'll try it.

What is [name] in

    Set qd = db.CreateQueryDef("", "SELECT [name] FROM sysobjects WHERE xtype IN ('U','V') ORDER BY [name]")

Is it a dtabase name?
0
TextReportCommented:
Beleive it or not it is a column name in sysobjects that holds the name of the tables, views etc, talk about not following your own recomendations Microsoft.
Cheers, Andrew
0
pfefferlAuthor Commented:
HI Andrew,

a. This assumes that prior to calling this routine the Access  DB is open and the OBD link to MySql is not?
b. Don't see the DAO control in list of VB6 controls. What is it called in list of Components?

Thanks.
0
TextReportCommented:
Sorry, this is code to be ran from VBA in access not from VB
Cheers, Andrew
0
pfefferlAuthor Commented:
I never woite VBA scripts - there is always a first time ... I'll take a crack at it.

Does your routine require any additional procedures to run - e.g. to cal it, etc.?

When it completes it sets up the Access Database - stru7crtures and values copied from MySQL (OBD) and the MySQL  Integer indexes are changed to autoincrement counters?
0
TextReportCommented:
In your access MDB file create a New Module
Add Option Explicit on line 2 underneath Option Compare Database
Past the code into the module below Option Explicit.
Go to Tools Menu References and select Microsoft DAO 3.6
It should now work.
Cheers, Andrew
0
pfefferlAuthor Commented:
First four lines (above ) done and code looks as shown below *****
When I press F5 it asks for Macro name and I get a blank form. How to proceed?


*****

Option Compare Database
Option Explicit

Function ImportTablesAndViews(pstrODBC As String, Optional pbooSilent As Boolean) As Boolean
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rst As DAO.Recordset
Dim td As DAO.TableDef
Dim booSilent As Boolean
 
    If IsMissing(pbooSilent) Then
       booSilent = False
    Else
       booSilent = pbooSilent
    End If
   
    Set db = CurrentDb()
    Set qd = db.CreateQueryDef("", "SELECT [name] FROM sysobjects WHERE xtype IN ('U','V') ORDER BY [name]")
    qd.Connect = pstrODBC
   
    Set rst = qd.OpenRecordset()
    Do While Not rst.EOF
       
       DoCmd.TransferDatabase acImport, "ODBC", pstrODBC, acTable, rst!Name, rst!Name, True
       rst.MoveNext
    Loop
    rst.Close
   
    Application.RefreshDatabaseWindow
 
    If Not pbooSilent Then
       MsgBox "All tables and Views have been imported"
    End If
   
    ImportTablesAndViews = True
   
End Function
0
TextReportCommented:
This is a function that needs parameters so can not be ran from F5, goto the immediate window (CTRL-G) and type it in there as is or with ? and brackets if you want to see the return value


?ImportTablesAndViews("ODBC;DRIVER={SQL Server};SERVER=PackardBell;APP=My Application;WSID=COMPUTERNAME;DATABASE=Northwind;USER=sa;PWD=", True)

Cheers, Andrew
0
pfefferlAuthor Commented:
I am almost "there". (Thank you for also opening the door to VBA script writing ...)

When I run the modified connection string I get:

"Compile error. Expected variable or procedure, not module".

Does APP= and  WSID= have to be set any particular value?

This is what I ran:

?ImportTablesAndViews("ODBC;DRIVER={MySQL ODBC 3.51 Driver;SERVER=localhost;APP=My Application;WSID=DESKTOPPC;DATABASE=Northwind;USER=user2;PWD=", True)

0
pfefferlAuthor Commented:
After modifying the script as shown after ******** below got past error

"Compile error. Expected variable or procedure, not module".

and when I run function main() now get message

"Run-time error 3146
OBDC--call failed"

Would appreciate advice on how to proceed.

********

Option Compare Database
Option Explicit

Function ImportTablesAndViews(pstrODBC As String, Optional pbooSilent As Boolean) As Boolean
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rst As DAO.Recordset
Dim td As DAO.TableDef
Dim booSilent As Boolean
 
    If IsMissing(pbooSilent) Then
       booSilent = False
    Else
       booSilent = pbooSilent
    End If
   
    Set db = CurrentDb()
    Set qd = db.CreateQueryDef("", "SELECT [name] FROM sysobjects WHERE xtype IN ('U','V') ORDER BY [name]")
    qd.Connect = pstrODBC
   
    Set rst = qd.OpenRecordset()
    Do While Not rst.EOF
       DoCmd.TransferDatabase acImport, "ODBC", pstrODBC, acTable, rst!Name, rst!Name, True
       rst.MoveNext
    Loop
    rst.Close
   
    Application.RefreshDatabaseWindow
 
    If Not pbooSilent Then
       MsgBox "All tables and Views have been imported"
    End If
   
    ImportTablesAndViews = True
   
End Function

Function main()
    Static lConnectionString As String
   
    MsgBox "Started"
     
    lConnectionString = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;APP=My Application;WSID=DESKTOPPC;" + _
        "DATABASE=Northwind;USER=user2;;PWD="

    ImportTablesAndViews lConnectionString, False

    MsgBox "Ended"
End Function

0
TextReportCommented:
The 3146 error is Access trapping a server error and you need to loop through the errors collection to get the actual error from the server. I have included sample error handling that will trap your server error for you.

Cheers, Andrew
Function ImportTablesAndViews(pstrODBC As String, Optional pbooSilent As Boolean) As Boolean
On Error GoTo ImportTablesAndViews_Error
Dim e As Error
Dim strErrorMSG As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rst As DAO.Recordset
Dim td As DAO.TableDef
Dim booSilent As Boolean
 
    If IsMissing(pbooSilent) Then
       booSilent = False
    Else
       booSilent = pbooSilent
    End If
    
    Set db = CurrentDb()
    Set qd = db.CreateQueryDef("", "SELECT [name] FROM sysobjects1 WHERE xtype IN ('U','V') ORDER BY [name]")
    qd.Connect = pstrODBC
    
    Set rst = qd.OpenRecordset()
    Do While Not rst.EOF
       
       DoCmd.TransferDatabase acImport, "ODBC", pstrODBC, acTable, rst!Name, rst!Name, True
       rst.MoveNext
    Loop
    rst.Close
    
    Application.RefreshDatabaseWindow
 
    If Not pbooSilent Then
       MsgBox "All tables and Views have been imported"
    End If
    
    ImportTablesAndViews = True
    
ImportTablesAndViews_Exit:
    Exit Function
    
ImportTablesAndViews_Error:
    strErrorMSG = "An Unexpected error has ocurred in ImportTablesAndViews" & vbCrLf & vbCrLf & "Error" & vbTab & "Description" & vbCrLf
    Select Case err
        Case 3146 'ODBC Call Failed
             For Each e In Errors
                 strErrorMSG = strErrorMSG & e.Number & vbTab & e.Description & vbCrLf
             Next
             Select Case MsgBox(strErrorMSG, vbCritical + vbRetryCancel)
                Case vbCancel: Resume ImportTablesAndViews_Exit
                Case vbRetry: Resume
             End Select
             
        Case Else
             strErrorMSG = strErrorMSG & err & vbTab & err.Description
             Select Case MsgBox(strErrorMSG, vbCritical + vbRetryCancel)
                Case vbCancel: Resume ImportTablesAndViews_Exit
                Case vbRetry: Resume
             End Select
    End Select
        
    
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pfefferlAuthor Commented:
Made some progress after the suggested change. This time theror message said there is a syntax error  on (since that is SQL Sever syntax:)

Set qd = db.CreateQueryDef("", "SELECT [name] FROM sysobjects1 WHERE xtype IN ('U','V') ORDER BY [name]")

Didn't find MySql syntax for this statement - and to progress for now replaced the generalized loop with one import statement per table to be imported - naming each table as shown after ******** below.

The table structures WERE imported into Access, but

a. table keys remained INTEGER rather than COUNTER, In MySQL they were defined as INTEGER, Primary Key. Auto Increment

b. table values were not imported.

Any further advice would be appreciated.

By the way, what is the meaning of the [ .. ] notation ---- as in [name]. Is this a notion with broader applicability?

Thank you.


********

Function ImportTablesAndViews(pstrODBC As String, Optional pbooSilent As Boolean) As Boolean
    On Error GoTo ImportTablesAndViews_Error
    Dim e As Error
    Dim strErrorMSG As String
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim td As DAO.TableDef
    Dim booSilent As Boolean
 
    If IsMissing(pbooSilent) Then
       booSilent = False
    Else
       booSilent = pbooSilent
    End If
   
    DoCmd.TransferDatabase acImport, "ODBC", pstrODBC, acTable, "employer", "employer", True        
    DoCmd.TransferDatabase acImport, "ODBC", pstrODBC, acTable, "employee", "employee", True
    DoCmd.TransferDatabase acImport, "ODBC", pstrODBC, acTable, "address", "address", True
    DoCmd.TransferDatabase acImport, "ODBC", pstrODBC, acTable, "resource", "resource", True
    DoCmd.TransferDatabase acImport, "ODBC", pstrODBC, acTable, "contact", "contact", True
'      etc,

    Application.RefreshDatabaseWindow
 
    If Not pbooSilent Then
       MsgBox "All tables and Views have been imported"
    End If
   
    ImportTablesAndViews = True
   
ImportTablesAndViews_Exit:
    Exit Function
   
ImportTablesAndViews_Error:
    strErrorMSG = "An Unexpected error has occurred in ImportTablesAndViews" & vbCrLf & vbCrLf & "Error" & vbTab & "Description" & vbCrLf
    Select Case Err
        Case 3146 'ODBC Call Failed
             For Each e In Errors
                 strErrorMSG = strErrorMSG & e.Number & vbTab & e.Description & vbCrLf
             Next
             Select Case MsgBox(strErrorMSG, vbCritical + vbRetryCancel)
                Case vbCancel: Resume ImportTablesAndViews_Exit
                Case vbRetry: Resume
             End Select
             
        Case Else
             strErrorMSG = strErrorMSG & Err & vbTab & Err.Description
             Select Case MsgBox(strErrorMSG, vbCritical + vbRetryCancel)
                Case vbCancel: Resume ImportTablesAndViews_Exit
                Case vbRetry: Resume
             End Select
    End Select
           
End Function
0
TextReportCommented:
The True at the end of the TransferDatabase if for Structure Only, please change these to false.
The AutoIncrement is a problem and depending on the version of Access you may be able to change the field to AutoNumber when the table is populated, if not, then you will need to change the field before the data is populated and then APPEND the data. This can be achieved by

Create the table
Modify the structure
Attache the MySQL tables
Run APPEND query

Cheers, Andrew
0
pfefferlAuthor Commented:
Are

Attache the MySQL tables
Run APPEND query

via VB or the UI?
0
TextReportCommented:
You can try changing acImport to acLink in the DoCmd.TransferDatabase
The Append you would need to create teh append queries and then you can do

currentdb.execute "qappMyQuery", dbfailonerror

Cheers, Andrew
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.