DCount Error

[Snowflake68]

I have created an application using 2013 and then put a copy of my system on another machine running 2010 but the DCount doesnt work.

I have a text box on a form with

= DCount("*","qryResults")

and this is working on 2013 but on the 2010 which is linked to SQL tables it just shows #Error in the box. I just want to count the records in the query called qryResults.

I have created local copies of the sql tables and put my application on another pc running 2010 and this is ok too. So I dont believe that it has anything to do with 2010 as such.

Not sure if its permissions to the sql tables but I can open the linked SQL tables without any issues in the application that has the #Error.

Any help would be greatly appreciated.
Thanks

 

[Minty]

Make sure you have set at least select permission on the tables, if you are a SQL admin you won't need them, but anyone else running the same database will.

 

[Snowflake68]

That's probably it. I will check it later when I am back at my desk. Thanks

 

[Snowflake68]

Make sure you have set at least select permission on the tables, if you are a SQL admin you won't need them, but anyone else running the same database will.

I have checked the permissions and all is ok as I am logged in as Sys Admin. I have also checked on another server using sys admin account and that too throws the error. However I asked a friend to put a copy of the app on their server which as the same tables and all is working on there.

I am lost as to what is causing the error, as I can run a simple select query on the table. The DCount is on a Union query, should that make any difference?

 

[The_Doc_Man]

You did a version downgrade. Check your references to see if that broke any of them. Normally, Access handles references correctly for version upgrades, but it's not so good on version downgrades. I wouldn't be surprised to find that your DCount problem is just the tip of an iceberg.

 

[Snowflake68]

You did a version downgrade. Check your references to see if that broke any of them. Normally, Access handles references correctly for version upgrades, but it's not so good on version downgrades. I wouldn't be surprised to find that your DCount problem is just the tip of an iceberg.

It works on 2010 on another pc so it cant be anything to do with version.

I have sourced the issue though but not fixed it yet.
I would appear that it only throws the error when it looks at a union query. If I change it to look at a simple select query then it works perfectly.

Still baffled as to why it wont work with a union query though.

Thanks anyway

 

[The_Doc_Man]

So it works on another machine on 2010? Fine. Has Access on THAT machine ever been upgraded to 2013?

The problem is machine-specific because the references are a part of the registry, which is ALSO machine-specific. More specifically than that, it is sensitive to the machine's history since the registry is your machine's local history book.

Let's try this again. On the miscreant machine, open a code page and look at the list of items exposed by clicking Tools then References. See if anything says "missing reference." Takes 2 minutes tops to do this test, but your answer tells me you didn't want to try this so answered without checking. At least that is how I read it.

 

[Snowflake68]

So it works on another machine on 2010? Fine. Has Access on THAT machine ever been upgraded to 2013?

The problem is machine-specific because the references are a part of the registry, which is ALSO machine-specific. More specifically than that, it is sensitive to the machine's history since the registry is your machine's local history book.

Let's try this again. On the miscreant machine, open a code page and look at the list of items exposed by clicking Tools then References. See if anything says "missing reference." Takes 2 minutes tops to do this test, but your answer tells me you didn't want to try this so answered without checking. At least that is how I read it.

Apologies if you thought I was ignoring your advice, I wasn't. The PC that I need this to work on which has 2010 on is not a pc that I have access to as I work remotely and I have to request permission prior to connecting which isnt as easy as it sounds, but I wont go in to that. Anyway I have however now been able to check the references and there are NO missing references.

Also I am absolutely sure that the pc with 2010 on which it is working on has never previously had 2013 on either.

Anyway I have stopped using the union query now and have changed it to two simple select queries and all is working perfectly.

I will park this issue for now but it still remains a mystery.

Thanks again for your help, I do appreciate offers of help

 

[The_Doc_Man]

OK, I understand working with accessibility restrictions. I used to be a sys admin for the U.S. Navy and "restriction" was pretty much de rigeur.

It might take a bit of doing to test this, but a UNION query is just a case of taking two (or more) SELECT queries and butting them end-to-end. So the "divide and conquer" approach might help. So...

