Link to home
Start Free TrialLog in
Avatar of pfefferl
pfefferl

asked on

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?
Avatar of TextReport
TextReport
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of pfefferl
pfefferl

ASKER

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
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

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?
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
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.
Sorry, this is code to be ran from VBA in access not from VB
Cheers, Andrew
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?
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
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
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
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)

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

ASKER CERTIFIED SOLUTION
Avatar of TextReport
TextReport
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
Are

Attache the MySQL tables
Run APPEND query

via VB or the UI?
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