Moveprevious throwing an error with Excel recordset

Posted on 2011-03-24
Medium Priority
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.
Question by:thach1ef2
  • 2
  • 2
LVL 77

Accepted Solution

GrahamSkan earned 2000 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
            Debug.Print rs.Fields("Field1").Value
            Debug.Print rs.Fields("Field1").Value
            Debug.Print rs.Fields("Field1").Value
End Sub

Open in new window


Author Comment

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.
LVL 77

Expert Comment

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

Author Comment

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.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

624 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