Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2010
    Posts
    6

    Unanswered: SQL Stored Proc As Recordsource

    Not use if this the right place.

    I have T-SQL Stored Proc's that can connect to different database on the same server, by passing in the database name. The MSSQL version is 2005

    The above is part of the backend of a MS Access Application

    i would like to be able to set T-SQL results to the recordsource of a form for example

    Is this possible?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Readonly, right?

    This is an Access question, not a SQL Server one. I will move the thread.
    The answer I think (though I might not be remembering correctly) is to use a pass through query in Access.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2010
    Posts
    6
    Okay thanks,

    but i havent got a clue how to do that.

    I have functions to return recordset etc, but can not get any of to work as recordsource,

    If some could point me in the right direction that would be great

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How to create an SQL pass-through query in Access
    Just do a proof of concept that you can assign a pass through query to a form recordsource. There is more to it after that but let's prove the concept first.

    The issue you have here is that the Access gui is not a very good consumer of stored procedure output. VBA is, but the Access gui is not a good consumer of that either.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2010
    Posts
    6
    I found this, but didnt work

    Dim MyQ As QueryDef
    Set MyQ = MyDb.CreateQueryDef("")

    With MyQ
    ' Type a connect string using the appropriate values for your server.
    .Connect = CoreConn.SQLConn
    ' Set ReturnsRecords to false in order to use the Execute method.
    .ReturnsRecords = False
    ' Set the SQL property and concatenate the variables.
    .SQL = "spBuying_Orders('" & strProjNo & "')"
    End With

    Debug.Print MyQ.SQL

    'Me.RecordSource = "OrderList" < this is hardcoded query
    Me.RecordSource = MyQ.Execute
    Last edited by barkinmadscot; 11-02-10 at 12:21.

  6. #6
    Join Date
    Nov 2010
    Posts
    6
    Quote Originally Posted by pootle flump View Post
    The issue you have here is that the Access gui is not a very good consumer of stored procedure output. VBA is, but the Access gui is not a good consumer of that either.
    I am trying to get them replace to VBA/MS Access interface with VB.net interface

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please could you define "didn't work"? I can make code "not work" in billions of exciting ways but you'd need to know specifics to have any idea why.

    Error message, lines that throw the error etc.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2010
    Posts
    6
    Quote Originally Posted by pootle flump View Post
    Please could you define "didn't work"? I can make code "not work" in billions of exciting ways but you'd need to know specifics to have any idea why.

    Error message, lines that throw the error etc.
    Error is on me.recordsource, datatype mismatch

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm flying a bit blind here since I used to do this a different way, but try this as the last line:
    Code:
    Me.RecordSource = MyQ
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're not obliged to use a query in this case:
    Code:
        Dim wksSQL As DAO.Workspace
        Dim cnnSQL As DAO.Connection
        Dim rstSQL As DAO.Recordset
        Dim strConnection As String
        Dim strCommand As String
    
    ' Using the server name and a trusted connection.
    '
        strConnection = "DataConnection=ODBC;driver={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;Trusted_Connection=Yes;"
        
    ' OR: Using the server address and a local SQL connection.
    '
        strConnection = ";DataConnection=ODBC;driver={SQL Server};SERVER=192.168.1.12;ADDRESS=192.168.1.12,1433;NETWORK=DBMSSOCN;DATABASE=Scoreboard;UID=Sinndho;PWD=EachUisgue-01$"
        
    ' The command passed to the server consists in the name of the stored procedure with a named parameter.
    '
        strCommand = "Proc_Tbl_Credentials_Select @RecordID = 123"
        
    ' Create ODBCDirect Workspace object.
    ' The DefaultCursorDriver setting is required when using compound SQL statements.
    '
        Set wksSQL = CreateWorkspace("", "admin", "", dbUseODBC)
        wksSQL.DefaultCursorDriver = dbUseODBCCursor
    
    ' Open the connection.
    '
        Set cnnSQL = wksSQL.OpenConnection("", , , Connection)
    
    ' Get the RecordSet from the stored procedure.
    '
        Set rstSQL = cnnSQL.OpenRecordset(strCommand, dbOpenSnapshot, dbExecDirect)
    If you want to use a pass-through query, you can get the recordset from the query using:
    Code:
    ' For a saved  (persisted) Query (visible in the database window)
    ' (search in Access help for OpenRecordSet to know about <type>, <options> and (lockedits>).
    '
        Dim rst as DAO.RecordSet
        Set rst = CurrentDb.OpenRecordSet("<QueryName>", <type>, <options>, <lockedits>)
    
    ' OR For any kind of QueryDef object.
    '
       Dim qdf As DAO.QueryDef
       Dim rst As DAO.RecordSet
    
    ' Do something to open or create the QueryDef object.
    '
    '    Set qdf = ...
    
    ' Now open the RecordSet.
    '
        Set rst =  qdf.OpenRecordSet (<type>, <options>, <lockedits>)
    Have a nice day!

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    that's fine Sinndho - but can it be assigned as the record source for a form? And does it matter what type of form (e.g. continuous)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Oct 2010
    Posts
    51
    Quote Originally Posted by pootle flump View Post
    that's fine Sinndho - but can it be assigned as the record source for a form? And does it matter what type of form (e.g. continuous)?
    Yes a pass-through query can be used as a record source on a form. It will take a noticable hit on turn around time though. It will also be read only, if that affects the desired outcome.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The pass-through query would be the better solution in this case. Use an ubound form and this code:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim strSQL As String
        Dim qdf As DAO.QueryDef
        Dim dbs As DAO.Database
        
        Set dbs = CurrentDb
        With dbs
            If QueryDef_Exists("Qry_Proc_Tbl_Credentials_Select") = True Then .QueryDefs.Delete "Qry_Proc_Tbl_Credentials_Select"
            Set qdf = .CreateQueryDef
            qdf.Name = "Qry_Proc_Tbl_Credentials_Select"
            .QueryDefs.Append qdf
            qdf.Connect = "ODBC;driver={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;Trusted_Connection=Yes;"
            strSQL = "EXEC Proc_Tbl_Credentials_Select  @Direct=1"
            qdf.SQL = strSQL
            qdf.Close
        End With
        Set qdf = Nothing
        Set dbs = Nothing
        Me.RecordSource = "Qry_Proc_Tbl_Credentials_Select"
    
    End Sub
    
    Private Function QueryDef_Exists(ByVal QueryName As String) As Boolean
    
        Dim qdf As DAO.QueryDef
        Dim dbs As DAO.Database
        
        Set dbs = CurrentDb
        For Each qdf In dbs.QueryDefs
            If qdf.Name = QueryName Then
                QueryDef_Exists = True
                Exit For
            End If
        Next qdf
        Set dbs = Nothing
        
    End Function
    As the controls of the form are unbound (in this example textboxes are named Text0, Text1... Text6 and the query returns 7 columns), you can use, a piece of code such as:
    Code:
    Private Sub Form_Current()
    
        Dim i As Integer
        
        For i = 0 To 6
            Me.Controls("Text" & i).Value = Me.Recordset.Fields(i).Value
        Next i
        
    End Sub
    Have a nice day!

  14. #14
    Join Date
    Nov 2010
    Posts
    6
    [QUOTE=Sinndho;6483889]The pass-through query would be the better solution in this case. Use an ubound form and this code:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Dim strSQL As String
        Dim qdf As DAO.QueryDef
        Dim dbs As DAO.Database
        
        Set dbs = CurrentDb
        With dbs
            If QueryDef_Exists("Qry_Proc_Tbl_Credentials_Select") = True Then .QueryDefs.Delete "Qry_Proc_Tbl_Credentials_Select"
            Set qdf = .CreateQueryDef
            qdf.Name = "Qry_Proc_Tbl_Credentials_Select"
            .QueryDefs.Append qdf
            qdf.Connect = "ODBC;driver={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;Trusted_Connection=Yes;"
            strSQL = "EXEC Proc_Tbl_Credentials_Select  @Direct=1"
            qdf.SQL = strSQL
            qdf.Close
        End With
        Set qdf = Nothing
        Set dbs = Nothing
        Me.RecordSource = "Qry_Proc_Tbl_Credentials_Select"
    
    End Sub
    
    Private Function QueryDef_Exists(ByVal QueryName As String) As Boolean
    
        Dim qdf As DAO.QueryDef
        Dim dbs As DAO.Database
        
        Set dbs = CurrentDb
        For Each qdf In dbs.QueryDefs
            If qdf.Name = QueryName Then
                QueryDef_Exists = True
                Exit For
            End If
        Next qdf
        Set dbs = Nothing
        
    End Function
    I think this is better solution for my problem

    Thanks very much

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Also keep in mind that a DBA would probably have a conniption (if not a fit!) seeing this code, pricipally:
    Code:
            strSQL = "EXEC Proc_Tbl_Credentials_Select  @Direct=1"
    This is against the cardinal security rules of a database (google for "SQL injection" for more about this).
    Last edited by Sinndho; 11-03-10 at 11:32.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •