Solved

Moveprevious throwing an error with Excel recordset

Posted on 2011-03-24
4
572 Views
Last Modified: 2012-05-11
To start, I'm very new with recordsets, ADO, and all that. However I'm being thrown into the fire so to speak with debugging someone's work that used these things. With that being said, I may not be 100% accurate in how I'm describing things. If so, please forgive me.

I have an excel spread sheet with 4 columns. I have a VB script that is reading this information and entering it into an external application. So far, I'm able to read, update columns, movenext etc etc. However it seems that MovePrevious is not working with this recordset.

The recordset is using adOpenStatic and adLockOptimistic and from what I could find, these aren't making it a move forward only item.

Say my spreadsheet has 5 rows. I process the first 3 and now I'm on the 4th. If I do a MovePrevious here it errors our that "Either EoF or BoF is true..." Checking what happens with an IF statement, it says that I'm at the BoF...

TBH, I'm completely lost here. I think I'm missing something painfully obvious with this due to my lack of knowledge.
0
Comment
Question by:thach1ef2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 35211955
From your description, there is nothing obviously wrong with what you are doing, however the problem might lie in some complexity in the code.

Here is some simple code that doesn't reproduce the problem for me (Excel 2003, Win 7 & Microsoft Active data Objects 2.8 Library).
Option Explicit

Private Sub Command1_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConnection As String
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder\Book1.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    cn.Open strConnection
        rs.Open "Select * FROM [Sheet1$]", strConnection, adOpenStatic, adLockOptimistic, adCmdText
            Debug.Print rs.Fields("Field1").Value
            rs.MoveNext
            Debug.Print rs.Fields("Field1").Value
            rs.MoveNext
            Debug.Print rs.Fields("Field1").Value
            rs.MovePrevious
            Debug.Print rs.Fields("Field1").Value
        rs.Close
    cn.Close
End Sub

Open in new window

0
 

Author Comment

by:thach1ef2
ID: 35236690
Sorry for the delay. I was away for the weekend.

Your code works fine for me as well without any errors. I've slowly added bits and pieces of my code to your code portion to see if it still works and everything so far has been fine. I simply can't figure out the problem point.

I'm going to continue testing and repost when I have a better idea what could be causing this issue.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 35236930
OK. Thanks for keeping us up-to-date.
0
 

Author Comment

by:thach1ef2
ID: 35236940
I really don't know what happened, but I am not able to reproduce this error anymore. It was reproducible 100% of the time when I posted this question and now it appears to be non existent.

Thank you for your help. I will close this and reference back to it if I encounter this issue again.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question