Function not Available - 3075 (1 Viewer)

antomack · Mar 30, 2004

I have an application that has been working for over a year with no issues and in the last month or 2 it has been causing 3075 errors on some PC's related to the use of the Date() function in sql statements. The application is in 97 and 2000 and the error has occured in both versions.

The error message goes
Function is not available in query expression Detail of SQL String 3075

I know that in general this is related to missing references but I have checked the PC's involved and this is not the case.

The application is in mde format when it causes the error, running an mdb version on the PC's do not cause the error so was wondering if it could be to do with different versions of the referenced files. If so is there any easy way to check this and discover the reference that is causing the problem.

The items that are referenced in the 97 version are
VBA - vba332.dll
Access Objects Library - msacc8.olb
DAO 3.51 - dao350.dll
Lotus Domino Objects - domobj.tlb

An example of the actual SQL string is as below

Code:

strSQL = "SELECT DISTINCT HolYrs.Year, HolYrs.Hol_Year, HolYrs.Yr_Start, HolYrs.Yr_End"
strSQL = strSQL + " FROM HolYrs WHERE (((Date()) Between [yr_start] And [yr_end]));"
Set rstHlYears = db.OpenRecordset(strSQL)

Changing the code to

Code:

strSQL = "SELECT DISTINCT HolYrs.Year, HolYrs.Hol_Year, HolYrs.Yr_Start, HolYrs.Yr_End"
strSQL = strSQL + " FROM HolYrs WHERE (((#" & Format(Date(),"mm/dd/yyyy") & "#) Between [yr_start] And [yr_end]));"
Set rstHlYears = db.OpenRecordset(strSQL)

will allow the code to run without errors but this means changing a fair number of instances of the use of the Date() function.

Any help or insght would be appreciated.

fpendino · Mar 30, 2004

Is the date() that you are referring to a field?? If so try getting rid of the () after date and enclose it in brackets [Date].

Pat Hartman · Mar 30, 2004

Installing new software on a PC can "break" the references in existing databases.

This code will display each reference in a message box.

Code:

    Sub ReferenceInfo()
      Dim strMessage As String
      Dim strTitle As String
      Dim bytButtons As Byte
      Dim refItem As Reference

      On Error GoTo Err_ReferenceInfo

      For Each refItem In References
         If IsError(refItem.Name) Then
            strTitle = "MISSING Reference"
            strMessage = "Missing Reference:" & vbCrLf & refItem.FullPath
            bytButtons = 16 'critical symbol
         Else
            strTitle = "Displaying References and Their Locations"
            strMessage = "Reference: " & refItem.Name & vbCrLf & _
            "Location: " & refItem.FullPath
            bytButtons = 64 'information symbol
        End If

      MsgBox prompt:=strMessage, Title:=strTitle, buttons:=bytButtons

      Next refItem
Exit_ReferenceInfo:
 
    Exit Sub

Err_ReferenceInfo:
    Select Case Err.Number
        Case 3043
            MsgBox "Please select a valid database", vbOKOnly
        Case 91   ' db was not opened so it cannot be closed.
            Exit Sub
        Case Else
            MsgBox Err.Number & "-" & Err.Description
    End Select
    Resume Exit_ReferenceInfo
    End Sub

antomack · Mar 31, 2004

Thanks for the code Pat but I'm already using something similar at start-up of the application to check that all the references are OK and it's getting by this code fine and causes the error later after the user logs in. The code I have in use is below which checks for broken references and adds details of these to a log file against the user name and machine id so the helpdesk can look it up to find the culprit.

Code:

Function CheckAllRefs()
On Error GoTo Err_CheckAllRefs

  Dim i As Integer
  
  CheckAllRefs = True
  
  For i = 1 To Access.References.Count
    If Access.References(i).IsBroken = True Then
      ' write to error log with detail of missing reference
      WriteRefErrLog ("Missing Reference: " & i & " - " & strUsrNm)
      CheckAllRefs = False
    End If
  Next i

Exit_CheckAllRefs:
  Exit Function
  
Err_CheckAllRefs:
  Select Case Err
    Case 48
      Resume Next
    Case Else
      MsgBox Err.Description & Err
      Resume Exit_CheckAllRefs
  End Select
  
End Function

The issue is only on about 10 PC's out of over 500 and in at least 5 of these new software has been installed in the week or so prior to the problem arising which I realise can 'break' the references, yet all the references appear to be fine according to my function above.

Is there anything else that I can check that may be causing the problem?

theprez · Apr 28, 2004

I am having the same problem but in a form where I am using the Date() for the default value of a field. Did you ever resolve this problem?

Tim K. · Apr 28, 2004

I always use late binding style coding to avoid the References problem.

Dim dbs As Object, rst As Object
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(...)

Function not Available - 3075 (1 Viewer)

antomack

Registered User.

fpendino

Registered User.

Pat Hartman

Super Moderator

antomack

Registered User.

theprez

Registered User.

Tim K.

Registered User.

Similar threads

Users who are viewing this thread