I used the upsizing wizard in Access to convert a DB to SQL2000. The query in Access was,

SELECT [pLastName]+', '+[pFirstName] AS Name, [pMedicalRecord]
FROM Pinfo
ORDER BY [pLastName], [pFirstName];

It has been converted to a user defined function in SQL,

CREATE FUNCTION vLastFirstNameLookUp ()
RETURNS TABLE
AS RETURN (SELECT TOP 100 PERCENT pLastName+', '+pFirstName AS Name, pMedicalRecord, pLastName, pFirstName
FROM pInfo
ORDER BY pLastName, pFirstName)

The front end is a VB app that uses an Active-X User Connection to call the function in the SQL DB. Here is the error I receive when open is called,

Run-time error '-2147217900 (80040e14)':

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '`'.

The problem is that I don't see a ` anywhere. The Data Link Properties are set for OLE DB Providers for ODBC Providers.

Thanks

Can you post the code that calls the function from the frontend?

_________________________
- Vijay G
Strive for Perfection

Dim de As deSearchItems

Set de = New deSearchItems
' The error occurs on the following line.
de.rscmdNameLookup.Open


'----------------------------------
' This is the only code in deSerchItems, there is a DEConnection
' called con in deSearchItems.

Private Sub DataEnvironment_Initialize()
    con.ConnectionString = g_ConStr
End Sub

'----------------------------------
' From main.bas, g_ConStr is global
g_ConStr = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=apicdsn;uid=;pwd=;"


I put a breakpoint on con.ConnectionString = g_ConStr. When it hit the breakpoint, I just hit run and the error does not occur.

Thanks,
Hez

Hi there,

Not sure where you call the SQL function in that code?
de.rscmdNameLookup.Open

Also, can you change this part of your query to not use ' and see if that works fine. But IMO, that query is perfect. just wanted to rule that possibilty out.

SELECT TOP 100 PERCENT pFirstName AS Name, pMedicalRecord, pLastName, pFirstName FROM pInfo ORDER BY pLastName, pFirstName

And then try this, if the previous one doesn't fail.

SELECT TOP 100 PERCENT pLastName+ ' ' +pFirstName AS Name, pMedicalRecord, pLastName, pFirstName FROM pInfo
ORDER BY pLastName, pFirstName

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection