By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,275 Members | 1,937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,275 IT Pros & Developers. It's quick & easy.

ADODB.RecordSet is not returning query results in MS Access 365 but same query does i

P: 2
I am using the following code. The problem is that the rst.recordcount is showing -1. When I take that same query in Access. it brings back two rows. I have MS Access 365 front end and SQL 2017 backend.

The current.connection beginning shows:
Expand|Select|Wrap|Line Numbers
  1. Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;
I am befuddled as to why this is not working.

Thanks for any insights
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String, strSQLWHERE As String
  2.     Dim rst As ADODB.Recordset
  3.     Dim strFolderPath As String
  4.  
  5.     Set rst = New ADODB.Recordset
  6.  
  7.     strSQL = "SELECT tblHyperlinks.* FROM tblHyperlinks WHERE Doc_Version_ID = " & Me!Doc_Version_ID
  8.  
  9.  rst.Open strSQL, CurrentProject.Connection
  10.  
  11.  If rst.RecordCount <= 0 Then
  12.  
  13. ELSE
  14. '' Need to be here
  15. End IF
4 Weeks Ago #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,660
Hi Jac.

Welcome to Bytes.com.

I suspect this is because a Recordset will typically not know the full count until it has processed through getting all the records into its local buffer. Until it knows what the count is it returns -1.
4 Weeks Ago #2

P: 2
Thanks, NeoPa. I tried inserting a rst.movelast command before the record count and go the error message Run-Time Error '3021;

Either BOF or EOF is True or the current record has been deleted. Requested operation requires a current record.
4 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,660
I'm sorry. I don't have a lot of experience using ADODB, and no time now to gain it or look into it more deeply.

Is there any reason why you're using ADODB for an ACE table? Seems a little counter-intuitive to me if I'm honest.

If using the more native DAO doesn't appeal to you then I would suggest reading all the notes from the ADODB.Open() help page and considering what may be the cause of the behaviour.
4 Weeks Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.