Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Moveprevious throwing an error with Excel recordset

Posted on 2011-03-24
4
Medium Priority
?
586 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 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
            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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

916 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