Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 989
  • Last Modified:

VBA error 3057 & 3075 - Refresh References question

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.

1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
WoodrowAAuthor Commented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...

WoodrowAAuthor Commented:
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
WoodrowAAuthor Commented:
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

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now