MySQL Forums
Forum List  »  Microsoft Access

Access queries with custom functions conversion to MySQL query
Posted by: Marco Canavese
Date: October 17, 2011 12:56AM

Hi,
I'm a new member here asking for some help.
I'm working on a MS Access database conversion to MySQL.
Using the "old" but still very useful MySQL Migration toolkit I did get a fully working conversion of my tables (both data and structure).
I'm now approaching the queries conversion and, apart from syntax peculiarities I hope to solve, I will have to face one major task. Some of my queries call some user defined functions (VBA functions).

How do I manage these kind of stuff?

Here's the VBA code of such routines, if helps:

First type of user defined function I use is: (see http://www.databasejournal.com/features/msaccess/article.php/3643596/Queries-On-Steroids--Part-I.htm for details)

Public Function Concatena(ByVal CodAtt As String) As String

Dim rs As DAO.Recordset
Dim strQuery As String
Dim strOutput As String

' Open a recordset with SSA list related to given Cod Att
strQuery = "SELECT [Tbl_Scoop_UO_Attivita_SSA].[Denominazione_SSA_Nome_Applicativo] FROM Tbl_Scoop_UO_Attivita_SSA WHERE [Tbl_Scoop_UO_Attivita_SSA].[Codice_Attivita_BC] = " & "'" & CodAtt & "'"
Set rs = CurrentDb.OpenRecordset(strQuery, dbOpenForwardOnly, dbReadOnly)

' Recordset loop
With rs
Do Until .EOF
' Names concat
strOutput = strOutput & .Fields("Denominazione_SSA_Nome_Applicativo").value & "; "

' Move to next line
.MoveNext
Loop

' recordset close
.Close
End With

' Delete recordset
Set rs = Nothing

Concatena = strOutput

End Function


Second type of user defined function I use is:

Public Function AlgoritmoStringa(ByVal Stringa As Variant) As String

Dim k As Integer
Dim appo As String

appo = ""

If IsNull(Stringa) Or Stringa = "" Then
appo = "000"
Else
For k = 1 To Len(Stringa)
If k = 1 Or k = 5 Or k = 10 Or k = 20 Or k = 30 Or k = 40 Or k = 60 Or k = 80 Or _
k = 100 Or k = 150 Or k = 200 Or k = Len(Stringa) Then
If Mid(Stringa, k, 1) = " " Or Mid(Stringa, k, 1) = """" Or Mid(Stringa, k, 1) = "'" Then
appo = appo & "-"
Else
appo = appo & Mid(Stringa, k, 1)
End If
End If
Next
appo = appo & Format(Len(Stringa), "000")
End If

AlgoritmoStringa = appo

End Function

I then call these functions within my queries with something like Concatena("parameter name"), etc...

Any help would be really appreciated for a completely MySQL newbie.

Thanks a lot.

Marco

Options: ReplyQuote


Subject
Views
Written By
Posted
Access queries with custom functions conversion to MySQL query
4114
October 17, 2011 12:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.