Results 1 to 5 of 5
  1. #1
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15

    Question Not receiving same query response when using linked SQL table versus local table

    I have a local table that I have placed records into that came from a linked SQL table. I am trying to query the linked SQL table that would have additional matching records. If it finds more than just the initial record I placed in the local table then I do additional things with these records. If it only finds the initial record I placed in the local table then I move to the next local record and continue until I reach the EOF in the local table. I am using 2 recordsets to loop through each table. The problem I am having is that when I am using the linked SQL table the recordcount always comes back as only 1 record but if I copy the linked SQL table and make it a local table it returns the correct recordcount.



    So I am using the same data whether in a SQL linked table or in a local table but why would it be able to identify the correct record count in the local table and not the linked SQL table?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If you are using a recordset, you need to do rst.movelast before rst.recordcount.
    Not sure what you mean by 'additional things' but you could almost certainly do your processing faster using queries or sql statements instead of recordsets
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, you could also have a problem with True\False (BIT) Data Types. SQL Server doesn't *see* -1, it sees *True*. Better to use True\False.

  4. #4
    madams is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    15
    Thanks, that worked adding the recordset MoveLast.

    I just find it odd that without moving to the last record when using a local table that it would return the correct number of records found but with a SQL table it would always return as 1.
    Last edited by madams; 10-10-2019 at 09:52 AM. Reason: clarifying what worked

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You OFTEN have to be more precise when dealing with SQL tables.
    TBH using MoveLast is a good idea even with local tables
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-07-2018, 07:08 AM
  2. Replies: 3
    Last Post: 10-06-2016, 10:18 AM
  3. Replies: 3
    Last Post: 10-08-2015, 01:02 PM
  4. Replies: 3
    Last Post: 01-01-2015, 05:25 PM
  5. Replies: 0
    Last Post: 05-21-2012, 11:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums