This problem occurs every time that you run the code within the Visual Basic IDE.
NOTE This error does not occur if the all the fields are selected in the SQL statement, for example:
SELECT * FROM ...
Steps to Reproduce Behavior
- Create a spreadsheet in Microsoft Excel 97 or Microsoft Excel 2000 and place the following values in the prescribe fields:
- A1 = F1
- B1 = F2
- C1 = F3
- Save the spreadsheet and create a DSN pointing to it.
- Start Visual Basic 6.0 from Visual Studio 6.0 Service Pack 3 or later.
- Create a new Standard EXE and set a reference to Microsoft Active Data Objects. Paste the following code into the form:
Public Sub Form_Load()
Dim constring As String
Dim Connection As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
' define a DSN IMPORT-XLS, using the Excel ODBC driver, and point it to the xls file
constring = "Provider=MSDASQL;DSN=IMPORT-XLS"
Set cmd.ActiveConnection = Connection
cmd.CommandType = adCmdText
' The following .CommandText causes the error, but only the first time it is run within the ide.
cmd.CommandText = "Select F1, F2 from ""Sheet1$"""
' The following .CommandText will not cause the error.
' cmd.CommandText = "Select * from ""Sheet1$"""
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
While Not rs.EOF
- Make the necessary changes to the ConnectionString.
- Run the form.
- The first time the form runs, the error appears; it does not appear in subsequent runs.
Article ID: 246167 - Last Review: Aug 4, 2008 - Revision: 1