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
Solved

Moveprevious throwing an error with Excel recordset

Posted on 2011-03-24
4
571 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
  • 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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

861 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