Getting RecordSet from TSQL Table-valued function

[rkruis]

Dim rs_det As DAO.Recordset


sSQL = "select * from dbo.Get_B_Data (" + CStr(gHistoryId) + ");"
Debug.Print sSQL

Set rs_det = CurrentDb.OpenRecordset(sSQL)


In SSMS this works;
select * from dbo.Get_B_Data(12);

In VBA I have tried Execute, and OpenRecordset

Using the above VBA Code, I get the error:
Run-time error '3131:
Syntax error in FROM clause.

Any ideas how I can call a TSQL Table Function from VBA and get a recordset back?

[jonh]

https://support.microsoft.com/en-gb/help/303968/how-to-create-an-sql-pass-through-query-in-access

[rkruis]

https://support.microsoft.com/en-gb/help/303968/how-to-create-an-sql-pass-through-query-in-access
Nope - that works with regular select statements, however fails when calling a Table-valued function created on SQL Server.

[rkruis]

Basic example of the SQL Function


CREATE FUNCTION dbo.Get_B_Data (
@history_id integer
)
RETURNS TABLE
AS
RETURN
(


select col1, col2 from myTable
where history_id = @history_id
)
GO

[jonh]

Eh? A passthrough query is processed by the Server. To SQL server it should just be a regular select statement.

[rkruis]

Dim db As Database Dim qdf As DAO.QueryDef
Dim sSQL As String
Dim rs As DAO.Recordset




Set db = CurrentDb()


db.QueryTimeout = 0




' next create the run details (projects) data
sSQL = ""
sSQL = sSQL + "select * from Get_SolidWorks_Import_BOM_Data(12) "
sSQL = sSQL + ";"
Debug.Print sSQL


If Not IsNull(db.QueryDefs("qrySQLPass").SQL) Then 'doesn't exist
db.QueryDefs.Delete "qrySQLPass"
End If


Set qdf = db.CreateQueryDef("qrySQLPass")
qdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=" & stDBServer & "\" & stDBDatabase & ";Trusted_Connection=Yes;DATABASE=" & stDBSchema & ";APP=APESv" & stVersion & " "
qdf.SQL = sSQL
qdf.ReturnsRecords = True


db.QueryDefs("qrySQLPass").Execute

Set rs = qdf.OpenRecordset()


qdf.Close




Set qdf = Nothing
Set db = Nothing

Error: 3065
Cannot Execute a select query

[rkruis]

If I set ReturnsRecords = False it continue, but I need a recordset returned.

[jonh]

If I set ReturnsRecords = False it continue, but I need a recordset returned.

db.QueryDefs("qrySQLPass").Execute

isn't needed. Remove it.

[rkruis]

OMG - Thank you. I looked at so many examples and non showed that.

[jonh]

OMG - Thank you. I looked at so many examples and non showed that.

np. glad you got it working. :)