Access DAO Reference/Right Function Error

[MeeMee]

Hi,

I have a problem in one of the MS Access systems at work. I am using some database functions that requires the DAO reference. The system is working fine on my PC, however it occurs on the clients PCs. I am using Access 2003.

Whenever the use clicks on a specific button that has a code which creates a specific Code through a number of steps the following error appears:

Function is not available in expressions in query expression 'Right(Cstr('00 00' & [Last_No]),4)'.

I am using this 'Right(Cstr('00 00' & [Last_No]),4)' to generate a 4 digit code to be appended later on to another part.

After doing some search I found out that this problem is identified by Microsoft here : http://support.microso ft.com/kb/194374 , the problem seems to be with the function "Right".

I did the manual solution to refresh the references, and it solved the problem, however from time to time it re appears and I have to do the steps again. It is hard to do it on a large number of clients :(

I also tried the code which do the manual refreshing automatically but it didn't work, I guess the code is for Access 97.

Please anyone help, I am really desperate, if there is another way to do the 4 digit code or some changes to make the refreshing code work I'd really appreciate it ...

Thanks in Advance

[ADezii]

Hi,

I have a problem in one of the MS Access systems at work. I am using some database functions that requires the DAO reference. The system is working fine on my PC, however it occurs on the clients PCs. I am using Access 2003.

Whenever the use clicks on a specific button that has a code which creates a specific Code through a number of steps the following error appears:



I am using this 'Right(Cstr('00 00' & [Last_No]),4)' to generate a 4 digit code to be appended later on to another part.

After doing some search I found out that this problem is identified by Microsoft here : http://support.microsoft.com/kb/194374 , the problem seems to be with the function "Right".

I did the manual solution to refresh the references, and it solved the problem, however from time to time it re appears and I have to do the steps again. It is hard to do it on a large number of clients :(

I also tried the code which do the manual refreshing automatically but it didn't work, I guess the code is for Access 97.

Please anyone help, I am really desperate, if there is another way to do the 4 digit code or some changes to make the refreshing code work I'd really appreciate it ...

Thanks in Advance

Try replacing Right() with Mid():

Mid$('0000' & CStr([Last_No]), Len('0000' & CStr([Last_No])) - 3)

P.S. - If this approach doesn't work either, try replacing the Expression with a Function Call that returns the proper Value.

[ADezii]

Try replacing Right() with Mid():

Mid$('0000' & CStr([Last_No]), Len('0000' & CStr([Last_No])) - 3)

P.S. - If this approach doesn't work either, try replacing the Expression with a Function Call that returns the proper Value.

Please do not think that I am totally insane responding to my own Post, but I just wanted to expand my previous point relating to the Function Call. Assuming [Last_No] is a LONG and REQUIRED:

SELECT tblTest.Last_No, fFormatLastNo([Last_No]) AS LastNumber
FROM tblTest;

Public Function fFormatLastNo(lngLastNum As Long) As String
  fFormatLastNo = Right$("0000" & CStr(lngLastNum), 4)
End Function

[MeeMee]

Hi ADezii

Thanks alot for your reply , I tried your Mid code on my PC and it worked , however to test it for the clients , I'll have to wait for the problem to reappear to test if it solves the error without the method given by Microsoft :(

and I'll try the Function call method too

I'll feed back once I get the error again :)

Thanks alot for your replies ..

P.S Don't worry I won't think you are Insane

[MeeMee]

If it's possible I have another question :)

I am using a code on form load to check weather the DAO reference is added on the client machine or not. If it is not added, it will automaticalyl do that.

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize

Dim Ref As Reference
Const constrDAO = "DAO"
Dim blReferenceFound As Boolean

blReferenceFound = False

'Find the DAO reference
For Each Ref In Application.References
    If Ref.Name = constrDAO Then
    blReferenceFound = True
Exit For
End If
Next Ref

If blReferenceFound = False Then
Application.References.AddFromFile ("C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.dll")
End If

End Sub

This is working fine, however sometimes the version of the DAO reference differs form one PC to another. Is there is a way to check for the vesrion of the DAO and accordingly add it to the system using VBA ??

Thanks again :)

[FishVal]

Hello, MeeMee.

The only reliable way to find location, version (and all other necessary info) of Type Library file is to use registry.
As far as I know, information about installed typelibs is stored in HKEY_CLASSES_RO OT\Typelib folder.

Regards,
Fish.

[ADezii]

If it's possible I have another question :)

I am using a code on form load to check weather the DAO reference is added on the client machine or not. If it is not added, it will automaticalyl do that.

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize

Dim Ref As Reference
Const constrDAO = "DAO"
Dim blReferenceFound As Boolean

blReferenceFound = False

'Find the DAO reference
For Each Ref In Application.References
    If Ref.Name = constrDAO Then
    blReferenceFound = True
Exit For
End If
Next Ref

If blReferenceFound = False Then
Application.References.AddFromFile ("C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.dll")
End If

End Sub

This is working fine, however sometimes the version of the DAO reference differs form one PC to another. Is there is a way to check for the vesrion of the DAO and accordingly add it to the system using VBA ??

Thanks again :)

