Late binding RST of Report. Possible? 1

[hwkranger]

I'm using MSSQL2k

Is it possible to do late binding of a report source in Access?

Private Sub Report_Open(Cancel As Integer)
    Dim objSuggestion As New clsSuggestion
    Me.RecordSource = objSuggestion.FetchAllRecordsReportView
End Sub

Where clsSuggestion has member:

Public Function FetchAllRecordsReportView() As ADODB.RecordSet
    Dim rst As New ADODB.RecordSet
    rst.Open GetFetchReportViewSQL, cn, adOpenKeyset, adLockOptimistic
    Set FetchAllRecordsReportView = rst
End Function

I'm getting an error: Data type mismatch. Why?



Randall Vollen
National City Bank Corp.

 

[RoyVidar]

The rowsource property I think, accepts a string, which is either a sql string, or the name of a table or query - I think that's the type mismatch.

For, I think Access 2002 and later, there is a recordset property which will accept an ADO recordset - but alas, this will only work in ADP's.

Would it work to assign the GetFetchReportViewSQL thingie in stead (then as a string)?

Roy-Vidar

 

[hwkranger]

Roy,

Thank you for your reply! I did some digging, and found that it does only accept a string.

My problem is that the SQL is something like:

Public Function GetFetchReportViewSQL() As String
    Dim strSQL As String
    strSQL = strSQL & " SELECT" & vbCrLf
    strSQL = strSQL & " S.SuggestionID," & vbCrLf
    strSQL = strSQL & " CoNumber, OprID, Suggestion, SuggestionLocation," & vbCrLf
    strSQL = strSQL & " SS.Name AS Status," & vbCrLf
    strSQL = strSQL & " ST.Name AS Type," & vbCrLf
    strSQL = strSQL & " SC.Comment," & vbCrLf
    strSQL = strSQL & " SA.Name AS [Action]" & vbCrLf
    strSQL = strSQL & " From" & vbCrLf
    strSQL = strSQL & "  CentralProofMA..tblSuggestion s" & vbCrLf
    strSQL = strSQL & "  INNER JOIN CentralProofMA..tblSuggestionType ST" & vbCrLf
    strSQL = strSQL & "   ON ST.SuggestionTypeID = S.SuggestionTypeID" & vbCrLf
    strSQL = strSQL & "   INNER JOIN CentralProofMA..tblSuggestionComment SC" & vbCrLf
    strSQL = strSQL & "   ON SC.SuggestionID = S.SuggestionID" & vbCrLf
    strSQL = strSQL & "  INNER JOIN CentralProofMA..tblSuggestionAction SA" & vbCrLf
    strSQL = strSQL & "    ON SA.ActionID = SC.ActionID" & vbCrLf
    strSQL = strSQL & "   INNER JOIN CentralProofMA..tblSuggestionStatus SS" & vbCrLf
    strSQL = strSQL & "  ON SS.StatusID = S.StatusID" & vbCrLf
    GetFetchReportViewSQL = strSQL
End Function

I'm specifying directly which database I want the report to pull from -- Thus I can't use this in the row source, since it's in SQL, not MSACCESS version of SQL.

I think as a work around, I'm probably going to have to use a pass-through query, which does not make me happy...

Randall Vollen
National City Bank Corp.