Text functions not working (again) 2

[GDGarth]

I've had this problem before - my database has some functions like "Mid", or "Left", in queries and code. I also use "Str" to convert a value to a string in some circumstances. These have worked fine for quite a while.

All of a sudden, the functions don't work on a machine that has Access Runtime installed. They work fine on my computer, with the full version. They worked fine on the runtime machine yesterday. I checked the database, and the references are set correctly. An old version of the database doesn't work, either.

I'm going to try to reinstall runtime, to see if that helps.

Any other ideas?

 

[Trendsetter]

It's the old problem of "References"

If you can get to a code page within your application in the toolbar under "Tools" go to "References" and the list will have a "MISSING" entry, uncheck this object and reinstall via the Browse button

 

[psemianonymous]

What he said.


Also for me, I get reference-like errors when I open a form using the "Startup" option. If I switch it to open via the autoexec macro, this error disappears. (I'm using Access97)

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[GDGarth]

I just don't get it.

How can I identify the missing reference? I don't see anything.

Why does this work on my computer, but not on the one with runtime?

I created a new database, and imported the objects. Now the functions work. When I check references on the new MDB, it has fewer things checked than the original. The only problem is, to use the new database, I'll have to recreate my link specifications (since I can't seem to import them), and get security set.

I tried compacting and repairing, both with Jetcomp as well as the built in utilities.

 

[psemianonymous]

Here's something that will probably not help, but it may:


I've heard that you have to copy DLL's as well as your run-time .EXE, so it may be a case of a missing file. I don't know which DLL's you need, I don't know where they need to go. I just know that (maybe) you need to have them.

I just read over my above comments and my reaction: "That was terribly unhelpful."

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Lightning]

Sometimes it can be the ORDER of the references that is causing problems. If you have references set to the ADO library and the DAO library, the one that is higher in the order will have priority. This MAY be causing you a problem.

Try changing the order of your references. This may help

HTH
Lightning

 

[GDGarth]

I'm trying my new copy now. Nothing I do with references seems to make any difference. I've got the references set the same in the new one as the old one.

The puzzling thing to me about this is that it works on my computer, but not the other one. I don't think I can set the references in the run time version (which used to work OK)

I'm using Access 97, if that makes any difference.

Hopefully, my new database will work, and I'll at least have the problem solved, for now. I'd still like to figure out an easier way to fix it.

 

[PaulBricker]

This problem is on everyone's list except MS's. It's talked about on every major forum around and no one is able to really able to provide an answer. Apparently there isn't a single solution (as you have probably found out). The problem can be if a reference is 'Missing' but it's not easy to explain just what this means. Your Built-in Functions like Left, Right, Mid etc. are stored in .dll files. No one knows exactly which .dll files but that's where they are. Applications share these .dll's and sometimes, the location of the file gets lost to Access. One way to try and reconnect is to deselect all the references in your database, reselect them and then recompile the Db. Many times this will send Access out searching for the location of the .dll and you are back in business. Other times, importing into a new database works. Sometimes you will actually see the word 'Missing' in your References library. Then you simply have to deselect the Missing library, close it out, and go back in and reselect it. Sometimes you have to make sure the ServicePaks for different machines are the same before a .dll library can work with the Built-in functions. I have done some searches on a lot of forums to see what people can tell you and not even the top performers have any real solution.
Good luck.

Paul

 

[GDGarth]

In case anyone's interested:

I imported all the objects into a new database, recreated the link specifications, and set up security.

Everything works fine, now. I still don't know what was wrong, but I think I'll keep the old MDB around and play with it - it'd be handy to know how to fix it in five minutes, instead of five hours.

 

[GingerR]

try this FAQ: faq181-936
i put this little bit of code in autoexec or main form OnOpen of all my db's - it will do all you are suggesting automatically when a user launches the db. the whole MISSING/unselect a reference/select a reference...ugh. i only know enuf to get thru it but like most others dont really totally understand it. this FAQ helps tho.

good luck--g

 

[PaulBricker]

Thanks for that link GingerR, it's the first decent help I've seen on this problem in a long time. I can't tell you how much I looked in the KB and never came across that particular article. If you look for the Undefined Error message(which is the error message you get with this problem) in the KB, you don't come up with anything helpful. Have a star.

Paul

 

[GDGarth]

GingerR,

You get a star! This sounds like exactly what I've been seeing.

I haven't had a chance to try it (since I've already rebuilt the database), but I'll try it when I get a chance, and post back the results.

Thanks again.

 

[GDGarth]

I tried the fix, but it didn't work. I got the message box saying it was repairing the references, and it took a minute or so, but when I tried running my routine, it had the same error (Function not available in query expression).

 

[GingerR]

sorry then i dont know where to go from there. It works for me, in fact it has never taken more than a millisecond (i have never even seen the message box flash). but like i said earlier, i know nothing about these damn reference things except to get it to work and MOVE ON....

anyhow, GDGarth--which function is it honking up on?
maybe it is the order of your references. this functionality doesnt have anything to do with that, it's how you set up your references yourself. also--i dont know anything about using runtime version. sorry.

 

[GDGarth]

It doesn't like Left, Str, Mid, etc. The wierd thing is, it works on my computer (with the full version of Access), but not on another (with Runtime). Two days ago, it worked fine on all computers.

My copy is working fine - and the references are set exactly the same in the copy.

The good thing is, I'm going to set it up so it's easier to recover from this in the future (which means, of course, that it will never happen again).

Thanks again for the help.