Automatic selection of DAO library 3.51 or 3.6

[Philfrench]

dear All,

I have an application developed with Excel 97 which has to run now also with Excel 2000. In some cases it is OK and there is nothing to do to change the library and sometime It has to be done manually by the tool/reference menu.
I wonder if it would be possible to link the right library depending on the version of Excel rather than doing it manually ?

Thanks for your answers

 

[Bowers74]

Hi Phil,

Read through the following thread:

thread707-647522

It has to do with pretty much the same thing that you are working on. It might help to put you on the right track.

I hope this helps!


Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[Philfrench]

Mike,

Thanks very much , it was of a great help.
I tried first a code like this :

On Error Resume Next
With ActiveWorkbook.VBProject.References
If Left(Application.Version, 1) = 8 Then
.Remove ActiveWorkbook.VBProject.References("DAO")
.AddFromGuid GUID:="{00025E01-0000-0000-C000-" & _
"000000000046}", major:=4, minor:=0 ' DAO 3.5
ElseIf Left(Application.Version, 1) = 9 Then
.Remove ActiveWorkbook.VBProject.References("DAO")
.AddFromGuid GUID:="{00025E01-0000-0000-C000-" & _
"000000000046}", major:=5, minor:=0 ' DAO 3.6
Else
MsgBox "This code was not written for Excel Version " _
& Application.Version
End If
End With
On Error GoTo 0

It works well but I had to face a side effect on Public variables which are reset when the macro stops even if they are initialized after the sequence ?? it happens only when the .Remove is executed. I have solved that by a more complex sequence (see below) to remove the DA0 library only if it has to be changed and in that case I save the file and close it. When it is reopened, the test is OK, therefore no remove, and it works well.


Dim Ref
Dim Ref_Desc As String
Dim Found As Boolean
Dim Message As String

Message = "XXXXX has updated a library link. To take " & _
"this modification into account " & _
"this file is going to be closed. & _
" & VBA.Chr(13) & "You will have to reopen it"
Found = False
With ActiveWorkbook.VBProject.references
If Left(Application.Version, 1) = 8 Then
For Each Ref In ActiveWorkbook.VBProject.references
Ref_Desc = Ref.Description
If Ref_Desc = "Microsoft DAO 3.51 Object Library" _
Then
Found = True
Exit For
End If
Next
If Found = False Then
.Remove ActiveWorkbook.VBProject.references("DAO")
.AddFromGuid GUID:="{00025E01-0000-0000-C000-" & _
"000000000046}", major:=4, minor:=0 ' DAO 3.5
ActiveWorkbook.Save
MsgBox Message, vbExclamation
Application.DisplayAlerts = False
ActiveWorkbook.Close
End If
ElseIf Left(Application.Version, 1) = 9 Then
For Each Ref In ActiveWorkbook.VBProject.references
Ref_Desc = Ref.Description
If Ref_Desc = "Microsoft DAO 3.6 Object Library" _
Then
Found = True
Exit For
End If
Next
If Found = False Then
.Remove ActiveWorkbook.VBProject.references("DAO")
.AddFromGuid GUID:="{00025E01-0000-0000-C000-" & _
"000000000046}", major:=5, minor:=0 ' DAO 3.6
ActiveWorkbook.Save
MsgBox Message, vbExclamation
Application.DisplayAlerts = False
ActiveWorkbook.Close
End If
Else
MsgBox "XXXXX is not compatible with Excel Version " & _
Application.Version
Application.DisplayAlerts = False
ActiveWorkbook.Close
End If
End With


Obviously, I would have prefered not being obliged to close the file and force the users to reopen it so if anyone has a solution I would appreciate.

Phil