how to refer to a query that calls a function

[Guest]

hi,
I have an Access database that uses some queries that call a function for
formatting purposes. They work just fine. I am starting to develop an
application in asp.net (VS 2005) using this database. I would like to call
one of the queries that calls a function. I didnt think this was a big deal,
but VS 2005 doesnt display the queries that call my function. I pasted it in
and tried it anyway, but it says it cannot find the function that I am
calling. How do I do this? Am I supposed to move the function over to vb.net
and code my function there? Can somebody explain this to me- I would like to
know how you would do this. thanks very much !!

 

[RobinS]

I don't think you can. I could never get this to work in VB6. I ended up
pulling the data and performing the function when processing the
information in the dataset.

Robin S.

 

[Steven Cheng[MSFT]]

Hello Maggie,

As for the Access queries, do you mean those predefined SQL queries in the
access mdb database? And I'm wondering the functions you mentioend, are
those functions the built-in Access helper SQL functions or your custom
functions writen through VBA? Based on my understanding, tables and
standard queries should be able to display in Visual Studio's Server
explorer and can be queried through ADO.NET code. Would you provide some
further description on the problem you met or give us a sample mdb database
for reference?

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi Steven,

The function I call is one that I defined in an Access module. It basically
takes data from a different table, and strings out the data separated by
comma's so that when I print my report, it all comes out in one line. It
works great in Access.
So, the query looks like this:
SELECT qryconcatdist.*, fConcat("qryteachsubj
","TeachID","subjectcode","long",[teachid]) AS subjects
FROM qryconcatdist;

**
In VS, when I look at my database, I can see that it separated the queries
into stored procedures, views and functions. These types of queries(above)
went into the functions. I tried to use it in .NET, but it told me that it
didn't understand the parameters .
So, my thought was that I should take the function and put it into the same
page where I display my data. Im new at VS, so I have no idea what I am
doing!! I know some ASP and I would have put this code in the same page. not
sure what to do here.

My function looks like this: ( I got the idea from it off this site actually-)
Function fConcat(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) As String
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSql As String
On Error GoTo err_fconcat
varConcat = Null
Set db = CurrentDb
strSql = "select[" & strFldConcat & "]from[" & strChildTable & "]"
strSql = strSql & "Where "

Select Case strIDType
Case "String":
strSql = strSql & "[" & strIDName & "]='" & varIDvalue & "'"
Case "Long", "Integer", "double":
strSql = strSql & "[" & strIDName & "] =" & varIDvalue

Case Else
GoTo err_fconcat
End Select

Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
'Debug.Print "after rs open rs " & rs.RecordCount & " STR SQK "; strSql
'
With rs
If .RecordCount <> 0 Then
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & ","
Debug.Print varConcat & " var concat ?"
.MoveNext
Loop
End If
End With
fConcat = Left(varConcat, Len(varConcat) - 1)

'Debug.Print fConcat

exit_concat:
Set rs = Nothing: Set db = Nothing
Exit Function
err_fconcat:
Resume exit_concat

End Function

 

[Steven Cheng[MSFT]]

Thanks for your reply Leotiger,

So the functions you mentioned are VBA functions defined in module. Based
on my local setting, when I open a access mdb database in the server
explorer, only the "Tables" and "Views" directory will contains
objects(those tables and queries in the access database). Those VBA
functions will not display in the server explorer. I'm not sure whether it
is due to my test database is too simple and the VBA function I used is not
be able to display in it. My test environment is windows XP with VS 2005
Team Suite and SP1 installed. If possible, would you create a simplified
access mdb database and send it to me (or where I can download) so that I
can perform some local test against it? You can reach my through the email
in my signature(remove "online").

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Thanks Steven, I will send it to your email address.

 

[Guest]

Hi Steven,

The emal I sent came back.. I sent the email to this address:

 

[Steven Cheng[MSFT]]

Hello Leotiger,

What's the email address you used? (e-mail address removed) ?

If so, you need to remove the "online". The actual address should be
"stcheng" + "@" +"microsoft.com"

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Guest]

Hi Steven,

I actually was able to send it 2/8 at night. I can try again.
thanks