Function not available

[CD Tom]

For some reason one of my users is getting a function not available on a Access 2007 program that has been running on other computers with no problem. The computer she is using is a new 64bit machine running windows 7. The program has never been installed on this machine, I sent her the installation program (I use the developer package solutions) but now when she tries to run the program she get this function not available, the statement is a DMAX("Mid statement. I know this is a missing reference but for the life of me I can't figure out which one is missing. If anybody can point me in the correct direction I would appreciate it. Also is there any way to find which ref is missing in a compiled program?
Thanks for any help.

[ADezii]

Dim ref As Reference
 
Debug.Print "Reference", "Broken?", "Absolute PATH"
Debug.Print "--------------------------------------------------------------------------------------------------"
 
For Each ref In Application.References
  Debug.Print ref.Name, IIf(ref.IsBroken, "Yes", "No"), ref.FullPath
Next
 
Debug.Print "--------------------------------------------------------------------------------------------------"

Sample OUTPUT:

Reference     Broken?       Absolute PATH
--------------------------------------------------------------------------------------------------
VBA           No            C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Access        No            C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
stdole        No            C:\WINDOWS\system32\stdole2.tlb
DAO           No            C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
ADODB         No            C:\Program Files\Common Files\System\ADO\msado21.tlb
Outlook       No            C:\Program Files\Microsoft Office\OFFICE11\msoutl.olb
Office        Yes           C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL
MSComctlLib   No            C:\WINDOWS\system32\MSCOMCTL.OCX
Scripting     No            C:\WINDOWS\system32\scrrun.dll
Excel         No            C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
--------------------------------------------------------------------------------------------------

[CD Tom]

Thanks for the quick reply. I've added this code to my program but when I run it I get the following message. Method 'FullPath' of object 'Reference' failed
Not sure what this means or what I'm doing wrong.

[ADezii]

Very strange Tom, let's go to Plan 'B'. Try listing the GUID instead, yoou can always reference this Value in the System Registry, then extract related information.

Dim ref As Reference
 
Debug.Print "Reference", "Broken?", "GUID"
Debug.Print "--------------------------------------------------------------------"
 
For Each ref In Application.References
  Debug.Print ref.Name, IIf(ref.IsBroken, "Yes", "No"), ref.Guid
Next
 
Debug.Print "--------------------------------------------------------------------"

Sample OUTPUT:

Reference     Broken?       GUID
--------------------------------------------------------------------
VBA           No            {000204EF-0000-0000-C000-000000000046}
Access        No            {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
stdole        No            {00020430-0000-0000-C000-000000000046}
DAO           No            {00025E01-0000-0000-C000-000000000046}
ADODB         No            {00000201-0000-0010-8000-00AA006D2EA4}
Outlook       No            {00062FFF-0000-0000-C000-000000000046}
Office        Yes           {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
MSComctlLib   No            {831FDD16-0C5C-11D2-A9FC-0000F8754DA1}
Scripting     No            {420B2830-E718-11CF-893D-00A0C9054228}
Excel         No            {00020813-0000-0000-C000-000000000046}
--------------------------------------------------------------------

[CD Tom]

Ok, that worked and I can see the results in the immediate window. How do I execute this on a compiled runtime program that is on a different machine?

[CD Tom]

I tried running the Check Ref program on my users machine and doesn't give me any answers. Do you know what reference or .dll is used for the following function. Max(mid(warmupcomment),1,4) that's the function that shows up in the error window. The exact message is Function is not available in expressions in query expression Max(mid(warmupcomment),1,4) this works on all other machines that has the program loaded. I've looked at all the references in my machine and then using windows explorer have compared then to what's on her machine by looking in the directories for the same files. It looks like everything is there. She doesn't have Access loaded on her machine or I would send the actual program and look at it there.
Right now I'm at a loss as what to do, so any help is greatly appreciated.

I did find a couple of references that were missing on her machine and copied those over. Do I need to do something else besides just coping them?

Thanks

[NeoPa]

Tom, Is the capitalisation correct in the error message you posted? If it is then you appear to have problems with both the Mid and the warmupcomment references.

[CD Tom]

I probably didn't type it exactly as should have been. However the statement works on all other systems except the one machine. I've developed the program on windows 7 on a 32bit machine, but I have a laptop that is a 64bit with windows 7 and it works fine on that machine. I can't get back on her machine until tomorrow so I will try and get the exact spelling. Although I don't think that's the problem.

[Mihail]

Just an idea: Compare references and see what references are in the computer where your program run without problems and are NOT in a computer where your program do not make the job.

[CD Tom]

The only way I've been able to check the references is by opening the program on my machine and going to the references looking at where the reference is located and then using explorer on the other machine checking the reference location and see if it is there. Like I mentioned earlier I have found a couple that were missing and have copied them from my computer to the other machine. I guess my question, is just coping them to the other machine all I needed to do or do I need to do something else?

[ADezii]

@CDTom: Not sure if this was simply a Typo on your part (Post #6, Lines 2 and 3)

Max(Mid(warmupcomment),1,4)

will not work, while

Max(Mid([warmupcomment],1,4))

will work

[CD Tom]

Sorry but it was a typo on my part. I did forget the [

[NeoPa]

ADezii:
Not sure if this was simply a Typo on your part (Post #6, Lines 2 and 3)

Tom,
As you'll no doubt appreciate now, while mis-typing may not identify what your solution may be, it can certainly waste time and effort of various people looking in the wrong places for you. I appreciate sometimes it's difficult (particularly when working from a machine where the message isn't directly available) but I hope you have a better understanding now of why it can be so important to relay these things accurately.

I suspect neither of these issues (The case of some letters and the missing parenthesis) is relevant in this case, as you have explained that it works on your machine and various others, but when you're working with very little information as we are, the accuracy of what you do have is critical.

[CD Tom]

I understand and will try and be more accurate in my typing. The machine I'm working on is in New Mexico and can only connect up at certain times. Thanks for all the help, I just wish I could find an answer.

[ADezii]

@CDTom: As a last ditch effort, you can Unregister and Re-Register the Access and VBA Type Libraries for 2007:

'Access
regsvr32 -u "C:\Program Files\Microsoft Office\Office12\msacc.olb"

'VBA
regsvr32 "C:\Program Files\Common Files\Microsoft Shared\vba\vba6\vbe6.dll"

P.S. - Do so at your own risk.

P.S2. - If the problem is with Mid() and a Missing or Corrupt Reference, then I would assume that other String Manipulation Functions would not work either. Have you tested for this?

[CD Tom]

Yes, I have tried some of the other Mid() functions and they don't work either.

[CD Tom]

I registered the VBA but because the machine doesn't have Access installed was not able to register the msacc.olb, remember this is a runtime access program so the machine doesn't need Access.

[Jaco B]

Hi. Any luck with this. I have the same problem and been struggling with this for days. Run 64bit machine with Windows 7 SP1. The only thing that i changed was the ADO workaround http://support.microsoft.com/kb/2517589

[CD Tom]

No luck so far. Haven't heard anything since I tried to re register and that didn't work. If I ever get this figured out I'll let you know what I did.

[NeoPa]

Tom, why not go back to post #4 and try printing to a file instead of the Immediate Pane (for the remote machine). That might give you something to work with.

[CD Tom]

I just did something that may help. I have a 64bit machine that I installed the program on. When I run the runtime version (.accdr) of the program I get the Function not available error, but when I run the non runtime version (.accdb) I don't get any error and the program runs fine. Do you think that there is a problem with the run time version of the program not working on a 64bit machine. Is there a 64bit version of Access runtime? I'm going to check on that.
Still looking for help.

[NeoPa]

CD Tom:
Still looking for help.

Tell us that again when you've reported on how you handled my last post (#20). If there's anything there you don't understand, you need to say so.

[CD Tom]

Ok, I don't understand how to print that to a file.

[NeoPa]

My system just crashed on me so I can't look anything up or give details, but essentially the Debug.Print facility is similar syntax to writing to a file. The file needs to be assigned an integer value to pass to the Open # command which opens the file (Open it for Append). Close it when finished, but each display changes from :

Debug.Print {values}

to :

Print #3 {values}

(assuming 3 is the integer value of the file in your code.

Let me know if this is not enough. I'll probably be on again tomorrow with the ability to look it up for you in more detail.

[CD Tom]

I was finally able to find out what reference was missing. Using my 64bit machine and installing the run time .accdr of the my program I was able to test more. The problem ended up being the Microsoft ActiveX Data Objects library was missing. I don't know why but once I put that back in everything worked fine.
Thanks for all the help you guys are a great source for any help. Thanks again

[NeoPa]

8-) Sorry I couldn't be more specific earlier.

I can put in some example code that may help with this, but there are so many options it would be irresponsible for me to make a specific suggestion. I often use a specific Binary mode that gives me full control of everything that is written out, but that is actually quite a complicated approach and is used very rarely. I'll shove some example commands in but with the proviso to check the details in Help before using.

Dim intFileNo As Integer
intFileNo = FreeFile()
Open strName For Binary Access Read Write Lock Write As #intFileNo

FreeFile() determines for you (in the code) a valid, free, file number to use.

Open# is where all the options and complications come in. A mode of Output, Random, or even Append may suit your purpose of logging the details.

PS. I add this now in case any one coming after you might find it helpful. I appreciate you have already resolved your issue. Well done for that. Such remote problems can be very tricky to handle.