02-25-2011 07:59 AM
I am accessing our ACT! Database from Excel VBA using the ACTOLEDB2 provider. When I submit a Select query that should return more than one record in the recordset, I only get one record and the RecordCount is -1. The data in the record is correct and I have even changed the database by changing the data in a field and rerunning the query and I see the changed data, but never more than one record.
Is there a limit on how many records can be returned?
I am able to make a connection from Excel and get the whole Company table into a worksheet so that connection works but it will not allow me to make a query so I cannot test it from the Excel side.
Any help or guidance would be appreciated.
02-25-2011 12:49 PM
The only SQL statement I'm aware of that should give you any issues would be HAVING.
There is no limit to the amount of records that can be returned by your queries, it may be a syntax issue, you could post the command text here and either one of the other posters or myself may be able to spot the problem.
02-25-2011 01:24 PM
Thanks for your reply Matthew.
This is my sql query string:
sqlquery = "SELECT COMPANY.COMPANY, COMPANY.COMPANYID " _ & "FROM COMPANY "
This should return about 300 records from our company table. If I make the connection from Excel and ask for the Company table I get the whole thing.
I have extracted the key values from the Recordset returned from the sql query:
Watch : - : rstRecordset : :
: MaxRecords : 0 :
: RecordCount : -1 :
: Source : "SELECT COMPANY.COMPANY, COMPANY.COMPANYID FROM COMPANY " :
I was concerned about MaxRecords of 0 but I checked sql queries to my own database (which work fine) and they get the same 0 value.
The problematic value is RecordCount of -1. Normally I get the number I expect or 0 if the query returns nothing. I don't know what -1 signifies.
In the Fields of the Recordset I always get legitimate values but only one.
The connection I use is:
Set cnnSL = New ADODB.ConnectionSet rstRecordset = New ADODB.RecordsetSet cnnSL = New ADODB.Connection Set rstRecordset = New ADODB.Recordsetcnstr = "Provider=ACTOLEDB2.1;Data Source=U:\FilterBoxx_Master.pad;User ID=Paul Crawford;Persist Security Info=True" cnnSL.connectionString = cnstr cnnSL.Open
I agree that it must be a setting or a syntax issue but i cannot see it.
03-01-2011 07:38 AM
I opened up my db in VS and ran the same query against my DB and it returned the expected records.
SELECT COMPANY, COMPANYID
This same query executed in excel as well, does this same issue occur in all dbs? Are you able to execute this query without the ADO.net provider and isntead with a straight OLEDB connection? Just trying to narrow down at this point whether it's a syntax issue with leaving out the database owner, or if it's specific to the db/ado.net.
03-01-2011 03:17 PM
Sorry I am a little late responding as I was travelling today.
I just tried a sql query string similar to yours:
"SELECT [COMPANY].[COMPANY], [COMPANY].[COMPANYID], [COMPANY].[INDUSTRY], [COMPANY].[ID/STATUS] FROM [Filterboxx_Master].[dbo].[COMPANY]"
i.e. I added [Filterboxx_Master].[dbo]. to the beginning of the table name so that it is fully qualified. I still get RecordCount of -1 and only one record returned in the Fields part of the Recordset.
I am running this query from Visual Basic for Applications from within MS Office Excel 2007. I agree that if you make a connection from within Excel you will get the whole Company table using ACTOLEDB2 as a connection. But from there you cannot (at least in my Excel) execute a query string. In VBA I simply make a Connection with ACTOLEDB2 and then open a
Recordset with the query string above. What gets returned is one field and a RecordCount of -1. Since the RecordCount is not greater than 0 you cannot "MoveNext" to get the next record, that is part of the deal with Recordset. I regularly query my own MS SQL Server database with exactly the same code and equivalent string and always get the number of records that I expect.
In summary, it seems that it is not to do with the database owner as putting it in changes nothing. I know I have the name right because I just removed a two letters from the database name and got a RunTime error. I am using simply a reference to ACTOLEDB2:
"Provider=ACTOLEDB2.1;Data Source=U:\FilterBoxx_Master.pad;User ID=Paul Crawford;Password="";Persist Security Info=True;Initial Catalog="";Extended Properties="";Location="";Mode=ReadWrite"
Whether that is dealing with db/ado.net, you will have to tell me as I cannot see inside ACTOLEDB2.1 to know what it is doing.
I still need to find out what the syntax error is, if it is indeed that.
Thanks for all your help.
03-02-2011 07:24 AM
Unfortunately I'm not a VB developer and haven't really done any work in VBA in excel, so while there may be some sytactic issue, I don't see it either and really can't say with any certainty that that's indeed the issue. Hopefully another member of the community can be of more assistance with this.
I can be certain however that it's not an issue with the provider as I'm able to execute these same queries in other environments and get accurate results.
05-18-2011 12:59 PM
A few weeks ago I finally found the way to correct this problem. I had googled about recordsets and found a comment that said that if recordset is created on the server side then RecordCount will not exist. Since I was getting RecordCount = -1 it might as well not exist. The suggestion to cure this problem was to force the recordset to be created on the client side by setting the property CursorLocation to adUseClient (=3).
I did that in my VBA code and it worked with the ACTOLEDB2.dll provider. I also checked my other connection to a SQL Server and found that although I was not setting the CursorLocation property explicitly, the default seems to be adUseServer (=2) or the server side. So apparently the ACTOLEDB2.dll does need the CursorLocation property to be set explicitly to adUseClient (=3) to make it work in the VBA environment. I have no idea then why my SQL Server recordset is fine with a server side cursor but it also works fine with adUseClient (=3). So the safe choice may be to always set the CursorLocation property to adUseClient (=3).
Perhaps this may help someone else in the future.