Error in DAO when using a function 1

[TC002]

I posted this problem 4 or 5 days ago in forum "Visual Basic (Microsoft Databases) under thread709-260314 and decided to post it here now to see if I can get some further attention. If anyone has had this problem and was able to solve it then please let me know how.


1. Why do I get an error using VB6 and DAO when including the ROUND function in a select statement one one computer and not the other - 3085?

2. How to correct?

On Developer PC:
VB6 IDE, NT, MDAC_Typ 2.5, Jet3.5 SP3, DAO 3.51
Database is an MDB '97 (Tables only) - no MS Access application

On PC #2
Win 98, the VB application created with the first PC and installed using P&D, The same MDB ('97 format), MDAC_Typ 2.5, Jet3.5 SP3, DAO 3.51, NO no MS Access

Note: I need an answer that will work for the above set-up only - not for Microsoft ACCESS!

I appreciate any help that I could get. I read 2 Kb articles concerning this but the first applied to MS Access 2000, and the second applied to MS Access '97 - but to the application - I wasn't able to figure out how to apply it in my case to my VB program using just an 97 MDB.

 

[Sawatzky]

Dear TC002,

1) Please provide , what error you get.

2) Do you dim your variables like this:

dim my_rec as recordset

or like this

dim my_rec as DAO.recordset
(which is the way it should be done)

3) I was told not to use DAO any longer as no one knows how long it will stay in existense, but to use ADO


regards Astrid

 

[TC002]

The error as stated above is 3085
From the error listing under Help the text it:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Undefined function <name> in expression. (Error 3085)

You entered an SQL expression that includes a Function procedure name that cannot be recognized. Make sure the function exists, that it can be used in SQL expressions, or check the expression to make sure you entered the name correctly.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

DAO is Ok to use, and in this app it doesn't matter if I use DAO.Recordset or Recordset - (I mean that this is not causing the error)

as no other references to any other recordset object libraries are used (just DAO).

The error must have something to do with the type library of DAO 3.51 - meaning some bug.

I want to stay a while with DAO/Jet 3.51 because it is faster and more effective than ADO/Jet when using a '97 MS Jet Database (Mdb). In any case, Jet will be around for a long time to come - it will remain the native engine for an MDB no matter what wrapper is being used....

 

[strongm]

I'm afraid that the people in the Access forum were pointing you in exactly the right direction, but you didn't seem to want to listen to them.

The error is caused by the fact that Access 97's Jet SQL doesn't support Round(). As a result it assumes that this is a user function, and you can't call user functions from VB.

Access 2000's Jet SQL, however, does support Round().

Now, before you make noises about how there is no Access apllication you should be aware that Access, the application, is from a certain point of view a front-end that ties together a variety of DLLs. Some of those DLLs make up the Jet Engine, and it is the Jet Engine that implements, interprets, optimises, and runs Jet SQL.

Those same DLLs are used by VB when it is accessing an MDB, even if you don't have Access itself installed.

So the issue is that on one of your machines you have at least one or more components that date from the Access 97 era (the machine that flags the error), and one machine that has later versions.

I suggest that you download the following file:

http://download.microsoft.com/download/dasdk/CC/2.7/WIN98MeXP/EN-US/cc.exe

to determine which components are too old to support Round().

 

[strongm]

(of course I'd forgotten that the version checker I recommended doesn't check some of the Jet dlls, so it might not be as useful as it might have been)

 

[TC002]

I've been away for 2 weeks and just now getting back.

I am not a person to critize help I may get, but because of several comments such as

&quot;...but you didn't seem to want to listen to them&quot; and

&quot;Now, before you make noises ...&quot;

I need to say that this answer has given someone who is a little in the clouds and talking about something they aren't themselves sure about - which the latter normally isn't bad when trying to figure out an answer to a problem in a discussion- but to critize a person for NOT agreeing, when the proposed answer or logic given by that same person is evidently wrong, is not a professional way of doing things. I suggest you hold back in the future of making these remarks, because as we all know, in this programming world, anything and everything is possible and nothing is for sure.. I myself will not post anymore questions in YOUR forum Mr. Strong - I guess that is how you feel about it. It is good to help others as much as you have - FOC - no, it is commending - but don't let it go to your head.

According to what I have experieced myself and learned:
1. Jet Sql DOES support the Round function, and many other functions that were not supported by Access PRIOR to Access 2000 - that is fact. Access 95 and 97 didn't - but during the SAME time frame, Jet did.
There is a parameter in Jet that prevented Jet from executing certain functions while running from Access - not so when using Jet through another developing enviroment. Heck, you could even use ALL VBA commands in Jet SQL if you wanted.

If this wasn't so, how could a program written almost 5 years use the Round function WITH-IN a SQL statement?

2. Jet does NOT use Access DLLs - but the other way around when it comes to the database. Jet was developed as native language to a Microsoft Database and not for just Access. It has the capability to interface with VBA, but not with Access. Access has the capability to interface with Jet, just like an application you create does - you also would not say that Jet uses your application's DLLs, or? No, your application, and ACCESS uses Jet. And ACCESS (and other Office Apps) and Jet each use VBA.

3. Jet has the capability to use VBA in it's SQL statement syntax. It interfaces with the VBA.The Round() function has been around in Jet for a long time....

4. The issue that I am having is a <<compatabilty>> issue between Jet 3.5, and I just do not know which DLL(s) is causing the problem.

5. Version checker cannot be used because it is not compatable on non-US systems.

 