You should be able to extract the Version Number from the Major and Minor Properties of the Reference Object, or indirectly from the FullPath Property. I'll demo the easier approach:

Dim Ref As Reference
Dim blReferenceFound As Boolean
Dim strDAOVersion As String
  
blReferenceFound = False
  
'Find the DAO reference
For Each Ref In Application.References
  If Ref.Name = "DAO" Then
    strDAOVersion = Ref.Major & "." & Ref.Minor
      blReferenceFound = True
         Exit For
  End If
Next Ref

[ChipR]

I've had this problem before, and believe it or not, it was an error about Left() caused by a missing reference to MS Outlook. If you have to support varying environments for your application, I really suggest using late binding to eliminate as many reference dependencies as possible.

[NeoPa]

@Chip - Bear in mind that late-binding seriously effects the help and usability of the IDE experience. Personally, I would recommend that only to people I knew were pretty experienced and self-supporting. That's only my opinion of course. Because I express it doesn't mean I think you should necessarily agree with me.

I also believe that MS have made a bit of a pig's ear of this whole reference system. Not very straightforward at all. Problems occur fairly frequently among members.

[ChipR]

Very true, NeoPa. I only mention late binding because I haven't learned any reliable alternatives for a complicated application to run on machines that aren't identical. It is a real pain.

[ADezii]

Here is another alternative for extracting the Version Number if anyone is actually interested. I've never used this logic before, but it works on my PCs:

Dim blReferenceFound As Boolean
Dim strDAOVersion As String
  
blReferenceFound = False
  
'Find the DAO reference
For Each Ref In Application.References
  If Ref.Name = "DAO" Then
    strDAOVersion = fExtractDAOVersion()
    MsgBox strDAOVersion     'testing, testing
      blReferenceFound = True
         Exit For
  End If
Next Ref

Private Function fExtractDAOVersion() As String
Dim strDAOFilePath As String
Dim intCharCount As Integer
Dim strBuild As String
Dim intNumeric As Integer

intNumeric = 0      'Initialize

'1st grab the DAO Type Library File Name (e.g. dao360.dll)
strDAOFilePath = Mid$(Application.References("DAO").FullPath, _
                 InStrRev(Application.References("DAO").FullPath, "\") + 1)
                 
'Loop thru evry Character in the File Name. To the nest of my knowledge, DAO
'embeds the Version Number within the Type Library. The 1st Numeric Value
'should be the Major Version Number, append a Period (.) after the 1st, then
'append the next consecutive Number(s)
For intCounter = 1 To Len(strDAOFilePath)
  If IsNumeric(Mid$(strDAOFilePath, intCounter, 1)) Then
    intNumeric = intNumeric + 1
      If intNumeric = 2 Then    'break between Major/Minor
        strBuild = strBuild & "." & Mid$(strDAOFilePath, intCounter, 1)
      Else
        strBuild = strBuild & Mid$(strDAOFilePath, intCounter, 1)
      End If
  End If
Next

fExtractDAOVersion = strBuild
End Function