Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Trying to come up with a foolproof way to add the current ADO library to the
project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Why not just using late binding?
Dim oConn As Object Dim oRS As Object Set oConn = CreateObject("ADODB.Connection") Set oRS = CreateObject("ADODB.Recordset") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
What Bob recommends is foolproof but note that there is a slight performance
hit when you do it this way. That is the tradeoff you make for doing it this way. The other issue is that you loose intellisence when you are writing the code. My preference when doing it this way is to reference the ADO object and write all of my dim statements referencing the actual ADO objects. Once the code is working then remove the reference and change the dim statements to use the late binding Bob listed. Maybe it is just me but I find it difficult to program without intelliesence... -- HTH... Jim Thomlinson "Bob Phillips" wrote: Why not just using late binding? Dim oConn As Object Dim oRS As Object Set oConn = CreateObject("ADODB.Connection") Set oRS = CreateObject("ADODB.Recordset") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
I don't want to use late binding for 3 reasons:
No intelli-sense Some performance penalty? As this is a very large .xla a lot of code re-writing. RBS "Bob Phillips" wrote in message ... Why not just using late binding? Dim oConn As Object Dim oRS As Object Set oConn = CreateObject("ADODB.Connection") Set oRS = CreateObject("ADODB.Recordset") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Pick the lowest version of ADO that you have on your system and save it using
that reference. Your code should still run and most people will have version 2.5 or better. Otherwise you are stuck with the re-write... The intellisence is pretty easy to work around so the only real issue is the performance hit which is probably not substantial enough to be a show stopper... -- HTH... Jim Thomlinson "RB Smissaert" wrote: I don't want to use late binding for 3 reasons: No intelli-sense Some performance penalty? As this is a very large .xla a lot of code re-writing. RBS "Bob Phillips" wrote in message ... Why not just using late binding? Dim oConn As Object Dim oRS As Object Set oConn = CreateObject("ADODB.Connection") Set oRS = CreateObject("ADODB.Recordset") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
One drawback of that is that if I save with say 2.1 and the user has 2.1 and
2.8 on the system it will stick with 2.1. Not sure if there is much harm in that, but maybe 2.8 has better performance. What is wrong with the posted code? RBS "Jim Thomlinson" wrote in message ... Pick the lowest version of ADO that you have on your system and save it using that reference. Your code should still run and most people will have version 2.5 or better. Otherwise you are stuck with the re-write... The intellisence is pretty easy to work around so the only real issue is the performance hit which is probably not substantial enough to be a show stopper... -- HTH... Jim Thomlinson "RB Smissaert" wrote: I don't want to use late binding for 3 reasons: No intelli-sense Some performance penalty? As this is a very large .xla a lot of code re-writing. RBS "Bob Phillips" wrote in message ... Why not just using late binding? Dim oConn As Object Dim oRS As Object Set oConn = CreateObject("ADODB.Connection") Set oRS = CreateObject("ADODB.Recordset") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Hi Bart,
Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Hi Peter,
Thanks for the reply, but I don't get it quite, other than that you say avoid 2.8. You are talking about the reference object, but I haven't used that in that code. Correct me if I misunderstood. Doing this from work, so maybe I didn't look properly. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Peter,
Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Hi Bart,
Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Hi Peter,
My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Hi Bart,
I wouldn't want to suggest you leave out 2.8, not sure what the problem is with it in my system and probably unusual. No idea if it's relates to the reason your user fails. I modified your function and it succeeded it correctly adding v2.7 after first adding the problematic 2.8. Function AddReferenceFromFile( blah Dim oRef As Object ' Reference ' code On Error Resume Next Set oRef = VBProj.References.AddFromFile(strFilePath) ' AddReferenceFromFile = True AddReferenceFromFile = Len(oRef.fullPath) 0 If Err.Number Then If Not oRef Is Nothing Then VBProj.References.Remove oRef ' this removed my 2.8 End If Else AddReferenceFromFile = True End If Regards, Peter T wrote in message oups.com... Hi Peter, My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Hi Peter,
OK, I got you. You are trying to test for a reference that was set, but that somehow is not right. I think that makes sense, and maybe the way you do it is a good way. I always have the option though if things don't work out to set the path to the right file via my .ini. Still, it is nice if such a thing could be avoided as it would involve contacting me etc. What are you using ADO for? RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, I wouldn't want to suggest you leave out 2.8, not sure what the problem is with it in my system and probably unusual. No idea if it's relates to the reason your user fails. I modified your function and it succeeded it correctly adding v2.7 after first adding the problematic 2.8. Function AddReferenceFromFile( blah Dim oRef As Object ' Reference ' code On Error Resume Next Set oRef = VBProj.References.AddFromFile(strFilePath) ' AddReferenceFromFile = True AddReferenceFromFile = Len(oRef.fullPath) 0 If Err.Number Then If Not oRef Is Nothing Then VBProj.References.Remove oRef ' this removed my 2.8 End If Else AddReferenceFromFile = True End If Regards, Peter T wrote in message oups.com... Hi Peter, My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Peter,
Can you add that ADO reference via the GUID and will it be faulty despite setting the reference successfully? I am talking about the problematic 2.8 here. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, I wouldn't want to suggest you leave out 2.8, not sure what the problem is with it in my system and probably unusual. No idea if it's relates to the reason your user fails. I modified your function and it succeeded it correctly adding v2.7 after first adding the problematic 2.8. Function AddReferenceFromFile( blah Dim oRef As Object ' Reference ' code On Error Resume Next Set oRef = VBProj.References.AddFromFile(strFilePath) ' AddReferenceFromFile = True AddReferenceFromFile = Len(oRef.fullPath) 0 If Err.Number Then If Not oRef Is Nothing Then VBProj.References.Remove oRef ' this removed my 2.8 End If Else AddReferenceFromFile = True End If Regards, Peter T wrote in message oups.com... Hi Peter, My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Hi Bart,
Although I already knew my 2.8 is problematic it's only today I've found out trying to return it's path throws the error. Not sure why I can return other info' about it, such as Name, Description, Major, Minor etc. So in your routine need to remove the 'bad' ref before trying lower versions. Which reminds me, if I (anyone) runs your routine twice, the second time user will get the error message because trying to add the ref when one already exists throws the error. I suppose that shouldn't occur because you set a flag in your ini, but...? What are you using ADO for? Not much ! Regards, Peter T "RB Smissaert" wrote in message ... Hi Peter, OK, I got you. You are trying to test for a reference that was set, but that somehow is not right. I think that makes sense, and maybe the way you do it is a good way. I always have the option though if things don't work out to set the path to the right file via my .ini. Still, it is nice if such a thing could be avoided as it would involve contacting me etc. What are you using ADO for? RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, I wouldn't want to suggest you leave out 2.8, not sure what the problem is with it in my system and probably unusual. No idea if it's relates to the reason your user fails. I modified your function and it succeeded it correctly adding v2.7 after first adding the problematic 2.8. Function AddReferenceFromFile( blah Dim oRef As Object ' Reference ' code On Error Resume Next Set oRef = VBProj.References.AddFromFile(strFilePath) ' AddReferenceFromFile = True AddReferenceFromFile = Len(oRef.fullPath) 0 If Err.Number Then If Not oRef Is Nothing Then VBProj.References.Remove oRef ' this removed my 2.8 End If Else AddReferenceFromFile = True End If Regards, Peter T wrote in message oups.com... Hi Peter, My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
I have added your find (ref successfully added, but faulty as .FullPath
doesn't work) now to my function. Looks this is a bit more foolproof then, but I am interested if anybody can still see holes in this. Function AddProjectReference(Optional strGUID As String, _ Optional lMajor As Long, _ Optional lMinor As Long, _ Optional strRefName As String = "", _ Optional bRemove As Boolean, _ Optional bRemoveAndAdd As Boolean, _ Optional bArray As Boolean, _ Optional vGUIDArray As Variant, _ Optional strObjectString As String, _ Optional strWorkbook As String, _ Optional strFilePath As String, _ Optional bMessage As Boolean = True) As Boolean Dim oRef As Object Dim VBProj As VBProject Dim i As Byte Dim bSuccess As Boolean If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject 'removing references '------------------- For Each oRef In VBProj.References If oRef.Name = strRefName Then If oRef.IsBroken Then 'so remove any broken references '------------------------------- VBProj.References.REMOVE oRef Else If bRemove Or bRemoveAndAdd Then VBProj.References.REMOVE oRef If bRemove Then AddProjectReference = True End If End If End If End If Next If bRemove Then Exit Function End If 'adding references '----------------- If Len(strFilePath) = 0 Then 'not adding directly from file '----------------------------- If Len(strObjectString) = 0 Then If bArray Then 'trying an array of GUID's and version numbers On Error Resume Next For i = 1 To UBound(vGUIDArray) Set oRef = VBProj.References.AddFromGuid(GUID:=vGUIDArray(i, 1), _ Major:=vGUIDArray(i, 2), _ Minor:=vGUIDArray(i, 3)) If Err.Number = 0 Then bSuccess = Len(oRef.FullPath) 0 If bSuccess Then AddProjectReference = True WriteIniValue strINIPath, _ "Add-in behaviour", _ "ADO reference added", _ vGUIDArray(i, 2) & "." & vGUIDArray(i, 3) Exit Function Else VBProj.References.REMOVE oRef End If End If Next GoTo ERROROUT 'as we couldn't add any of the references Else On Error Resume Next Set oRef = VBProj.References.AddFromGuid(GUID:=strGUID, _ Major:=lMajor, _ Minor:=lMinor) If Err.Number = 0 Then bSuccess = Len(oRef.FullPath) 0 'just for in case the len is 0 without error If bSuccess Then AddProjectReference = True 'as we got here without an error Else VBProj.References.REMOVE oRef GoTo ERROROUT End If End If End If Else 'adding from file via registry reads '----------------------------------- On Error GoTo ERROROUT AddProjectReference = AddRefFromFileWithRegReads(strObjectString, strWorkbook) End If Else 'adding directly from file '------------------------- On Error Resume Next If bFileExistsVBA(strFilePath) Then Set oRef = VBProj.References.AddFromFile(strFilePath) If Err.Number = 0 Then bSuccess = Len(oRef.FullPath) 0 If bSuccess Then AddProjectReference = True Else VBProj.References.REMOVE oRef GoTo ERROROUT End If End If Else If bMessage Then MsgBox "Couldn't add the " & strRefName & " reference as the file:" & _ vbCrLf & _ strFilePath & vbCrLf & _ "is missing." & vbCrLf & vbCrLf & _ "Run the installer on this PC", vbExclamation, _ "adding " & strRefName & " reference" End If End If End If If bRemove = False Then WriteIniValue strINIPath, _ "Add-in behaviour", _ "Added " & strRefName & " library file path", _ oRef.FullPath End If Exit Function ERROROUT: If bMessage Then MsgBox "Couldn't add the " & strRefName & " reference", , _ "adding references to VB Project" End If On Error GoTo 0 End Function It can't work without the other functions, but you will get the idea. Thanks for that suggestion, it may be useful. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, Although I already knew my 2.8 is problematic it's only today I've found out trying to return it's path throws the error. Not sure why I can return other info' about it, such as Name, Description, Major, Minor etc. So in your routine need to remove the 'bad' ref before trying lower versions. Which reminds me, if I (anyone) runs your routine twice, the second time user will get the error message because trying to add the ref when one already exists throws the error. I suppose that shouldn't occur because you set a flag in your ini, but...? What are you using ADO for? Not much ! Regards, Peter T "RB Smissaert" wrote in message ... Hi Peter, OK, I got you. You are trying to test for a reference that was set, but that somehow is not right. I think that makes sense, and maybe the way you do it is a good way. I always have the option though if things don't work out to set the path to the right file via my .ini. Still, it is nice if such a thing could be avoided as it would involve contacting me etc. What are you using ADO for? RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, I wouldn't want to suggest you leave out 2.8, not sure what the problem is with it in my system and probably unusual. No idea if it's relates to the reason your user fails. I modified your function and it succeeded it correctly adding v2.7 after first adding the problematic 2.8. Function AddReferenceFromFile( blah Dim oRef As Object ' Reference ' code On Error Resume Next Set oRef = VBProj.References.AddFromFile(strFilePath) ' AddReferenceFromFile = True AddReferenceFromFile = Len(oRef.fullPath) 0 If Err.Number Then If Not oRef Is Nothing Then VBProj.References.Remove oRef ' this removed my 2.8 End If Else AddReferenceFromFile = True End If Regards, Peter T wrote in message oups.com... Hi Peter, My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
No, I can't set 2.8 via the GUID, I get error "Object library not
registered" I used your original routine to add all versions, returned GUID each time before removing the ref I got these - 2.0, 2.1, 2.5, 2.6 are all same {00000200-0000-0010-8000-00AA006D2EA4} 2.7 {EF53050B-882E-4776-B643-EDA472E8E3F2} 2.8 {2A75196C-D9EB-4129-B803-931327F72D5C} s = the above GUID string ..references.AddFromGuid(s, 2, 8) fails with the 2.8, all others work It seems strange I can add AddFromFile the 'bad' ref and return its GUID, yet can't AddFromGuid. But as I've said, there's something wrong with this one! Regards, Peter T "RB Smissaert" wrote in message ... Peter, Can you add that ADO reference via the GUID and will it be faulty despite setting the reference successfully? I am talking about the problematic 2.8 here. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, I wouldn't want to suggest you leave out 2.8, not sure what the problem is with it in my system and probably unusual. No idea if it's relates to the reason your user fails. I modified your function and it succeeded it correctly adding v2.7 after first adding the problematic 2.8. Function AddReferenceFromFile( blah Dim oRef As Object ' Reference ' code On Error Resume Next Set oRef = VBProj.References.AddFromFile(strFilePath) ' AddReferenceFromFile = True AddReferenceFromFile = Len(oRef.fullPath) 0 If Err.Number Then If Not oRef Is Nothing Then VBProj.References.Remove oRef ' this removed my 2.8 End If Else AddReferenceFromFile = True End If Regards, Peter T wrote in message oups.com... Hi Peter, My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
I've just found out I have the same problem with 2.6 (but 2.7 seems OK).
My understanding in this scenario - "Object library not registered" - means not registered to Excel (not like say a dll or ocx not registered). So I tried - Start Run "full path to excel.exe" /regserver No difference ! Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... No, I can't set 2.8 via the GUID, I get error "Object library not registered" I used your original routine to add all versions, returned GUID each time before removing the ref I got these - 2.0, 2.1, 2.5, 2.6 are all same {00000200-0000-0010-8000-00AA006D2EA4} 2.7 {EF53050B-882E-4776-B643-EDA472E8E3F2} 2.8 {2A75196C-D9EB-4129-B803-931327F72D5C} s = the above GUID string .references.AddFromGuid(s, 2, 8) fails with the 2.8, all others work It seems strange I can add AddFromFile the 'bad' ref and return its GUID, yet can't AddFromGuid. But as I've said, there's something wrong with this one! Regards, Peter T "RB Smissaert" wrote in message ... Peter, Can you add that ADO reference via the GUID and will it be faulty despite setting the reference successfully? I am talking about the problematic 2.8 here. RBS |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Bloody confusing this ADO!
Have you tried the MDAC version checker? http://www.microsoft.com/downloads/d...displaylang=en RBS "Peter T" <peter_t@discussions wrote in message ... No, I can't set 2.8 via the GUID, I get error "Object library not registered" I used your original routine to add all versions, returned GUID each time before removing the ref I got these - 2.0, 2.1, 2.5, 2.6 are all same {00000200-0000-0010-8000-00AA006D2EA4} 2.7 {EF53050B-882E-4776-B643-EDA472E8E3F2} 2.8 {2A75196C-D9EB-4129-B803-931327F72D5C} s = the above GUID string .references.AddFromGuid(s, 2, 8) fails with the 2.8, all others work It seems strange I can add AddFromFile the 'bad' ref and return its GUID, yet can't AddFromGuid. But as I've said, there's something wrong with this one! Regards, Peter T "RB Smissaert" wrote in message ... Peter, Can you add that ADO reference via the GUID and will it be faulty despite setting the reference successfully? I am talking about the problematic 2.8 here. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, I wouldn't want to suggest you leave out 2.8, not sure what the problem is with it in my system and probably unusual. No idea if it's relates to the reason your user fails. I modified your function and it succeeded it correctly adding v2.7 after first adding the problematic 2.8. Function AddReferenceFromFile( blah Dim oRef As Object ' Reference ' code On Error Resume Next Set oRef = VBProj.References.AddFromFile(strFilePath) ' AddReferenceFromFile = True AddReferenceFromFile = Len(oRef.fullPath) 0 If Err.Number Then If Not oRef Is Nothing Then VBProj.References.Remove oRef ' this removed my 2.8 End If Else AddReferenceFromFile = True End If Regards, Peter T wrote in message oups.com... Hi Peter, My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Looks interesting, I try and get it -
MS need to verify I've got genuine Windows, continue - Seems I need to use the "alternative" Genuine Windows Validation method - Run the validation tool - Continue - Download and run from location - msgbox - this version of Windows Advantage is no longer supported, please download the latest version Give up !! Regards, Peter T "RB Smissaert" wrote in message ... Bloody confusing this ADO! Have you tried the MDAC version checker? http://www.microsoft.com/downloads/d...DF6-4A21-4B43- BF53-14332EF092C9&displaylang=en RBS "Peter T" <peter_t@discussions wrote in message ... No, I can't set 2.8 via the GUID, I get error "Object library not registered" I used your original routine to add all versions, returned GUID each time before removing the ref I got these - 2.0, 2.1, 2.5, 2.6 are all same {00000200-0000-0010-8000-00AA006D2EA4} 2.7 {EF53050B-882E-4776-B643-EDA472E8E3F2} 2.8 {2A75196C-D9EB-4129-B803-931327F72D5C} s = the above GUID string .references.AddFromGuid(s, 2, 8) fails with the 2.8, all others work It seems strange I can add AddFromFile the 'bad' ref and return its GUID, yet can't AddFromGuid. But as I've said, there's something wrong with this one! Regards, Peter T "RB Smissaert" wrote in message ... Peter, Can you add that ADO reference via the GUID and will it be faulty despite setting the reference successfully? I am talking about the problematic 2.8 here. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, I wouldn't want to suggest you leave out 2.8, not sure what the problem is with it in my system and probably unusual. No idea if it's relates to the reason your user fails. I modified your function and it succeeded it correctly adding v2.7 after first adding the problematic 2.8. Function AddReferenceFromFile( blah Dim oRef As Object ' Reference ' code On Error Resume Next Set oRef = VBProj.References.AddFromFile(strFilePath) ' AddReferenceFromFile = True AddReferenceFromFile = Len(oRef.fullPath) 0 If Err.Number Then If Not oRef Is Nothing Then VBProj.References.Remove oRef ' this removed my 2.8 End If Else AddReferenceFromFile = True End If Regards, Peter T wrote in message oups.com... Hi Peter, My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
I can mail you the setup file.
RBS "Peter T" <peter_t@discussions wrote in message ... Looks interesting, I try and get it - MS need to verify I've got genuine Windows, continue - Seems I need to use the "alternative" Genuine Windows Validation method - Run the validation tool - Continue - Download and run from location - msgbox - this version of Windows Advantage is no longer supported, please download the latest version Give up !! Regards, Peter T "RB Smissaert" wrote in message ... Bloody confusing this ADO! Have you tried the MDAC version checker? http://www.microsoft.com/downloads/d...DF6-4A21-4B43- BF53-14332EF092C9&displaylang=en RBS "Peter T" <peter_t@discussions wrote in message ... No, I can't set 2.8 via the GUID, I get error "Object library not registered" I used your original routine to add all versions, returned GUID each time before removing the ref I got these - 2.0, 2.1, 2.5, 2.6 are all same {00000200-0000-0010-8000-00AA006D2EA4} 2.7 {EF53050B-882E-4776-B643-EDA472E8E3F2} 2.8 {2A75196C-D9EB-4129-B803-931327F72D5C} s = the above GUID string .references.AddFromGuid(s, 2, 8) fails with the 2.8, all others work It seems strange I can add AddFromFile the 'bad' ref and return its GUID, yet can't AddFromGuid. But as I've said, there's something wrong with this one! Regards, Peter T "RB Smissaert" wrote in message ... Peter, Can you add that ADO reference via the GUID and will it be faulty despite setting the reference successfully? I am talking about the problematic 2.8 here. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, I wouldn't want to suggest you leave out 2.8, not sure what the problem is with it in my system and probably unusual. No idea if it's relates to the reason your user fails. I modified your function and it succeeded it correctly adding v2.7 after first adding the problematic 2.8. Function AddReferenceFromFile( blah Dim oRef As Object ' Reference ' code On Error Resume Next Set oRef = VBProj.References.AddFromFile(strFilePath) ' AddReferenceFromFile = True AddReferenceFromFile = Len(oRef.fullPath) 0 If Err.Number Then If Not oRef Is Nothing Then VBProj.References.Remove oRef ' this removed my 2.8 End If Else AddReferenceFromFile = True End If Regards, Peter T wrote in message oups.com... Hi Peter, My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
Thanks, but for some reason I persevered, downloaded the same validation
tool again and this time gave me a code Got the setup (I already have an older version, maybe that's what the message was about but what is "Advantage" - very confusing). I run it, reports many mismatch registry fields, not only in 2.8 but all my versions, incl 2.5 which works fine. Think I'll pass on trying to manually edit so many registry entries. Regards, Peter T "RB Smissaert" wrote in message ... I can mail you the setup file. RBS "Peter T" <peter_t@discussions wrote in message ... Looks interesting, I try and get it - MS need to verify I've got genuine Windows, continue - Seems I need to use the "alternative" Genuine Windows Validation method - Run the validation tool - Continue - Download and run from location - msgbox - this version of Windows Advantage is no longer supported, please download the latest version Give up !! Regards, Peter T "RB Smissaert" wrote in message ... Bloody confusing this ADO! Have you tried the MDAC version checker? http://www.microsoft.com/downloads/d...DF6-4A21-4B43- BF53-14332EF092C9&displaylang=en RBS "Peter T" <peter_t@discussions wrote in message ... No, I can't set 2.8 via the GUID, I get error "Object library not registered" I used your original routine to add all versions, returned GUID each time before removing the ref I got these - 2.0, 2.1, 2.5, 2.6 are all same {00000200-0000-0010-8000-00AA006D2EA4} 2.7 {EF53050B-882E-4776-B643-EDA472E8E3F2} 2.8 {2A75196C-D9EB-4129-B803-931327F72D5C} s = the above GUID string .references.AddFromGuid(s, 2, 8) fails with the 2.8, all others work It seems strange I can add AddFromFile the 'bad' ref and return its GUID, yet can't AddFromGuid. But as I've said, there's something wrong with this one! Regards, Peter T "RB Smissaert" wrote in message ... Peter, Can you add that ADO reference via the GUID and will it be faulty despite setting the reference successfully? I am talking about the problematic 2.8 here. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, I wouldn't want to suggest you leave out 2.8, not sure what the problem is with it in my system and probably unusual. No idea if it's relates to the reason your user fails. I modified your function and it succeeded it correctly adding v2.7 after first adding the problematic 2.8. Function AddReferenceFromFile( blah Dim oRef As Object ' Reference ' code On Error Resume Next Set oRef = VBProj.References.AddFromFile(strFilePath) ' AddReferenceFromFile = True AddReferenceFromFile = Len(oRef.fullPath) 0 If Err.Number Then If Not oRef Is Nothing Then VBProj.References.Remove oRef ' this removed my 2.8 End If Else AddReferenceFromFile = True End If Regards, Peter T wrote in message oups.com... Hi Peter, My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Foolproof way to add ADO reference
If 2.5 works I suppose there is no real trouble, but maybe you could run a
reg cleaner. RBS "Peter T" <peter_t@discussions wrote in message ... Thanks, but for some reason I persevered, downloaded the same validation tool again and this time gave me a code Got the setup (I already have an older version, maybe that's what the message was about but what is "Advantage" - very confusing). I run it, reports many mismatch registry fields, not only in 2.8 but all my versions, incl 2.5 which works fine. Think I'll pass on trying to manually edit so many registry entries. Regards, Peter T "RB Smissaert" wrote in message ... I can mail you the setup file. RBS "Peter T" <peter_t@discussions wrote in message ... Looks interesting, I try and get it - MS need to verify I've got genuine Windows, continue - Seems I need to use the "alternative" Genuine Windows Validation method - Run the validation tool - Continue - Download and run from location - msgbox - this version of Windows Advantage is no longer supported, please download the latest version Give up !! Regards, Peter T "RB Smissaert" wrote in message ... Bloody confusing this ADO! Have you tried the MDAC version checker? http://www.microsoft.com/downloads/d...DF6-4A21-4B43- BF53-14332EF092C9&displaylang=en RBS "Peter T" <peter_t@discussions wrote in message ... No, I can't set 2.8 via the GUID, I get error "Object library not registered" I used your original routine to add all versions, returned GUID each time before removing the ref I got these - 2.0, 2.1, 2.5, 2.6 are all same {00000200-0000-0010-8000-00AA006D2EA4} 2.7 {EF53050B-882E-4776-B643-EDA472E8E3F2} 2.8 {2A75196C-D9EB-4129-B803-931327F72D5C} s = the above GUID string .references.AddFromGuid(s, 2, 8) fails with the 2.8, all others work It seems strange I can add AddFromFile the 'bad' ref and return its GUID, yet can't AddFromGuid. But as I've said, there's something wrong with this one! Regards, Peter T "RB Smissaert" wrote in message ... Peter, Can you add that ADO reference via the GUID and will it be faulty despite setting the reference successfully? I am talking about the problematic 2.8 here. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, I wouldn't want to suggest you leave out 2.8, not sure what the problem is with it in my system and probably unusual. No idea if it's relates to the reason your user fails. I modified your function and it succeeded it correctly adding v2.7 after first adding the problematic 2.8. Function AddReferenceFromFile( blah Dim oRef As Object ' Reference ' code On Error Resume Next Set oRef = VBProj.References.AddFromFile(strFilePath) ' AddReferenceFromFile = True AddReferenceFromFile = Len(oRef.fullPath) 0 If Err.Number Then If Not oRef Is Nothing Then VBProj.References.Remove oRef ' this removed my 2.8 End If Else AddReferenceFromFile = True End If Regards, Peter T wrote in message oups.com... Hi Peter, My customers ( 100 ) have been using this routine now for a week and sofar I haven't heard about any problems. Are you suggesting I should leave the 2.8 option out? RBS Peter T wrote: Hi Bart, Yes I had noticed (later) the function didn't return True on success. However that doesn't change what I reported earlier about v2.8 not working correctly in my system. Trying to debug its Fullpath (after setting the ref) errors. In my XL2000 looking at tools ref's v2.8 is checked and looks correct (but I know it will cause problems). I ran same code in XL97, v2.8 is also checked but marked MISSING. Regards, Peter wrote in message ups.com... Peter, Just noticed that the function misses this line at the end: AddReferenceFromFile = True Before Exit Function Try again with that added. RBS Peter T wrote: Hi Bart, Your add ado ref routine both worked and failed getting your error message. Somehow it managed to add the ref to v2.8 though as far as I know this version is not correctly registered on my system. It doesn't normally appear in tools ref's and things have gone wrong in the past using other people's wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which for some reason I find more reliable. As I say it added the 2.8 ref but when I did this ' r = a vba ref to v2.8 Debug.Print r.Name, r.Description, r.Major, r.Minor ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8 Debug.Print r.FullPath ' this fails !! debug.print err, err.description -2147319779 Method 'FullPath' of object 'Reference' failed If I change the ref to v2.7 both debug lines work ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7 C:\Program Files\Common Files\System\ado\msado27.tlb Maybe something similar to your user. I have two versions of msado15.dll on my system, one an old v1.5 and the newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the other versions in your list. FWIW I recall having problems when trying to upgrade to 2.8, it was a while ago. Regards, Peter T PS I commented out your code to get file name from an ini and did - strADOPathFromINI = ThisWorkbook.Name "RB Smissaert" wrote in message ... Trying to come up with a foolproof way to add the current ADO library to the project's references. I used to do this by just saving the .xla with a reference to a low version, 2.5 and that worked fine for a long time, but then came across a user where this failed. Then I had a method that got the ADO library file path from registry reads using code from KeepITCool: Function GetLibrary(sProgID$) As String Dim oReg As Object, sDat$ Const HKCR = &H80000000 Set oReg = GetObject( _ "winmgmts:{impersonationLevel=impersonate}!\\.\roo t\default:StdRegProv") oReg.getstringvalue _ HKCR, sProgID & "\CLSID", vbNullString, sDat oReg.getstringvalue _ HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat GetLibrary = sDat End Function Except I re-wrote this by using the Windows API. This solved this one user's problem, but it failed with others, not exactly sure why. Currently I use this method: Save the .xla with the lowest ADO version I have on my development machine, 2.1 In the Workbook_Open event remove this reference and add the current library like this, slightly simplified: Function AddReferenceFromFile(strFilePath As String, _ Optional strWorkbook As String) As Boolean Dim VBProj As VBProject On Error GoTo ERROROUT If Len(strWorkbook) = 0 Then strWorkbook = ThisWorkbook.Name End If Set VBProj = Workbooks(strWorkbook).VBProject VBProj.References.AddFromFile strFilePath Exit Function ERROROUT: End Function Sub SetADOReference() Dim i As Byte Dim ADOConn As Object Dim strADOVersion As String Dim strADOFolder As String Dim strADOFile As String Dim strADOPathFromINI As String Dim arrADOFiles Const strINIPath As String = "C:\test.ini" strADOPathFromINI = ReadINIValue(strINIPath, _ "Add-in behaviour", _ "Full path to ADO library") If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) 0 Then If AddReferenceFromFile(strADOPathFromINI) = True Then Exit Sub End If End If strADOFolder = Left$(Application.Path, 1) & _ ":\Program Files\Common Files\System\ADO\" Set ADOConn = CreateObject("ADODB.Connection") strADOVersion = Left$(ADOConn.Version, 3) Set ADOConn = Nothing Select Case strADOVersion Case "2.8" strADOFile = "msado15.dll" Case "2.7" strADOFile = "msado27.tlb" Case "2.6" strADOFile = "msado26.tlb" Case "2.5" strADOFile = "msado25.tlb" Case "2.1" strADOFile = "msado21.tlb" Case "2.0" strADOFile = "msado20.tlb" End Select If AddReferenceFromFile(strADOFolder & strADOFile) = True Then Exit Sub End If arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _ "msado25.tlb", "msado21.tlb", "msado20.tlb") For i = 0 To 5 If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then Exit Sub End If Next MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _ "Please contact Bart Smissaert: ", _ vbExclamation, "adding ADO reference" End Sub Sofar this seems to work fine. I know it is overkill, but as this is so tricky I can't be careful enough. The .ini file read should always make it possible for the user to set the right path, but this can be skipped for starters. I am not 100% sure the Select Case sequence is right, but then there always is the brute force method with the array. Have read a lot of postings about this problem, but there doesn't seem to be any definite, single best way how to tackle this. Any pitfalls here or any suggestions for improvement? RBS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
Foolproof .find | Excel Programming | |||
Row reference increment but preserve column reference | Excel Worksheet Functions |