> I'd really appreciate your help creating a parameterised view in SQL
> Server, which I can then invoke from VB5. I'm using DAO and ODBC to
> connect to SQL Server 2000.
>
> I can open Tables and Views, but can't find how to create or use a
> "query" parameter. If I use the "?" (in the SQL Enterprise manager
> View design mode), the syntax checker verifies the query ok, but I
> then get the message "Parameters cannot be used in this query type".
> It doesn't like the %var% terminology at all here.
>
> I am sure I could build the full query in VB and save it to the Server
> "on-the-fly", but this partly defeats the object, doesn't it?
>
> I could really do with a couple of pointers: (a) how to define the
> view/query in Ent Manager and (b) how to invoke it from VB, supplying
> the parameter.

> I had tried this, amongst many other things. However, I didn't realise
> I'd got it right, as I couldn't work out how to populate a VB recordset
> object from an SQL function.
>
> I normally (in Access) open a database object, then use something like:
>
> lsSQL = "Select * from Northwind.Orders WHERE CustomerID = " &
> llCustID
> Set lrsOrders = gdbMain.OpenRecordset(lsSQL)
> lrsOrders.MoveFirst
>
> where:
> gdbMain is a globally-defined database object
> lrsOrders is a locally-defined recordset object

> That's brilliant, thanks, Erland. I've applied that example to my
> system and got it to work fine within SQL Server now.
>
> However, when I try:
> lsSQL = "Select * from FindCust('07940437220')"
> Set lrsTemp = gdbSparc.OpenRecordset(lsSQL)
>
> ..in VB, I get "Error 3131 - Syntax Error in FROM clause". It's as if
> VB doesn't recognise the function exists at all?

In article <403a0e0e$0$198$75868355@news.frii.net>, Phil <philsowden@nospam.dataservicesltd.co.uk> wrote:
>That's brilliant, thanks, Erland. I've applied that example to my
>system and got it to work fine within SQL Server now.
>
>However, when I try:
>lsSQL = "Select * from FindCust('07940437220')"
>Set lrsTemp = gdbSparc.OpenRecordset(lsSQL)
>
>...in VB, I get "Error 3131 - Syntax Error in FROM clause". It's as if
>VB doesn't recognise the function exists at all?
>
>The identical code works fine (as a View) within SQL Server (n.b. the
>customer reference I'm using is a 14 character nvarchar field).
>
>I'm sorry to be such a novice! I also realise that this may not be the
>correct forum - but it may be that you are able to help.
>
>Thanks once again,
>
>Phil
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!

> The call you're looking at is my failed attempt to call that same
> function from within VB (version 5, using DAO with ODBC connection to
> SQL Server).