November 1, 2002 at 3:50 am
I have noticed differences between different versions of the ODBC SQL Server driver when creating links in Access 97 VBA against SQL Server 2000.
I would like to check in my VBA code whether the latest SQL Server driver is loaded or at least a version later than a specific number or date.
Can anyone provide example code to check for and install ODBC drivers from VBA code.
Also any advice as to which are best version of the ODBC driver to use.
I noticed problems with:
3.70.06.23 4/5/1999
But following seem OK:
2000.80.528.00 18/10/2001
2000.80.194.00 06/08/2000
November 1, 2002 at 5:06 am
The only way I know is using ADO to make the connection and get the driver versions back.
From MSDN
quote:
Version PropertyIndicates the ADO version number.
Return Value
Returns a String value.
Remarks
Use the Version property to return the version number of the ADO implementation.
The version of the provider will be available as a dynamic property in the Properties collection.
Version Property Example
This example uses the Version property of a Connection object to display the current ADO version. It also uses several dynamic properties to show the current DBMS name and version, OLE DB version, provider name and version, driver name and version, and driver ODBC version.
Public Sub VersionX()
Dim cnn1 As ADODB.Connection
' Open connection.
Set cnn1 = New ADODB.Connection
strCnn = “driver={SQL Server};server=srv;” & _
“user id=sa;password=;database=pubs;”
cnn1.Open strCnn
strVersionInfo = "ADO Version: " & cnn1.Version & vbCr & _
"DBMS Name: " & cnn1.Properties("DBMS Name") & vbCr & _
"DBMS Version: " & cnn1.Properties("DBMS Version") & vbCr & _
"OLE DB Version: " & cnn1.Properties("OLE DB Version") & vbCr & _
"Provider Name: " & cnn1.Properties("Provider Name") & vbCr & _
"Provider Version: " & cnn1.Properties("Provider Version") & vbCr & _
"Driver Name: " & cnn1.Properties("Driver Name") & vbCr & _
"Driver Version: " & cnn1.Properties("Driver Version") & vbCr & _
"Driver ODBC Version: " & cnn1.Properties("Driver ODBC Version")
MsgBox strVersionInfo
cnn1.Close
End Sub
Other than that you have to search the registry or get the DLL files path and reads it's properties.
November 4, 2002 at 9:08 am
Thanks, your answer helps me with part of the question and that it how to identify the ODBC version.
Access 97 VBA does not like the syntax of the code you supplied but I will persevere and hopefully can work it out.
November 5, 2002 at 10:17 am
Hi I got the code to work in Access 97 by changing it to the following:
Public Sub VersionX()
Dim cnn As ADODB.Connection
Dim strVersionInfo As String
' Open connection.
Set cnn = New ADODB.Connection
cnn.Open _
"Provider=SQLOLEDB.1;" & _
"Data Source=myipaddress;Initial Catalogu=kbdemo;" & _
"User id=myid;password=mypass"
strVersionInfo = "ADO Version: " & cnn.Version & vbCr & _
"DBMS Name: " & cnn.Properties("DBMS Name") & vbCr & _
"DBMS Version: " & cnn.Properties("DBMS Version") & vbCr & _
"OLE DB Version: " & cnn.Properties("OLE DB Version") & vbCr & _
"Provider Name: " & cnn.Properties("Provider Name") & vbCr & _
"Provider Version: " & cnn.Properties("Provider Version") & vbCr
MsgBox strVersionInfo
cnn.Close
End Sub
I had to remove the lines:
"Driver Name: " & cnn.Properties("Driver Name") & vbCr & _
"Driver Version: " & cnn.Properties("Driver Version") & vbCr & _
"Driver ODBC Version: " & cnn.Properties("Driver ODBC Version")
With those lines included I got the message "Item cannot be found in the collection corresponding to the requested name or ordinal.
November 5, 2002 at 11:02 am
Thanks for the update. The driver must not include those items in teh colletion.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply