MS Access crashes binding RS to a form from SQL Server stored procedure-CodePudding

I am just starting to move our Access DB to SQL Server and am having trouble.

I have a stored procedure that successfully returns rows to an ado recordset.

When I try to bind the rs containing the results of the stored procedure to the Access form, Access crashes without displaying any error messages. I'm on O365 32b and SQL Server 2019.

Here's the code:

Dim sSQL As String, rs As ADODB.Recordset
1   sSQL = "Exec usp_TaskStatusWidget " & Me.Tag & ",0"
 
2   ADOConn.ConnectionString = conADO
4   ADOConn.Open
6   Set rs = New ADODB.Recordset
7   rs.CursorLocation = adUseClient
8   rs.Open sSQL, ADOConn
10  Set Me.Recordset = rs ' Access crashes here

. . .

Any help would be greatly appreciated!

tia.

CodePudding user response：

Ok, are you previous using ADO, or are you just introducing this?

In most cases, you are better off to just use a view. (replace the access query with a linked view), and then continue useing client side where clauses or filters (access will ONLY pull down the rows you request). So linked views are often a better choice and much less work (in fact, even existing filter for a open report etc. will work and only critera matching the were clause records are pulled.

And in most cases, i don't introduce ADO.

So for a PT query, I often do this:

dim rs    as DAO.RecordSet
with CurrentDb.queryDefs("qryPt")
   .SQL = "Exec usp_TaskStatusWidget " & Me.Tag & ",0"
   set rs = .OpenRecordSet
end with

So, above assumes you have a pt query called qryPt. This also means that you never deal with or worry about connection strings in code. The pt query has the connection. (and your re-link code now can re-link tables and pt queries).

I ONLY suggest the above as a FYI in case that you introducing ADO for calling store procedures, and the rest of the application was previous DAO. If the application was previous DAO, then leave it alone, and use above approach for your PT queries - even code that needs to call store procedures.

CodePudding user response：

Access tends to try and parse the query text to get filters/sorts/etc to work, and if it isn't a plain syntax error but isn't Access SQL either, strange things tend to happen, mostly crashes.

Try adding a comment up front to make sure Access knows not to parse:

 sSQL = "-- Access no parse pls" & vbCrLf & "Exec usp_TaskStatusWidget " & Me.Tag & ",0"

The content of the comment is not relevant, of course, its purpose is to immediately cause a syntax error when Access tries to parse it as Access SQL (which doesn't have comments)