The query blows up when you try to take a DCount. So see if you can manually open the query directly. If you can, see if any of the records contain null fields.

If the query will not open, you have to find out why. Take each SELECT sub-set of the UNION query and manually open each piece to verify that it returns data. If one doesn't, then the problem is (obviously) in the part that won't return data, probably because it is somehow malformed.

The only reason a DCount("*",...) would return #Error would be because when you tried to open the query, there was an error. (OK, sounds like a big "Duh" - but there is a point to be had.) DCount can ONLY return #Error as a result when an error occurs. Other ways of opening queries give better access to error reporting. Opening the query directly to see what it returns should give error messages that you might not see when using DCount().

 

[Snowflake68]

Thanks for your reply. I have checked what you said and I can open the Union query without any issues.
There are no Null values in any of the fields of data and no fields with any errors.

However the Union query is only ever meant to return results from one of the queries.

This is how it is supposed to work.
The union query combines results from two separate tables. One for Dispatches and another for Arrivals.
Sub form has a combo box with two flags. The user selects either D or A depending what they want to see in the results set on another subform (The the union query criteria looks at the flag on the subform and uses that to return the results for either Dispatches or Arrivals. (Date criteria (using two text boxes on the main form with From and To date) is also used to filter the results.

The attached image shows the Dcount Error in the text box on the form. But this is not the only place I use the DCount so it was causing more than just the issue of not displaying the total lines in the results.

All works perfectly for me but not on the server where it needs to be.

I have now changed the way it works by having two separate queries and two separate subforms with their respective results. I simply hide the subform that is empty when the user selects the flag. So if they choose D for dispatches I hide the arrivals subform and show the Dispatches subform and vice versa.

Alhtough I have a solution I am still none the wiser as to why the union query works on some pc's and not others. I guess I may never find out but I dont like to quit so may return to this another day. Thanks again for trying to help a damsel in distress

 

[The_Doc_Man]

This is perplexing. In essence, if you can open the query directly, then DCount should be able to count records returned by it, and returning no records isn't a reason to throw an error. I.e. it is always possible to return zero records if your criteria are too strict, and DCount takes that into consideration. The fact of it being a UNION query or a simple SELECT query shouldn't enter into the picture. This is a simple SQL concept here.

However, something that you said in this thread triggered an oddball thought. You have a query that affects this UNION query in some specific way. My question is, when you tested the queries by opening them manually, were they filtered? And is there a chance that the problem isn't with the SELECT sub-clauses but rather with the WHERE clauses?

DCount is a dumb function that can ONLY return a number or a hash-tag error code. It can't tell you WHY it is failing. All we really know is that the "#Error" indicates specifically that the thing used for the query synthesized to implement the DCount could not be opened. But it doesn't point to the SELECT clause or the FROM clause or the WHERE clause etc. It just says "#Error" - so you have to diddle with it to find the error.

Stated another way: If you could DCount either leg of the UNION query individually, there is no reason to be unable to DCount the UNION query with both legs defined normally, and that is so basic that I have to think something else is badly wrong with the miscreant machine. Other things HAVE to be failing for this to fail.

 

[Snowflake68]

Just a quick update. I created local copies of the tables with the data on the pc where I am having the Dcount error issue and it doesnt produce the error anymore. But as soon as I run it against the link SQL tables it produces the error hence why Minty in suggested it was permissions. But I am logged in as Sys admin so that threw that theory out.

oh and in answer to your question "......You have a query that affects this UNION query in some specific way. My question is, when you tested the queries by opening them manually, were they filtered? "

Yes when I opened the queries manually they are all filtered.

Still ongoing....

 

[Minty]

Check the ODBC table links, and the ODBC drivers used - and how the connections are set up - 32-bit or 64-bit ODBC. If you have 32 bit Access use must use the 32-Bit ODBC DSNs if you are connecting that way.

Edit and also make sure you select a unique key if it asks you to specify one when you link the tables.

 

[The_Doc_Man]

But I am logged in as Sys admin so that threw that theory out.

Technically, no it doesn't toss out permissions issues. It is not uncommon to set permissions via a group ID or a list of individual IDs that don't include the system admin. In fact, with ORACLE you CANNOT access the database as SYSTEM. You must be in either the ORACLE account (as DBA) or in an account that has been given the appropriate access rights.

I'm not saying that SQL Server is identical to ORACLE, but rather I'm saying that the design of the rights matrix for your database back-end doesn't necessarily have to include any rights at all for user SYSTEM or ADMINISTRATOR on a remote system.

 

[Snowflake68]

Technically, no it doesn't toss out permissions issues. It is not uncommon to set permissions via a group ID or a list of individual IDs that don't include the system admin. In fact, with ORACLE you CANNOT access the database as SYSTEM. You must be in either the ORACLE account (as DBA) or in an account that has been given the appropriate access rights.

I'm not saying that SQL Server is identical to ORACLE, but rather I'm saying that the design of the rights matrix for your database back-end doesn't necessarily have to include any rights at all for user SYSTEM or ADMINISTRATOR on a remote system.

I have had someone else check the permissions for me as this area is definitely not in my skill set, and they have told me they are correct. Also I can open the tables from withing Access and run a standard select query on them without any issues. Its only the Union query that is causing the DCount issue. If I point the Dcount function at a stand select query then all is good there too. Its a bit of a mystery but it must be to do the SQL somewhere along the lines because it all works when I create local copies of the two tables.

 

[Snowflake68]

Check the ODBC table links, and the ODBC drivers used - and how the connections are set up - 32-bit or 64-bit ODBC. If you have 32 bit Access use must use the 32-Bit ODBC DSNs if you are connecting that way.

Edit and also make sure you select a unique key if it asks you to specify one when you link the tables.

Thanks, I will get this checked and report back my findings.

 

[The_Doc_Man]

Something you just noted rings a bell.

If I point the Dcount function at a stand select query then all is good there too.

I'm assuming you meant "standard." The bell that went off was that my most recent shared back-end setup had troubles until I took a different approach.

I never really found out why, but in my split database, I got a dialog box in some things when I directly referenced a table. It wanted to know where that table was located even though the table definition was correct by every measure I could find.

I found an article somewhere - and I'm sorry I can't remember where - that suggested that I make a single-table SELECT query for every table and always reference the query when I wanted stuff from that table. As soon as I took that approach, the problem went away. I'm wondering whether you are experiencing the same thing, and the failure is because there is something about the connect string of the table and somehow having that query bypasses the problem.

 

[Minty]

+ 1 For the Docs suggestion - I saw that thread somewhere as well and although I've not experienced the problem, others reported positive results.

 

[The_Doc_Man]

Thanks for the +, Minty. I'm DASHED if I can recall where I saw it but it was at least six or seven years ago, certainly not more recent, and from the Government office, so I can't even say I tagged the URL for future reference. All I can say is that when I implemented it, the problem went away instantly. Just build a front-end query that exactly covers every back-end table. Then always reference the query instead of directly hitting the table.

 

[Snowflake68]

Something you just noted rings a bell.



I'm assuming you meant "standard." The bell that went off was that my most recent shared back-end setup had troubles until I took a different approach.

I never really found out why, but in my split database, I got a dialog box in some things when I directly referenced a table. It wanted to know where that table was located even though the table definition was correct by every measure I could find.

I found an article somewhere - and I'm sorry I can't remember where - that suggested that I make a single-table SELECT query for every table and always reference the query when I wanted stuff from that table. As soon as I took that approach, the problem went away. I'm wondering whether you are experiencing the same thing, and the failure is because there is something about the connect string of the table and somehow having that query bypasses the problem.

oh yes I did mean 'Standard' fingers type too fast sometimes and my brain cant keep up.

Great Idea which I have tried but unfortunately I still have the Error in the Text box. Just to confirm I created a select query for each of the tables and then used them in the union query.

I may never get to the bottom of it but still open to try anything.