We help IT Professionals succeed at work.

Using Access to process data in MySQL tables

326 Views
Last Modified: 2012-08-13
I have a large table which contains 1.6 million rows. I need to do some string manipulation on one of the columns that is (probably) too complex to be done just using SQL. I have used VBA ADO in Access to loop through each record in the table, carry out the string processing and then update the record. With a smaller "test" table in Access this works fine but when I tried it using the large table in MySQL  the first attempt ran painfully slowly (processing approx 100 records in 10 minutes). Subsequent attempts generated an errror message stating that another process was trying to update the record. I have tried to process the large table just using the Access database tables but it just seems to be too large and keeps failing with exceeding max record lock errors, when I temporarily increase the max record locks in the registry it process more records but then fails with a database error. I would like to find out how to processing large amounts of data in MySQL using Access but I am obviosly not using the correct method.

Any assistane with this would be appreciated

Thanks

John
Comment
Watch Question

Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
"I need to do some string manipulation on one of the columns"
Do you mean getting one record, work on that column, and repeat the same process with another record?
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Can you let us know what sort of manipulation you're doing? Perhaps show us the VBA code you're running?

Author

Commented:
I am reading a UK post code from one column (e.g. BS21 7QA) capitalising it and removing any chracter or space that is not A-Z or 0-9 I am then writing it back to another column. The table is all the UK postcodes with geocoding data. I realise that I could sort of do this just in SQL but I would like to know how to use Access ADO with MySQL tables as I have other applications where I think it would not be possible to do the processing in SQL
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
What about finding the min and max record id.
Divide the range to reasonable shunks of records, say 1000 or 10000 parts.
Process each part in a loop.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Okay - as I asked earlier, can you show us the code you're using?

Author

Commented:
Following is the code:

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strPostCode As String

strSQL = "select * from tblcodepointdata" 'This is the MySQL table
Set rst = New ADODB.Recordset
        With rst
            .ActiveConnection = CurrentProject.Connection
            .CursorType = adOpenDynamic
            .LockType = adLockOptimistic
            .Open strSQL
        End With

Do Until rst.EOF

strPostCode = Nz(rst!PostCode, "N/A")

Call FormatPostCode(strPostCode)

rst!PostCodeFormatted = strPostCode

rst.Update
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
MsgBox "Processing Completed"


Sub FormatPostCode(strPostCode As String)
Dim l As Integer
Dim iASC As Integer
Dim strCHR As String
Dim strTemp As String

l = Len(strPostCode)
strPostCode = UCase(strPostCode)
For i = 1 To l
strCHR = Mid(strPostCode, i, 1)
iASC = Asc(strCHR)
If iASC > 64 And iASC < 91 Then
strTemp = strTemp & Chr(iASC)
End If

If iASC > 47 And iASC < 58 Then
strTemp = strTemp & Chr(iASC)
End If

Next i
strPostCode = strTemp
strTemp = Empty
End Sub
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
"processing approx 100 records in 10 minutes"
Try to select only 100 records and check the speed. This will show if splitting the recordset may help.

strSQL = "select * from tblcodepointdata WHERE id between " & 1 & " AND " & 100
' ammend the where clause to include your field name and criteria.

Author

Commented:
I have overcome the problem now by doing the changes using SQL only. However I would still like to know if it is possible to use DAO to manipulate data in a MySQL table as I am sure that I will have a situation where I need to.

Any further info on this would be appreciated

John
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.