Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Windows 7 and VB functions in Access XP

    I'm having trouble with the Access XP installed on a W7 computer. I get an error that "Function is not available in expressions in query expression '(Left([FirstName],1)<>")").

    I also get the error when we use an expression to format dates (Format (Now(),"mm/dd/yyyy") on this computer only.
    All other XP computers using this database are functioning properly.

    I assume this is due to W7 and the interface with the Visual Basic Library references. I checked the copy of Access on the W7 computer and it has all the same references as the Windows XP computers and is able to find those references.

    Thanks in advance for your help.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    This has all the symptoms of being a References problem. Double-check on the computer having problems to make sure there isn't a broken reference. Here is a way to check them them via code if you need it:

    Dim strMessage As String
    Dim strTitle As String
    Dim bytButtons As Byte
    Dim refItem As Reference

    On Error Resume Next
    For Each refItem In References
    If refItem.IsBroken = True Then
    strTitle = "MISSING Reference"
    strMessage = "MISSING REFERENCE!" & Chr(10) & Chr(13) _
    & "Location: " & refItem.FullPath & Chr(10) & Chr(13) _
    & "GUID: " & refItem.Guid & ", " & refItem.Major & ", " & refItem.Minor & Chr(10) & Chr(13)
    bytButtons = 16 'critical symbol
    Else
    strTitle = "Displaying References and Their Locations"
    strMessage = "Reference: " & refItem.Name & Chr(10) & Chr(13) _
    & "Location: " & refItem.FullPath & Chr(10) & Chr(13) _
    & "GUID: " & refItem.Guid & ", " & refItem.Major & ", " & refItem.Minor
    bytButtons = 64 'information symbol
    End If
    MsgBox prompt:=strMessage, Title:=strTitle, Buttons:=bytButtons
    Next refItem
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I agree with Mark that it sounds like a Reference problem.
    The manual way to check is to activate the VBA editor (Alt + F11) then Tools ...References

    Look for the word Missing in some of the ticked References.

    A Reference is just a link to a code library. Different versions of Office and Windows have different libraries and different versions of libraries available. If a reference is missing generally you untick it, then look for an alternative with a similar name and a different version number.
    Regards
    John



  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Hi Mark,
    Can you set a reference using VBA code?

    eg. I run Outlook 2007 but a client runs Outlook 2003.

    It would be good to be able to untick a MISSING reference and tick it's alternative.

    I made it with late binding (in that other forum) so no problems now
    Last edited by patt; 2011-09-27 at 05:27. Reason: Let people know it's no longer a problem

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No broken references but the W7 computer appeared to need a reference to Microsoft ActiveX Data Objects 2.1 Library.

    When I ascertianed there were no broken references, I created a new test db on the W7 computer and the functions worked in a sample form. I noticed that there was a reference to Microsoft ActiveX Data Objects 2.1 Library in that new test db. I added that to the db that was having trouble and all began to work properly.

    Not smart enough to know why but happy that it is functioning. If anyone has insight into this, I'd appreciate the education.

    Thanks for the help!

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by patt View Post
    Hi Mark,
    Can you set a reference using VBA code?

    eg. I run Outlook 2007 but a client runs Outlook 2003.

    It would be good to be able to untick a MISSING reference and tick it's alternative.

    I made it with late binding (in that other forum) so no problems now
    Patt,

    I don't think you can set the reference via VBA. I believe it is Read-only in code. But I'm not 100% sure of that.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You should be able to set a reference using either the AddFromFile method or the AddFromGUID method of the Application.References class.

    AddFromFile requires the fully qualified path of the library. AddFromGUID is more reliable as the GUID is the same across machines and usually across library versions.

    For example, the following adds the Microsoft Word object library to the list of references:
    Application.References.AddFromGuid "{00020905-0000-0000-C000-000000000046}",0,0

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    But isn't it usually the case that no code will actually run if any references are broken?
    So the code to fix the broken reference won't actually execute?
    Regards
    John



  9. #9
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think you're correct on this, John. I guess the code is mainly good if you want to add new references.

  10. #10
    New Lounger
    Join Date
    Oct 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What happens to reference differences when working with a make file (MDE, ADE) instead of the raw container (MDB, ADP)? I'm having a problem with a connection statement. It runs fine on Windows 7 boxes. It USED to run fine on Windows XP boxes, but suddenly stopped working, issuing a "Class does not support Automation or does not support expected interface message". I get this on a Windows Server 8 box as well. Could this be do an an Active Data X reference disagreement?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •