Hi,

We have VBA code that needs to access records in an Excel spreadsheet. Simple one columns sheet - no header row. This code works fine on an XP operating system, however when loaded on a Vista system then we get the follwoing issue:

"-2147467259Method 'Open' of object '_Recordset' failed"

As soon as RS.open is executed it falls over with a very undescriptive error.

Here’s the code

Private Sub ImportSerialNumbers_Changed()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Msg As String
Dim sConn As String

Set cn = New ADODB.Connection

Dim sSql As String
Dim sOutput As String

Set rs = New ADODB.Recordset

Dim Message, Title, Default, MyValue

On Error GoTo ErrorHandler
RemoveAll = 1
Message = "Please enter the full path & File name of the Excel Spreadsheet"
Title = "Import of Serial Numbers"
Default = "c:\"
MyValue = InputBox(Message, Title, Default)


With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & MyValue & "; FIRSTROWHASNAMES = 0"
.Open
End With

rs.CursorLocation = adUseClient
rs.CursorType = adOpenKeyset
rs.Open "SELECT * FROM [sheet1$]", cn.ConnectionString
‘****************THIS IS WHERE THE ERROR HAPPENS**************

If Not (rs.BOF Or rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF
sOutput = rs.Fields(0).Value
SerialNumber = sOutput
Insert = 1
rs.MoveNext
Loop

rs.Close
cn.Close

sOutput = Left(sOutput, Len(sOutput) - 1)
Else
sOutput = "Empty Recordset"
End If

Debug.Print sOutput

Set rs = Nothing
Set cn = Nothing

ErrorHandler:
MsgBox Err.Number & Err.Description
Exit Sub
End Sub

Any suggestions much appreciated. We have tried using different versions of Excel, Drivers, ADO drivers.

Frank