I've written a COM Add-In for Access 2000. While most of it seems to work OK, I'm having trouble executing any SQL statements (using a Command object) that contain custom functions, e.g.
strSQL = "UPDATE MyTable " & _
"SET MyField = StrConv(MyField, vbUpperCase)"
will work, as StrConv is a built-in function, but:
strSQL = "UPDATE MyTable " & _
"SET MyField = MyFunc(MyField)"
doesn't, and neither does:
strSQL = "UPDATE MyTable " & _
"SET MyField = Replace(MyField, "A", "a""
If I set a reference to the COM Add-In from within Access, I can use the function in the immediate window, but not in any queries. If, however, I copy/paste the same code into a code module within the database, then I can use the SQL statements like the above from within my Add-In. I can also create a seperate 'Code Library' database and programatically set a reference to it when my Add-In connects. This enables me to use the functions as I intend, but relies on an extra file, and I am trying to keep the Add-In self-contained.
Currently, I am having to work around the issue by opening a recordset and updating each record individually, which takes far longer to run (minutes rather than seconds)
So, what I'm wondering is, is there a way to declare these functions within my Add-In, so that I can use them as intended.
rgds
Andy
strSQL = "UPDATE MyTable " & _
"SET MyField = StrConv(MyField, vbUpperCase)"
will work, as StrConv is a built-in function, but:
strSQL = "UPDATE MyTable " & _
"SET MyField = MyFunc(MyField)"
doesn't, and neither does:
strSQL = "UPDATE MyTable " & _
"SET MyField = Replace(MyField, "A", "a""
If I set a reference to the COM Add-In from within Access, I can use the function in the immediate window, but not in any queries. If, however, I copy/paste the same code into a code module within the database, then I can use the SQL statements like the above from within my Add-In. I can also create a seperate 'Code Library' database and programatically set a reference to it when my Add-In connects. This enables me to use the functions as I intend, but relies on an extra file, and I am trying to keep the Add-In self-contained.
Currently, I am having to work around the issue by opening a recordset and updating each record individually, which takes far longer to run (minutes rather than seconds)
So, what I'm wondering is, is there a way to declare these functions within my Add-In, so that I can use them as intended.
rgds
Andy