[petermeachem]

Handbags at dawn lads.

Look instead of arguing constantly about why something works or doesn't, why don't you just do it a different way that does work. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[TC002]

Who was arguing up to the point that I was told that I wasn't listening?
I thanked the two answers in the other forum, said it didn't work, got no more responses, asked the same here in this forum, got one answer and responded to the question asked of me, and then got the response from strongm.

I stated my case as everyone else in this forum does - and there are alot of ways of doing things differenty but people insist on finding the answer to their current problem, for very good reasons, and this is one of them - and I am willing to use another way, (please note that this may involve alot of changes - which is fine, but that may take a while). The problem is, there are times when we need to fine an answer the fast way, esp. when customers are waiting.

I did read a few of the answers given by some people in this forum, including above people, and I thought the opinion was pretty much the same:
To solve a problem and not the symptoms.

But it looks like I may have stepped into a nest. I did commend on the amount of help given by strongm to so many people, and I guess your answer shows that some will be on that side whether the person's answer is right or wrong, simply because for that reason.

I only kindly asked for help, and the help I was given wasn't working - I reasoned why. Then I was told that I wasn't listening and that in my next response do not go and make noise. What's the deal here?

Do forget, I didn't start the accusations - but I guess that doesn't count here.

Oh no, but that's ok, I will get out of the nest.

 

[petermeachem]

and I guess your answer shows that some will be on that side whether the person's answer is right or wrong, simply because for that reason

Did you really read that into what I wrote?

People help here in their spare time. If you want professional help, then I suggest you pay for it. Personally I would have recoded the bit that didn't work. There's nearly always more than one way to do something.

And for heaven's sake, calm down. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[CCLINT]

He/she may have rightly felt that they were being looked down upon or mocked....what ever...

To the problem: It is a compatability problem. The Round function should be working with DAO 3.51. And yes, it will not in Access but only through JetVBA outside of the Office enviroment.

Intermediate asnswer: I'm leaving with-in the next 5 minutes for a 3 week vacation - don't be alarmed - that's not a rude answer of course, just in case more questions are asked I will not be able to answer each day, or that quick.
Get the Process explorer from

http://www.sysinternal.com.

Print out a protocol from both systems for the program in question and compare versions. Not always do installation programs install all files needed. And then it could be just the fact that a newer DLL on the system wasn't set correctly for backwards compatability.
Any ways, the explorer will show all dlls used by the program - you will find that there will be one(s) that are not the same version, which is causing the problem.

 

[CCLINT]

That was supposed to read:
&quot;And yes, it will not in available Access 95 or 97, but only through JetVBA, outside of the Office enviroment.

 

[kaz8896]

Peter and Mr. Strong definitely have some big heads so lets just ignore them. CCLINT makes a good point but before you try that have you tried to run the mdac 2.5 setup on PC #2 that could very quickly resolve this problem. It also might not do anything. In which case I would go with CCLINT's response.

 

[CCLINT]

Just was curious - no, haven't gone anywhere yet like I planned - typical....

Well, many people have really appreciated, made alot of money, had alot lesser headaches, and have been able to go many miles, because of &quot;those heads&quot;.
We cannot (in the above words) &quot;ignore&quot; people like the ones mentioned, because we need people like them.
If everyone would ignor me because of my bad habits, or for saying something wrong in my hast, or on a bad day, I then might as well go and live on a deserted island. (I am not saying here that someone has said something wrong). And, How many times have all of use claimed something to be correct when only ending up eating our hats later. Nothing wrong with that - doesn't taste good, but you haved learned something and have advanced in the process - and what really counts is admitting to it.

We all have our good sides and bad sides, good days and bad days, and, like me, sometimes alot of stubborness. You have to learn to always take the good with the bad, and accept people as they are, otherwise you will never get anywhere.

And in these forums, everyone wins, even if a person only answers questions, which helps to the also to see things new ways and helps to embedded those ideas more firmly. That's how we advance by discussing and hacking things out here.

Some people also say things straight forward the way that they see it at the moment, and that may offend others - good old pride in the way again.

I can understand where the person may have thought sides were being taken though - comments were made first against (if you can really even say that) the person (from their view), and it was then the response to that first comment, that was criticised.

I think alot of people have started first in a forum by asking a question, and then have continued later answering them - making &quot;payback&quot; contributions. Maybe this one person would have been able to help one or more of us in a difficult situation someday. And maybe this one has already freely contributed in other forums, or where ever, the past (sounds so).

To the technical side of the last comment: The user stated originally that MDAC was loaded with the most current version for DAO.

So let's get on with business at hand, or vacation.

 

[kaz8896]

Sorry I wasn't specific enough but what I was trying to get at was....Did you install MDAC 2.5 through the Setup Package of the program on maching #2. Because if you did I myself have expirienced problems using the P & D Wizard with data access apps. If so try running the MDAC setup by itself on #2.

CCLINT,

I guess you're right we shouldn't ignore these people. But these people need to show some respect and common courtesy when addressing people's problems. They shouldn't act so cocky.

 

[TC002]

1). Ok guys, I'll be the first to say &quot;I'm sorry for any mis-understandings that I have had/made or caused, or any discomfort, or wrongly criticism&quot;.

2). I guess one can answer however they feel as long as no hard insults get exchanged - esp. when it is free service, and it is the thought and attempt to help that counts, and the main thing is that problems get solved.

Thank you!

3). MDAC was previously installed seperately and so was Jet3 SP3 but it didn't help. Thank you!