Problems with Dcount on String field programatically 1

[efinnen]

Hi All

First off, let me say thanks for any advice or help you can provide with this problem. So I’m a bit confused with whats going on right now. I have two sets of tables that I need to run a DCOUNT routine (VBA) on and I am at a loss why the second one isn’t working programmatically.

The first command works fine (in this case the field TRIPID is a number, and the table is called SPECIES_02_03). Please drop me a line if you need all the code..

cmd.CommandText = "UPDATE ANALYSIS SET NMBR_TRP = DCount(""*"",""SPECIES_02_03"",""TRIPID="" & [TRIPID]);".

The problem is when I have to do something similar on the other table (OBSPP) where the field LINK1 is a string it doesn’t work programmatically.

cmd.CommandText = "UPDATE Analysis SET NMBR_TRP = DCount(""*"",""OBSPP"",""LINK1='"" & [LINK1] & "" '"")"

I was initially thinking that I might have screwed up somewhere feeding in the double quotes or something along those lines. However, if I put a break in the code and extract what cmd.commandtext is I get the code below. When I cut and past that into a standalone query in Access it works perfectly.

UPDATE Analysis SET NMBR_TRP = DCount("*","OBSPP","LINK1='" & [LINK1] & " '")

Any suggestions would be greatly appreciated since I’m at a bit of a loss in regards to why this sql snippet isn’t working programmatically but is working in Access.

Thanks
-eric

 

[mp9]

Try:

cmd.CommandText = "UPDATE Analysis SET NMBR_TRP =" &  DCount("*","OBSPP","link1='" & [link1] & "'")

http://www22.brinkster.com/accessory/

 

[efinnen]

I just tried it. I'm getting a Compile Error : External name not defined on the last LINK1

 

[PHV]

And this ?[tt]
cmd.CommandText = "UPDATE Analysis SET NMBR_TRP = DCount(""*"",""OBSPP"",""link1='"" & [link1] & "[!]"'"[/!]")"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[efinnen]

That last one would be what did it.. Unfortunately I also just figured oout that Dcount blows up when you feed it a large amount of records programatically (system resources exceeded). However, it does work when embedded as a query in Access.

So with that in mind. I guess my VB will just call the query instead of feeding it the SQL line.

Thanks for the help..

-eric

 

[PHV]

Dcount blows up
and this ?
cmd.CommandText = "UPDATE Analysis SET NMBR_TRP = (SELECT Count(*) FROM OBSPP WHERE link1=Analysis.link1)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[efinnen]

The last one gives me a "Operation must use an updateable query.

Your posting at 1:19 seems to solve the problem (sort of). I haven't established theexact number that causes it to blow up. But it appears that it doesn't run when part of the Analysis table is to big. I haven't been able to hunt down if its number of records, number of unique records in LINK1 or a combination of both.

-e