This also works well with the OLEDB Provider and functions can be created and dropped programatically. If you leave off "Initial Catalog=" in the connection string it will default to the scaler functions under the Master Database, otherwise it will under the particular Catalog [or database]. If you leave off the [dbo]. it should be automagically appended. I assume the scope is to the catalog; I'm not sure you can use [sys] since is not a schema prefix.

The code below leaves off initial catalog and assumes the function was not previously created. Stan

[vba]
sub sqlcrfunc
cText="CREATE FUNCTION" & vbcrlf
cText=cText & "fn_regex(@pattern varchar(255), @matchstring varchar(8000))" & vbcrlf
cText=cText & "RETURNS int" & vbcrlf
cText=cText & "AS" & vbcrlf
cText=cText & "BEGIN" & vbcrlf
cText=cText & " declare @obj int" & vbcrlf
cText=cText & " declare @res int" & vbcrlf
cText=cText & " declare @match bit" & vbcrlf
cText=cText & " set @match=0" & vbcrlf
cText=cText & " exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT" & vbcrlf
cText=cText & " IF (@res <> 0) BEGIN" & vbcrlf
cText=cText & " RETURN NULL" & vbcrlf
cText=cText & " END" & vbcrlf
cText=cText & " exec @res=sp_OASetProperty @obj, 'Pattern', @pattern" & vbcrlf
cText=cText & " IF (@res <> 0) BEGIN" & vbcrlf
cText=cText & " RETURN NULL" & vbcrlf
cText=cText & " END" & vbcrlf
cText=cText & " exec @res=sp_OASetProperty @obj, 'IgnoreCase', 0" & vbcrlf
cText=cText & " IF (@res <> 0) BEGIN" & vbcrlf
cText=cText & " RETURN NULL" & vbcrlf
cText=cText & " END" & vbcrlf
cText=cText & " exec @res=sp_OAMethod @obj, 'Test',@match OUT, @matchstring" & vbcrlf
cText=cText & " IF (@res <> 0) BEGIN" & vbcrlf
cText=cText & " RETURN NULL" & vbcrlf
cText=cText & " END" & vbcrlf
cText=cText & " exec @res=sp_OADestroy @obj" & vbcrlf
cText=cText & " return @match" & vbcrlf
cText=cText & "END" & vbcrlf
adCmdUnknown = 8
cConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS"
Set oConn = CreateObject("ADODB.Connection")
Set oCmd = CreateObject("ADODB.Command")
oConn.CommandTimeOut=0
oConn.Open cConn
oCmd.ActiveConnection = oConn
oCmd.CommandType = adCmdUnknown
oCmd.CommandText = cText
oRS = oCmd.Execute
Set oCmd= Nothing
oConn.Close
Set oConn=Nothing
End sub
[/vba]

to remove the function
[vba]
oCmd.CommandText = "DROP FUNCTION [dbo].[fn_regex]"

[/vba]