dkj
07-08-2005, 11:08 AM
As a newbie that has inherited the further customization of an application I have been experiencing an unusual problem.
I have an EXCEL VBA application (Excel 2002) that sends data to a SQLSERVER which works most of the time.
I have gone into References and selected "Microsoft ActiveX Data Objects Recordset 2.7 Library"
Every now and again I recieve my Excel application back from a user and when I try to run the application and load the SQLSERVER I get an error. When I check the References I find "MISSING: Microsoft ActiveX Data Objects Recordset 2.8 Library"
To resolve the problem I uncheck the MISSING:... option, close the References drop down, re-open the references and check what I had originally checked. "Microsoft ActiveX Data Objects Recordset 2.7 Library"
Is there a way of putting something in the code to prevent this from happening of does every one of the users have to be running the same version of Excel?
I know that this is somewhat long winded but I have spent considerable time looking for solutions in the many different forums and through textbooks.
The connection code looks like this:
'==================== DATABASE CONNECTION =============
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim lngRecsAff As Long
' now lets try to make the database connection here now
Set cn = New ADODB.Connection
cn.Open dbConnString
Set rs = New ADODB.Recordset
cn.Execute sqlInsStr, lngRecsAff, adExecuteNoRecords
Dim strCustomerReferenceNumber As String
strCustomerReferenceNumber = Replace(Sheet1.Cells(4, 5), "'", "''")
' now lets get the primary key
strSQL = "SELECT customerID FROM ipOppCustomer where customerReferenceNumber = '" & strCustomerReferenceNumber & "'"
rs.Open strSQL, cn
Sheet1.Cells(222, 2) = rs("customerID")
rs.Close
' now close the connection
cn.Close
Set cn = Nothing
'==================== END DATABASE CONNECTION ======
I have an EXCEL VBA application (Excel 2002) that sends data to a SQLSERVER which works most of the time.
I have gone into References and selected "Microsoft ActiveX Data Objects Recordset 2.7 Library"
Every now and again I recieve my Excel application back from a user and when I try to run the application and load the SQLSERVER I get an error. When I check the References I find "MISSING: Microsoft ActiveX Data Objects Recordset 2.8 Library"
To resolve the problem I uncheck the MISSING:... option, close the References drop down, re-open the references and check what I had originally checked. "Microsoft ActiveX Data Objects Recordset 2.7 Library"
Is there a way of putting something in the code to prevent this from happening of does every one of the users have to be running the same version of Excel?
I know that this is somewhat long winded but I have spent considerable time looking for solutions in the many different forums and through textbooks.
The connection code looks like this:
'==================== DATABASE CONNECTION =============
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim lngRecsAff As Long
' now lets try to make the database connection here now
Set cn = New ADODB.Connection
cn.Open dbConnString
Set rs = New ADODB.Recordset
cn.Execute sqlInsStr, lngRecsAff, adExecuteNoRecords
Dim strCustomerReferenceNumber As String
strCustomerReferenceNumber = Replace(Sheet1.Cells(4, 5), "'", "''")
' now lets get the primary key
strSQL = "SELECT customerID FROM ipOppCustomer where customerReferenceNumber = '" & strCustomerReferenceNumber & "'"
rs.Open strSQL, cn
Sheet1.Cells(222, 2) = rs("customerID")
rs.Close
' now close the connection
cn.Close
Set cn = Nothing
'==================== END DATABASE CONNECTION ======