Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Unanswered: So simple, yet so frustrating ...

    Hi all - trying to do something simple and it's not working. It's more complicated but works like a charm in .NET, but I have to do this in Access.

    Want to pull data from a SQL sp into an ADO rs and use it locally.

    In SQL server, the sp executes nicely, returning 5 columns and thousands of rows.

    In Access, the rs gets the data structure (5 columns, field names, all that) but no data (recordcount = -1).

    What probably simple thing am I missing?

    Access:
    Code:
    Sub FetchSQL(sproc As String, rs As ADODB.Recordset)
    
        Dim cnn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim par As ADODB.Parameter
        
        On Error GoTo FetchError
        
        ' Establish connection
        Set cnn = New ADODB.Connection
        cnn.ConnectionString = ConnectionString
        cnn.Open
    
        ' Setup command
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cnn
        cmd.CommandText = sproc
        cmd.CommandType = adCmdStoredProc
        cmd.Parameters.Refresh
        
       ' Open recordset
        Set rs = New ADODB.Recordset
        Set rs = cmd.Execute()
        
    FetchDone:
        Set cmd = Nothing
        Set cnn = Nothing
        Exit Sub
        
    FetchError:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Fetch Error"
        Resume FetchDone
    
    End Sub
    SQL Server side:
    Code:
    USE [Sandbox]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[sp_Ups_CombinedCRMList]
    	
    AS
    BEGIN
    	
    	SET NOCOUNT ON;
    
        SELECT dbo.Ups_CombinedAddressList.CustKey, 
               dbo.Ups_CombinedAddressList.AddressID, 
               dbo.Ups_CombinedAddressList.Name, 
               dbo.Ups_CombinedAddressList.Cust, 
               dbo.Ups_CombinedAddressList.ID
        FROM dbo.Ups_CombinedAddressList
        ORDER BY dbo.Ups_CombinedAddressList.Name;
    END
    Immediate window debugging:
    Code:
    ? rs.Fields.Count
     5
    ? rs.Fields(1).Name
    AddressID
    ? rs.RecordCount
    -1 
    rs.MoveLast  Generates an error
    Error is: "Rowset does not support fetching backwards"

    So, it's obviously a good connection and the sp is giving the rs the data structure, but no data.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't remember precisely because I almost never use the ADO model for communicating with a SQL Server, but I think you have to specify the type of recordset you want to use (ADO CursorType Property or something like that). See ADO references in Access Help.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    shouldn't you be using a pass through query for connecting to a server db?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2011
    Posts
    14
    I think Sinndho's right. It sounds like your cursor type is set to ForwardOnly. In that case (if I remember correctly) RecordCount will return -1 and MoveLast will error out. ForwardOnly behaves about the way a DbDataReader in .NET does - it starts returning rows as soon as even 1 has been returned from the data server and doesn't cache them in memory, making it good for large data reads. I'd try using MoveNext and EOF to create a loop processing the data.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    healdem: I have it setup using pass-thorugh queries now, but I want to make it more ".NETish" for when I get asked to move all the code in the future. And, I am trying to see if I can "disconnected recordsets" to work in Access - it's a great tool in other environments.

    Plus, I want to get away from the datasource setup required on each machine that ends up running the app.

    Sinndho: I will try that this morning once the proverbial fires have been reduced to smolders. Boy, I sure do love Mondays ...

    Thanks guys!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I don't know if this solution is right or not, but the problem has to do with how the ADO recordset is populated. No matter how I set the cursor and lock type, I can only go forward, one record at a time.

    So, to make it work (faster than the pass through's and now bundled into an app wide function) I employed a second rs to hand the data off to, so it truly becomes a "disconnected recordset".

    Now, pull from the DB once through a stored procedure and do the rest on the local machine. Later, move to .NET is more seamless - the Fetch section get's remodeled to use datasource objects to which the controls are tied and the rest of the code is nearly the same.

    Code:
    Option Compare Database
    Option Explicit
    
    Public rsDisconnected_BillToList As ADODB.Recordset
    Const ConnectionString As String = "Provider=SQLOLEDB;Data Source=APP-SRV2;Trusted Connection=Yes;Initial Catalog=Sandbox;Integrated Security=SSPI;"
    
    Function FetchSQL(sproc As String, rs As ADODB.Recordset) As Boolean
    
        Dim cnn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim par As ADODB.Parameter
        Dim rsT As ADODB.Recordset
        Dim fld As ADODB.Field
        
        On Error GoTo FetchError
        
        ' Establish connection
        Set cnn = New ADODB.Connection
        cnn.ConnectionString = ConnectionString
        cnn.Open
    
        ' Setup command
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = cnn
        cmd.CommandText = sproc
        cmd.CommandType = adCmdStoredProc
        cmd.Parameters.Refresh
        'cmd.Parameters(1).Value = 10
        
       ' Open recordset
        Set rs = New ADODB.Recordset
        rs.CursorLocation = adUseServer
        rs.LockType = adLockOptimistic
        rs.CursorType = adOpenForwardOnly
        Set rs = cmd.Execute()
        
        ' Move to local rs
        Set rsT = New ADODB.Recordset
        rsT.CursorLocation = adUseClient
        rsT.LockType = adLockOptimistic
        rsT.CursorType = adOpenDynamic
        For Each fld In rs.Fields
            rsT.Fields.Append fld.Name, fld.Type, fld.DefinedSize
        Next
        rsT.Open
        Do While Not (rs.EOF Or rs.BOF)
            rsT.AddNew
            For Each fld In rs.Fields
                rsT.Fields(fld.Name).Value = fld.Value
            Next
            rsT.Update
            rs.MoveNext
        Loop
        
        ' Swap the recordsets
        Set rs = Nothing
        Set rs = rsT
        
        FetchSQL = True
    
    FetchDone:
        Set cmd = Nothing
        Set cnn = Nothing
        Set rsT = Nothing
        Exit Function
        
    FetchError:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Fetch Error"
        FetchSQL = False
        Resume FetchDone
    
    End Function
    
    Private Sub Command16_Click()
    
        rsDisconnected_BillToList.Filter = "[Cust] = 'Prospect'"
        Set Me.Recordset = rsDisconnected_BillToList
        Me.Refresh
    End Sub
    
    Private Sub Command3_Click()
    
        Dim fld As ADODB.Field
        
    '    Use this tp populate a combo or list box
    '    If FetchSQL("sp_Ups_CombinedCRMList", rsDisconnected_BillToList) Then
    '        Set Me.cboCust.Recordset = rsDisconnected_BillToList
    '    End If
    
    '   Use this to populate a form - just make sure the form has controls
    '   with the same name as the fields in the recordset
        If FetchSQL("sp_Ups_CombinedCRMList", rsDisconnected_BillToList) Then
            Set Me.Recordset = rsDisconnected_BillToList
            Me.Refresh
            For Each fld In rsDisconnected_BillToList.Fields
                On Error Resume Next
                Me.Controls(fld.Name).ControlSource = fld.Name
                If Err Then Err.Clear
            Next
        End If
    End Sub
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you test the performance of such a code with a big data set (say 20.000 rows of data)? I can be wrong but it seems slow, specially the loop used for populating the second recordset.

    To me, the idea of writing an Access application in a contorded way with the hope that it will be easier to port it into the .NET environment later on seems irrelevant and deeply flawed. .NET is such a totally different programing environment that the only solution would consist in rebuilding everything from the foundations anyway, and the data access model would not be the main source of torments.

    As for trying to "pilot" Access from a .NET application, it's a tedious process. Having .NET and COM interfaces communicate using interops is nearly a nightmare and, as far as performances are concerned, they're running down the drain.
    Have a nice day!

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Sinndho, thank you for your frank response. As soon as I switched over the the real data, is was quite slow - the data retrieval was very fast, but the loop was an anchor.

    I will briefly explain: the app is written in Access, with the typical FE/BE setup, a fairly large set of data for and MDB. It is pretty slow when 10 users are in there at once. We are implementing a new software package (with a SQL Server BE) that will replace some of this apps function, but the current app will need to be modified to work with the new system.

    There are always multiple ways to do things, and in this case I decided to migrate the MDB data into the same SQL Server and use that as my sole BE in hopes to eliminate the network based MDB file (a real drag) and improve both security and performance.

    However, I must minimize rewrite of the existing app (no way to justify a complete rewrite, to .NET for example, which would be my preference).

    So, I initially setup linked ODBC tables and several pass-through queries, but the performance is still sluggish (in some cases worse). Plus, there's all sorts of security and access rights issues and I had to use direct SQL statements for inserts and updates.

    What I really want to do is have everything using stored procedures on the server and have the app push and pull as needed - mimicing a .NET app in Access without actually having to rewrite it.

    The plan is to use the existing forms ("as is" in most cases, with some minor changes in other places) and use disconnected recordsets to drive the data in them. Since I aligned my field names with my control names, I can semi-automate the pushing and puling of data on and off the forms.

    By the way, I figured out what I was doing wrong. I finally found a reference that said ado executes commands to forward only recordsets, regardless of how they are set. The solution is to use open instead of execute.

    The correct code follows:
    Code:
    Public Property Get FetchSQL(sproc As String, rs As ADODB.Recordset) As Boolean
    
        Dim cnn As ADODB.Connection
        
        On Error GoTo FetchError
        
        ' Establish connection
        Set cnn = New ADODB.Connection
        cnn.ConnectionString = ConnectionString
        cnn.Open
        
       ' Open recordset
        Set rs = New ADODB.Recordset
        rs.CursorLocation = adUseClient
        rs.Open sproc, cnn, adOpenStatic, adLockReadOnly, adCmdStoredProc
            
        FetchSQL = True
    
    FetchDone:
        Set cnn = Nothing
        Exit Function
        
    FetchError:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbCritical + vbOKOnly, "Fetch Error"
        FetchSQL = False
        Resume FetchDone
    
    End Property
    The key here is that when the connection is set to nothing, the database is released and any searches, queries, etc. done on the local end work off the data cached in memory. Now there is only 1 link in the chain to be weak - the user's desktop.

    Thanks for your input and concern - constructive criticism is often overlooked for the value it brings.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I understand and respect your choices and motivations. By the way, did you ever consider using DAO as the data access interface? It's native to Access, communicates flawlessly with a SQL Server and is faster than any ADO solution I know. Of course you can forget any idea of portability in the .NET environment, but I don't see Access leaving the DAO path any time soon.
    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
  •