Error – 3085 DOA. Database – Undefined function ‘DSUM’ in expression.

[syscrash]

I am working with VB6 looking at an Access 97 database. I have come across a problem where the code creates a Query in the Access file, then assigns that query to a Recordset (I know the query is fine because when I run open it in Access it shows the desired results). The problem is with some DSum, DMin, and DMax statements in the Query. It assigns the Query to the Recordset with no problem (.Data1.RecordSource = "SELECT qryJCDSSchedLU1.* FROM qryJCDSSchedLU1 ") but then it runs a refresh (.Data1.Refresh) and then this gives an error. (Error – 3085 DOA. Database – Undefined function ‘DSUM’ in expression.) If I remove the DSum then it gives me this error on the DMin, and if I remove that it gives me the error on the DMax.

Can anyone give me any suggestions on what I need to do to fix this? Thanks in advance for any help that you can give.

 

[Andrzejek]

Your VB app runs this quesry:

SELECT qryJCDSSchedLU1.* FROM qryJCDSSchedLU1

Unless you have a Table (or view) in your Access named qryJCDSSchedLU1, you will ger an error.

Your VB app does not know about queries in your Access. It looks at the tables or views, if you have any.



Have fun.

---- Andy

 

[syscrash]

Thanks for your response Andy. Yes the VB runs that query, and the App does know about it because I create the query on the fly. I do this in other areas too but in this case it is having a problem with the DSum command in the Query. However I can run the query that was created by looking at the database in Access and it runs fine.

 

[strongm]

DSUM is an Access function, not a SQL function. If you try and run a query that includes Access (or user-defined)functions from outside of Access it won't work.

 

[syscrash]

Thanks for your reply. Maybe it would help if I posted the query that is being used to build the query in VB....

strSQLtmpDrawLookup = "SELECT JCDrHdr.DrawHdrID, JCDrHdr.ReqNo AS Request, JCDrHdr.PrjNo AS Project, JCDrHdr.JobNo AS Job, JCDES.DesName AS JobName, JCDES.DesLoNo AS LotNo, DSum('RevConAmt','JCDrDtl','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & ' And DrawNo=' & DMax('ReqNo','JCDrHdr','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & '')) AS ContractTotal, DSum('AmtThisReq','JCDrDtl','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & ' And DrawNo=' & DMax('ReqNo','JCDrHdr','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & '')) AS RequestAmnt, DSum('BalnToCompl','JCDrDtl','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & ' And DrawNo=' & DMax('ReqNo','JCDrHdr','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & '')) AS Balance"
strSQLtmpDrawLookup = strSQLtmpDrawLookup & " FROM (JCDrLndr RIGHT JOIN (JCDrHdr INNER JOIN JCDrDtl ON JCDrHdr.DrawHdrID = JCDrDtl.DrawHdrID) ON JCDrLndr.LenderID = JCDrHdr.LenderID) LEFT JOIN JCDES ON (JCDrHdr.JobNo = JCDES.DesJbNo) AND (JCDrHdr.PrjNo = JCDES.DesPrNo)"
strSQLtmpDrawLookup = strSQLtmpDrawLookup & " GROUP BY JCDrHdr.DrawHdrID, JCDrHdr.ReqNo, JCDrHdr.PrjNo, JCDrHdr.JobNo, JCDES.DesName, JCDES.DesLoNo"
strSQLtmpDrawLookup = strSQLtmpDrawLookup & " HAVING (((JCDrHdr.ReqNo)=DMax('ReqNo','JCDrHdr','DrawHdrID=' & [JCDrHdr].[DrawHdrID] & '')))"

...again I know that works because when I run the query in Access it is fine. If this is an Access function and not an SQL function does anyone know of a different way I could do this?

 

[JoeAtWork]

I know that works because when I run the query in Access it is fine

As StrongM said, running it outside of Access won't work.

In regular SQL you would do a kind of query-in-a-query that returns a single value, for example:

SELECT JCDrHdr.DrawHdrID, (SELECT Max(BalnToCompl) FROM JCDrDtl WHERE DrawHdrID='999') AS MaxBalnToCompl FROM MyTable

 

[strongm]

I'll repeat: DSum (and DMax)* are Access function(s) and can only be used in SQL queries that run within the Access environment.

Using them in SQL queries from OUTSIDE the Access environment (as you are doing) means they will not work.

One solution would be to re-express DSum (and DMax) as SQL subqueries based on the SQL Sum and Max functions



*I can't find DMin anywhere in your SQL string, so I'm not sure why you have ever seen a DMin error as you suggest yoiu have done in your original post

 

[syscrash]

Thanks for your replies StrongM and Joe...I am sorry I do not understanding what I am doing wrong. Here are the steps I am taking when I get to this routine...
1. I delete the query if it is already there.
2. I recreate the query using the SQL that I gave earlier.
3. I assign my record source to the query (shown earlier).
4. I refresh the record source (shown earlier). to be sure it has the most current data..and I get an error (shown earlier)..
5. If I think go look at my query in Access it will show the needed results, so the query, even though it is being created from VB, is good and works fine.


How would I create/use an SQL sub query in this case??

**I have not given the example of the DMin because it is in a different routine. I am hoping that if I can understand how to fix this one I can also figure out how to fix the other one. I can't put several routine issues in here because it just make it that much more confusing for anyone to help me.

 

[syscrash]

Thanks for your replies StrongM and Joe...I am sorry I do not understanding what I am doing wrong. Here are the steps I am taking when I get to this routine...
1. I delete the query if it is already there.
2. I recreate the query using the SQL that I gave earlier.
3. I assign my record source to the query (shown earlier).
4. I refresh the record source (shown earlier). to be sure it has the most current data..and I get an error (shown earlier)..
5. If I think go look at my query in Access it will show the needed results, so the query, even though it is being created from VB, is good and works fine.


How would I create/use an SQL sub query in this case??

**I have not given the example of the DMin because it is in a different routine. I am hoping that if I can understand how to fix this one I can also figure out how to fix the other one. I can't put several routine issues in here because it would just make it that much more confusing for anyone to help me.

 

[JoeAtWork]

If I think go look at my query in Access it will show the needed results, so the query, even though it is being created from VB, is good and works fine.

DSum will only work in Access.

From what I see of your code, you are probably using an ADO data control. ADO does not have DSum. Forget about what works in Access, what matters is what works in ADO. ADO uses SQL syntax that is different from Access syntax.

How would I create/use an SQL sub query in this case??

I gave you an example in my first post.

 

[BobRodes]

syscrash, the reason that your code doesn't work is because you're not running the Access application. There are numerous functions in "Access SQL" that are only able to be interpreted by the Access application. DSum, DMax and DMin are examples of these. When you run your code, you're not running the Access application, Instead, you're you're using a technology called JET (Joint Engine Technology) to open the Access file and read the data. JET only supports a more standard set of SQL functions.

Now, one other approach to solving your issue is to use Automation to open the Access application from VB and run your query that way. It's more (a lot more) overhead, but it does let your query run as it stands.

If you want to get a little clearer on the size of the can of worms involved, google to "ANSI SQL" and do a little reading. This should give you a working understanding of what to look out for.

HTH

Bob