469,352 Members | 1,795 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

Function not available

470 256MB
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.
Jul 12 '11 #1
25 6713
ADezii
8,800 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. Dim ref As Reference
  2.  
  3. Debug.Print "Reference", "Broken?", "Absolute PATH"
  4. Debug.Print "--------------------------------------------------------------------------------------------------"
  5.  
  6. For Each ref In Application.References
  7.   Debug.Print ref.Name, IIf(ref.IsBroken, "Yes", "No"), ref.FullPath
  8. Next
  9.  
  10. Debug.Print "--------------------------------------------------------------------------------------------------"
Sample OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Reference     Broken?       Absolute PATH
  2. --------------------------------------------------------------------------------------------------
  3. VBA           No            C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
  4. Access        No            C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
  5. stdole        No            C:\WINDOWS\system32\stdole2.tlb
  6. DAO           No            C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
  7. ADODB         No            C:\Program Files\Common Files\System\ADO\msado21.tlb
  8. Outlook       No            C:\Program Files\Microsoft Office\OFFICE11\msoutl.olb
  9. Office        Yes           C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL
  10. MSComctlLib   No            C:\WINDOWS\system32\MSCOMCTL.OCX
  11. Scripting     No            C:\WINDOWS\system32\scrrun.dll
  12. Excel         No            C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
  13. --------------------------------------------------------------------------------------------------
Jul 12 '11 #2
CD Tom
470 256MB
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.
Jul 12 '11 #3
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Dim ref As Reference
  2.  
  3. Debug.Print "Reference", "Broken?", "GUID"
  4. Debug.Print "--------------------------------------------------------------------"
  5.  
  6. For Each ref In Application.References
  7.   Debug.Print ref.Name, IIf(ref.IsBroken, "Yes", "No"), ref.Guid
  8. Next
  9.  
  10. Debug.Print "--------------------------------------------------------------------"
Sample OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Reference     Broken?       GUID
  2. --------------------------------------------------------------------
  3. VBA           No            {000204EF-0000-0000-C000-000000000046}
  4. Access        No            {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
  5. stdole        No            {00020430-0000-0000-C000-000000000046}
  6. DAO           No            {00025E01-0000-0000-C000-000000000046}
  7. ADODB         No            {00000201-0000-0010-8000-00AA006D2EA4}
  8. Outlook       No            {00062FFF-0000-0000-C000-000000000046}
  9. Office        Yes           {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
  10. MSComctlLib   No            {831FDD16-0C5C-11D2-A9FC-0000F8754DA1}
  11. Scripting     No            {420B2830-E718-11CF-893D-00A0C9054228}
  12. Excel         No            {00020813-0000-0000-C000-000000000046}
  13. --------------------------------------------------------------------
Jul 12 '11 #4
CD Tom
470 256MB
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?
Jul 12 '11 #5
CD Tom
470 256MB
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
Jul 12 '11 #6
NeoPa
32,184 Expert Mod 16PB
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.
Jul 13 '11 #7
CD Tom
470 256MB
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.
Jul 13 '11 #8
Mihail
759 512MB
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.
Jul 13 '11 #9
CD Tom
470 256MB
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?
Jul 13 '11 #10
ADezii
8,800 Expert 8TB
@CDTom: Not sure if this was simply a Typo on your part (Post #6, Lines 2 and 3)
Expand|Select|Wrap|Line Numbers
  1. Max(Mid(warmupcomment),1,4)
will not work, while
Expand|Select|Wrap|Line Numbers
  1. Max(Mid([warmupcomment],1,4))
will work
Jul 13 '11 #11
CD Tom
470 256MB
Sorry but it was a typo on my part. I did forget the [
Jul 13 '11 #12
NeoPa
32,184 Expert Mod 16PB
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.
Jul 13 '11 #13
CD Tom
470 256MB
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.
Jul 13 '11 #14
ADezii
8,800 Expert 8TB
@CDTom: As a last ditch effort, you can Unregister and Re-Register the Access and VBA Type Libraries for 2007:
Expand|Select|Wrap|Line Numbers
  1. 'Access
  2. regsvr32 -u "C:\Program Files\Microsoft Office\Office12\msacc.olb"
Expand|Select|Wrap|Line Numbers
  1. 'VBA
  2. 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?
Jul 13 '11 #15
CD Tom
470 256MB
Yes, I have tried some of the other Mid() functions and they don't work either.
Jul 13 '11 #16
CD Tom
470 256MB
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.
Jul 13 '11 #17
Jaco B
1
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
Jul 18 '11 #18
CD Tom
470 256MB
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.
Jul 18 '11 #19
NeoPa
32,184 Expert Mod 16PB
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.
Jul 18 '11 #20
CD Tom
470 256MB
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.
Jul 18 '11 #21
NeoPa
32,184 Expert Mod 16PB
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.
Jul 18 '11 #22
CD Tom
470 256MB
Ok, I don't understand how to print that to a file.
Jul 18 '11 #23
NeoPa
32,184 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print {values}
to :
Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 19 '11 #24
CD Tom
470 256MB
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
Jul 20 '11 #25
NeoPa
32,184 Expert Mod 16PB
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.

Expand|Select|Wrap|Line Numbers
  1. Dim intFileNo As Integer
  2. intFileNo = FreeFile()
  3. 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.
Jul 20 '11 #26

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by Ken VdB | last post: by
1 post views Thread by Yannick Warnier | last post: by
1 post views Thread by reeteshsingh | last post: by
21 posts views Thread by H9XLrv5oXVNvHiUI | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.