Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

Moveprevious throwing an error with Excel recordset

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
thach1ef2
Asked:
thach1ef2
  • 2
  • 2
1 Solution
 
GrahamSkanRetiredCommented:
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
 
thach1ef2Author Commented:
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
 
GrahamSkanRetiredCommented:
OK. Thanks for keeping us up-to-date.
0
 
thach1ef2Author Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now