VBA error 3057 & 3075 - Refresh References question

Posted on 2009-12-21
Last Modified: 2013-12-20
I am using Access 2007.
I am finding that when I send my appliadtion to another machine it may or may not work on that machine.  After sending the applciation to another machine I get an error message of 3057 or 3075, when running a certain query that involves the Format Function on a date field.  The message is "Function isn't available in expressions in Query Expression".  

There is a simple solution.  I go into the VBA and add a reference.  Any reference will do.  Just this simple act of adding the reference somehow refreshes the thinking on the client machine.  I don't even have to recompile or Save.  Just by doing the above, if I then re-run the query, everything is fine.

So what's the problem?  The problem is that I am adding this reference manually to the client machine, but there may be times in the future where the client will be running a Runtime version of Access and so adding a manual reference to VBA will not be an option.

I'm wondering if there is a code equivilent?  Is there some innocous reference I could add using code.  It does not seem to matter what you add.  Just by adding any reference in VBA (tools menu, references) causes Access to get its thinking right again.

Question by:WoodrowA
    LVL 77

    Expert Comment

    LVL 84
    You should never have to refresh your references, assuming (a) your references are properly installed on the client machine and (b) Windows and Office are fully up to date and (c) your database does not have any issues.

    I realize that when you refresh the refs on the client machine that this seems to resolve the issue, but in a properly built and properly deployed database, you should never need to do this.

    What references does your application require?

    Author Comment

    Thank you for responding.
    Well, according to Microsoft, whether it should happen or not, it does happen and when it does the solution is a refresh.  I could spend an inordinate amount of time determining why it happens, but since others smarter than I haven't been able to do so I am willing to accept the refresh solution, if I can actually get it to work.
    I am testing suggestions now and will respond when I am something of substance to report.
    Best Regards
    LVL 84
    Could you please cite your reference where MS says it's necessary to refresh references when moving a database to a different machine? I'd be interested in this ...

    My point is this: If you can STOP it from happening, then you won't need to worry with refreshing your links. I've deployed thousands of applications (many of the same one over and over and over again) to thousands of desktops, and I have never once had to refresh references, either in code or manually.

    Can you list your references? You may simply have one that is giving you trouble during installation, so it may be something as simple as that ...


    Author Comment

    Thank you for considering what I had to say.  The main reference is the first one listed below, but there are others:
    I have been toying with the problem and have come up with a solution (with regard to the refresh) that works for me.  I will check my coding and upload it later today.
    I would like to believe this is a fluke and that in most circumstances a refresh isn't or won't be necessary.  I may have done some things in this particular app to provoke the problem.  For one thing I did a lot of early binding, which I will do less of in the future.  And there are 11 early binding references.
    All I can tell you is this.  I have an appliacation I have written for a freight forwarder, written in Access 2007.  The app is split with Access as both the FE and BE.  From time to time I connect with client via LogMeIn and send him a new FE.  This is done to two of his machines on a Local Area Network.  Both are running Vista, one running Vista 64.
    Once I send the app, it will fail when a query is run that involves functions. However, if I simply go into VBA on their machines and do a Tools, Rreferences, and add a Reference, (any reference) the problem is sovled.  Don't know why.  I don't even have to save or compile, just add the reference.
    In the code I will send later I simply determine what the 11th or last reference is in the machines VBA, remove it and then add it back.
    I too would like to get at the root of the problem, but like everyone I am under time constraints.  You can spend a lot of time tracking things like this down. My concern is "what happens if the same problem manifests itself using Access Runtime." With Runtime there is no manual option to fix the problem, so, the need for the code.
    Woody - Sacramento

    Author Comment

    Below is the code that worked for me.  I put the first part of the code in the load event of the  form that opens when the database is run.

    If modUtility.IsQueryProblem = True Then
            If modUtility.IsQueryProblemFixed = True Then
                'continue on, do nothing
                MsgBox "Problem in frm000 Load Event"
                Exit Sub
            End If
    End If
    Function IsQueryProblem() As Boolean
       Dim db As Database, rs As Recordset
       Set db = CurrentDb
       On Error Resume Next
       ' Run query qry913. If the references are not uptodate this will produce an error.
       'Set rs = db.OpenRecordset("qry913_FormatFunctionTesting", dbOpenDynaset)
        Set rs = db.OpenRecordset("qry913_FormatFunctionTesting")
       'I know the query below will produce an error.
       'Set rs = db.OpenRecordset("qry914", dbOpenDynaset)
        If Err.Number <> 0 Then
          'MsgBox "Error Number " & Err.Number & ". " & Err.Description
          Set rs = Nothing
          IsQueryProblem = True
            'MsgBox "Query ran with no detected errors."
            Set rs = Nothing
            IsQueryProblem = False
       End If
    End Function
    Function IsQueryProblemFixed() As Boolean
        Dim loRef As Access.Reference
        Dim intCount As Integer
        Dim strPath As String
        On Error Resume Next
        'Count the number of references already referenced
        intCount = Access.References.Count
          'Get the value of the last reference, remove it, then add it back.
          Set loRef = Access.References(intCount)
          With loRef
              strPath = .FullPath
              With Access.References
                .Remove loRef
                .AddFromFile strPath
              End With
              IsQueryProblemFixed = True
              Exit Function
              'At one time the lines below appeared to be necessary but
              'for this particular problem they are not.
              ' Call a hidden SysCmd to automatically compile/save all modules.
              'Call SysCmd(504, 16483)
          End With
        'This line should not be reached unless there is an error.
        IsQueryProblemFixed = False
        Exit Function
    End Function

    Open in new window


    Accepted Solution

    Question PAQ'd, 500 points refunded, and stored in the solution database.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now