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?
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?
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
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=COMPUTERN AME;DATABA SE=Northwi nd;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
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
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
ASKER
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?
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
Cheers, Andrew
ASKER
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.
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
Cheers, Andrew
ASKER
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?
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
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
ASKER
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(pstrO DBC 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.RefreshDatabas eWindow
If Not pbooSilent Then
MsgBox "All tables and Views have been imported"
End If
ImportTablesAndViews = True
End Function
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(pstrO
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.RefreshDatabas
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("ODB C;DRIVER={ SQL Server};SERVER=PackardBell ;APP=My Application;WSID=COMPUTERN AME;DATABA SE=Northwi nd;USER=sa ;PWD=", True)
Cheers, Andrew
?ImportTablesAndViews("ODB
Cheers, Andrew
ASKER
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("ODB C;DRIVER={ MySQL ODBC 3.51 Driver;SERVER=localhost;AP P=My Application;WSID=DESKTOPPC ;DATABASE= Northwind; USER=user2 ;PWD=", True)
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("ODB
ASKER
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(pstrO DBC 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.RefreshDatabas eWindow
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;A PP=My Application;WSID=DESKTOPPC ;" + _
"DATABASE=Northwind;USER=u ser2;;PWD= "
ImportTablesAndViews lConnectionString, False
MsgBox "Ended"
End Function
"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(pstrO
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.RefreshDatabas
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;A
"DATABASE=Northwind;USER=u
ImportTablesAndViews lConnectionString, False
MsgBox "Ended"
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(pstrO DBC 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.RefreshDatabas eWindow
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
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(pstrO
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.RefreshDatabas
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
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
ASKER
Are
Attache the MySQL tables
Run APPEND query
via VB or the UI?
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
The Append you would need to create teh append queries and then you can do
currentdb.execute "qappMyQuery", dbfailonerror
Cheers, Andrew
Cheers, Andrew