Can you use a Public Function created in VBA in SQL Query 1

[4946]

Can a Public Function created with the ACCESS VBA environment that produces an integer be referenced directly in the SQL Query window? i.e.

Select FieldX - PublicFunctionName as Column1, FieldY, etc.......
From TableWhatever;

Thanks,

WinN

 

[SkipVought]

Hi,

Did you try it?

You know that no one will arrest you or issue a fine for trying.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[MichaelRed]

as long as the query reference an "Access" table.




MichaelRed

 

[4946]

Hey Skip and MichaelRed,

Yep, I've tried it.... several different ways and times. When I check it in the immediate window it works fine. But when I try to reference it in a query I get the "Enter Parameter" box. If I try to use it in a macro, it's module name does not show up as a selection in the "openFunction" action, but does show up as a selection in the "openModule" action.

Option Compare Database

Option Explicit
_________________________________________
Public Function functionname() As Integer

     Body of code that computes integer

functionname = integer value computed above
End Function

I compiled the code before trying it in the SQL query that references a linked table in the database.

What am I overlooking that apparently prevents the database from seeing this as a function?

WinN

 

[PHV]

Have you tried this ?
Select FieldX - PublicFunctionName[highlight]()[/highlight] as Column1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zukkster]

You are in the twighlight zone that is SQL to Access and took me some time to figure, but if I understand your question correctly I think this may help giving you the code to reference data from a SQL server table. The Public constant you will need to edit to connect to the SQL database you are querying.

This gives you the added funcionality of being able to move through multiple records and search for specific values in a record set and return just them.

You will need to reference one of the available ADO files in your VB module (Tools > Reference>) and select

Microsoft ActiveX Data Objects 2.7 Library

(if you don't have this version others will work)

Obviously you need to be sure you are returning an integer datatype to the function or you'll get issues.

++++++++++++++++++++++++++++++++++++++++++++

PublicFuntion ReturnFirstRecord() as integer

Public Const reporterConnection As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=centralRef;Data Source=CMCUKSQL"

Dim conn As ADODB.Connection, RS As ADODB.Recordset
Dim sqlStr As String


sqlStr = "SELECT tblVersions.version FROM tblVersions WHERE tblVersions.ID=9;"

Set conn = New ADODB.Connection
With conn
.ConnectionString = reporterConnection
.Open
End With

Set RS = New ADODB.Recordset
With RS
.ActiveConnection = conn
.LockType = adLockReadOnly
.Open sqlStr
End With

RS.MoveFirst
ReturnFirstRecord = RS("Field_Name")

RS.Close
conn.Close

End function

 

[MichaelRed]

per earlier,

as long as the query reference an "Access" table

"SQL" is an anamolous term. SQL is a "language" all on its' own. To refer to a DIALECT, you need to be somewhqat more precise, as in "SQL Server" or "Jet SQL" or "Oracle sql", etc.






MichaelRed

 

[4946]

PH,
Shazamm!!!!!!! You nailed it. What an eye you have for detail. (an ex-English or Math teacher perhaps ?? ).

Have you tried this ?
Select FieldX - PublicFunctionName[COLOR=red yellow]()[/color] as Column1

Many thanks to you and all who have responded.

WinN

 

[4946]

Zukkster,

You're right about the twilight zone!! Thanks for the function code, I will save it to my tool kit for the next project. I really appreciate all the help everyone in the group provides.

Thanks,

WinN

 

[Zukkster]

4946,

If you like that code and you need to do lots of connection to SQL server using VB(A) then I'd absolutely recommend a book called

"SQL Server and ADO Programming" by Sybex.

The only trouble with this code is that when you distribute the Access database, some users may not have the required ActiveX version references for your code.

I'm starting to wish I'd created the application in VB rather than Access and VBA, because that would get arround the referencing issues by installing the required files when the setup is run.

Enjoy.

 

[MichaelRed]

" ... VB rather than Access and VBA, because that would get arround the referencing issues by installing the required files ... "

but a brief search in these fora will easily return threads discussing (and solving) this.





MichaelRed

 

[4946]

Zukkster,

Thanks for the tip on the book,

"SQL Server and ADO Programming" by Sybex.

I will definitely look it up my next visit to the tech bookstore.

WinN