0

I found @mwolfe02's Mike Wolfe's advice to do in-place Form requires without headaches or fuss. Cool. Looks easy enough, cuts down on extra coding required. Except...it doesn't work.

Every time, every form, no matter what, I get error 3251: "Operation is not supported for this type of Object". Every time. Doesn't matter how the Form is bound, whether via me.recordsource = "something", set me.recordset = somerecordset, statically assigned in the Form Properties, etc.

Forms are set to Dynaset, passthrough (when used) are opened as dbOpenDynaset, etc.

I am able to use me.recordsetclone just fine in these (in fact, that's what I'm doing now to get the rowid). What am I doing wrong? or is there something wrong? Or is this just not usable in practice with SQL Server as the source?

For reference, I'm using O365 Office 64 bit, Ver 16, and SQL Server as a backend, but some tables are loaded locally as temp tables.

[Edit] When setting the form's record source, I do it like this:

' (in the form open)
me.recordsource = SendSQLReturnData (SQLString) ' This works great, and I know the SQL functions. 
(later in an OnPress from button:
me.recordset.requery ' <= Does not work.

' (SendSQLReturnData)
Public Function SendSQLReturnData(ByRef SQLString As String, _
Optional ByRef ReturnTable As String = PassthroughQuery, _ 
Optional DeleteExist As Boolean = False, _
Optional NoChange As Boolean = False) As String
'Sets up a passthrough query, hands back the name of the string via query. 

    Dim qdf As DAO.QueryDef
    Dim rstReport As DAO.Recordset
    Dim dB As DAO.Database

    Set dB = CurrentDb

    If DoesQryExist(ReturnTable) = False Then
    'this handles the case where the table wasn't set up already either by accident or didn't need to be previously.
        Set qdf = dB.CreateQueryDef(ReturnTable)
    Else 'Assume the only other case for "Does it exist" is true, so don't bother checking
        If NoChange = False Then
            Set qdf = dB.QueryDefs(ReturnTable)
        Else
            GoTo Exit_Here
        End If
    End If

    With qdf
        .Connect = dB.TableDefs(BackEndKeyTbl).Connect
        .SQL = SQLString
        .ReturnsRecords = True
        .OpenRecordset , dbOpenDynaset
    End With
Exit_Here:
    SendSQLReturnData = ReturnTable
    qdf.Close ' Turning these on/off doesn't change result.
    Set qdf = Nothing
    Set dB = Nothing
 
End Function
hecon5
  • 66
  • 1
  • 7
  • 1
    Post your exact code, not just reference to link with example. I tested the `Recordset.Requery` and works for me. – June7 Jun 02 '21 at 16:45
  • Tested with a linked SQLServerExpress table in Access2010 32-bit. – June7 Jun 02 '21 at 17:51
  • When you say exact code, what do you mean? The code `me.recordset.requery` will not work for forms (for me). – hecon5 Jun 02 '21 at 17:57
  • More troubleshooting: If I use a Passthrough table, the requery method works properly. If I use a passthrough query or try to hand the results of a query via a recordset (snapshot or dynaset), the same happens. Is there something about a passthrough query that makes it non requeryable? – hecon5 Jun 02 '21 at 18:12
  • By exact code, I mean your procedure. What do you mean by 'Passthrough' table - a table link? I don't know about passthrough query. Just installed SQLServerExpress last week. Lots to learn. What do you mean by 'hand the results of a query via a recordset' - hand to where? – June7 Jun 02 '21 at 18:19
  • You say OnPress event - did you mean OnClick? I don't see an OnPress event and I've never used OnKeyPress button event which I just tested and it does not trigger. – June7 Jun 02 '21 at 19:14
  • Where is `PassthroughQuery` declared and set? I get 'variable not defined' on this. – June7 Jun 02 '21 at 19:24
  • OnClick, yes, sorry; I was working on another tool that uses "press" (unrelated to mice) events and brain didn't wholly switch gears. It's a static constant; it's basically an already setup Passthrough Query so if I forget to define the query, there's something to use, like in this example form where its' a modal form so I do the "work" then close it. Pick any string, perhaps `"ISeekRows"` would work nicely. – hecon5 Jun 02 '21 at 19:48
  • Now I get "sub or function not defined" on DoesQryExist(). This may be way over my head. I installed SQLServerExpress on my laptop so my connection is localhost. I need only one line to set a link to table. Then I have RecordSource set in form design with table name. I have yet to explore passthrough. – June7 Jun 02 '21 at 20:10
  • DoesQryExist() is a local function that returns if there's a table or query with that name. I appreciate your help on this! Passthrough queries are awesome, as they offload a ton of the client side processing to an SQL server, can be dynamic, and (IMO) are easier to deal with programmatically than linked tables. Downsides: they're a little more cumbersome if you're just starting out with SQL journey, and they require you to have more access to the SQL server than some allow. They're also maddening when you're trying to refresh them, apparently :) – hecon5 Jun 02 '21 at 20:24
  • 1
    Built first passthrough query. Confirmed Requery works but Recordset.Requery does not. Will have to use long version code to hold record position. – June7 Jun 02 '21 at 20:51
  • This is so disappointing! Hopefully someone else can figure this out, cause it's got me scratching my head! – hecon5 Jun 03 '21 at 11:39
  • I guess not. If @June7 has verified the behaviour, so it is. – Gustav Jun 03 '21 at 11:41
  • However, I find that PT query is not directly editable so not sure how form Requery is even useful. I did find a discussion indicating a PT could be made editable by including connection string in the SQL but it did not work for me, insists on opening the SelectDataSource dialog when I try to open query. https://stackoverflow.com/questions/18898032/how-to-make-a-passthrough-passthru-query-editable. Oh well, maybe someday I will ask a question. – June7 Jun 03 '21 at 18:26
  • Good find! Interesting read, looks like I will have to either use the long form, or update another way. – hecon5 Jun 03 '21 at 19:17

1 Answers1

0

This will not work when the Form or Report recordsource is a passthrough query.

hecon5
  • 66
  • 1
  • 7