Mixing
ADODB.RecordSet.MoveNext going up by 2
I am importing a CSV into ACCESS using ADODB recordset. I am trying to iterate through the recordset to get data I need, but when I use the .MoveNext method it goes up 2 instead of one. By that I mean AbsolutePosition counts by 2s (1,3,5..) and I hit the EOF when the rowcounter I am using hits exactly half of the total number of records
Public Function ConnectToCSV(filePath As String, fileName As String)
Dim connString As String
Dim connection As ADODB.connection
Dim RS As New ADODB.recordSet
Dim currentRow As Long
dim readArray() as Variant
Set connection = New ADODB.connection
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & filePath & fileName & _
";Extended Properties='text;HDR=YES;FMT=Delimited';"
connection.Open connString
RS.Open "SELECT * FROM " & fileName, connection, adOpenStatic, adLockReadOnly, adCmdText
currentRow = 0
RS.MoveLast
RS.MoveFirst
While Not RS.EOF
readArray = RS.GetRows(1)
RS.MoveNext
currentRow = currentRow + 1
Wend
End Function
vba access-vba adodb recordset
asked Jan 2 at 20:08
DanGDanG
236
|
show 2 more comments
I am importing a CSV into ACCESS using ADODB recordset. I am trying to iterate through the recordset to get data I need, but when I use the .MoveNext method it goes up 2 instead of one. By that I mean AbsolutePosition counts by 2s (1,3,5..) and I hit the EOF when the rowcounter I am using hits exactly half of the total number of records
Public Function ConnectToCSV(filePath As String, fileName As String)
Dim connString As String
Dim connection As ADODB.connection
Dim RS As New ADODB.recordSet
Dim currentRow As Long
dim readArray() as Variant
Set connection = New ADODB.connection
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & filePath & fileName & _
";Extended Properties='text;HDR=YES;FMT=Delimited';"
connection.Open connString
RS.Open "SELECT * FROM " & fileName, connection, adOpenStatic, adLockReadOnly, adCmdText
currentRow = 0
RS.MoveLast
RS.MoveFirst
While Not RS.EOF
readArray = RS.GetRows(1)
RS.MoveNext
currentRow = currentRow + 1
Wend
End Function
vba access-vba adodb recordset
asked Jan 2 at 20:08
DanGDanG
236
Why are you callingMoveLastandMoveFirstbefore entering the loop?
– Dai
Jan 2 at 20:15
@Dai IIRC, it's a bug with the ADODB.Recordsets. Usually do this to get an accurate RecordCount.
– Ryan Wildry
Jan 2 at 20:16
Also, please show the actual code inside yourWhileloop - it's possible you're seeing side-effects.
– Dai
Jan 2 at 20:16
Can you replicate this with a contrived file that follows the same format? I wonder if it is how the file is formatted.
– Ryan Wildry
Jan 2 at 20:17
@Dai I added what I am actually doing - for now putting the row into an array using GetRows
– DanG
Jan 2 at 20:18
|
show 2 more comments
I am importing a CSV into ACCESS using ADODB recordset. I am trying to iterate through the recordset to get data I need, but when I use the .MoveNext method it goes up 2 instead of one. By that I mean AbsolutePosition counts by 2s (1,3,5..) and I hit the EOF when the rowcounter I am using hits exactly half of the total number of records
Public Function ConnectToCSV(filePath As String, fileName As String)
Dim connString As String
Dim connection As ADODB.connection
Dim RS As New ADODB.recordSet
Dim currentRow As Long
dim readArray() as Variant
Set connection = New ADODB.connection
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & filePath & fileName & _
";Extended Properties='text;HDR=YES;FMT=Delimited';"
connection.Open connString
RS.Open "SELECT * FROM " & fileName, connection, adOpenStatic, adLockReadOnly, adCmdText
currentRow = 0
RS.MoveLast
RS.MoveFirst
While Not RS.EOF
readArray = RS.GetRows(1)
RS.MoveNext
currentRow = currentRow + 1
Wend
End Function
vba access-vba adodb recordset
asked Jan 2 at 20:08
DanGDanG
236
I am importing a CSV into ACCESS using ADODB recordset. I am trying to iterate through the recordset to get data I need, but when I use the .MoveNext method it goes up 2 instead of one. By that I mean AbsolutePosition counts by 2s (1,3,5..) and I hit the EOF when the rowcounter I am using hits exactly half of the total number of records
Public Function ConnectToCSV(filePath As String, fileName As String)
Dim connString As String
Dim connection As ADODB.connection
Dim RS As New ADODB.recordSet
Dim currentRow As Long
dim readArray() as Variant
Set connection = New ADODB.connection
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & filePath & fileName & _
";Extended Properties='text;HDR=YES;FMT=Delimited';"
connection.Open connString
RS.Open "SELECT * FROM " & fileName, connection, adOpenStatic, adLockReadOnly, adCmdText
currentRow = 0
RS.MoveLast
RS.MoveFirst
While Not RS.EOF
readArray = RS.GetRows(1)
RS.MoveNext
currentRow = currentRow + 1
Wend
End Function
vba access-vba adodb recordset
vba access-vba adodb recordset
asked Jan 2 at 20:08
DanGDanG
236
asked Jan 2 at 20:08
DanGDanG
236
edited Jan 2 at 20:18
DanG
asked Jan 2 at 20:08
DanGDanG
236
asked Jan 2 at 20:08
DanGDanG
236
asked Jan 2 at 20:08
DanGDanG
236
236
Why are you callingMoveLastandMoveFirstbefore entering the loop?
– Dai
Jan 2 at 20:15
@Dai IIRC, it's a bug with the ADODB.Recordsets. Usually do this to get an accurate RecordCount.
– Ryan Wildry
Jan 2 at 20:16
Also, please show the actual code inside yourWhileloop - it's possible you're seeing side-effects.
– Dai
Jan 2 at 20:16
Can you replicate this with a contrived file that follows the same format? I wonder if it is how the file is formatted.
– Ryan Wildry
Jan 2 at 20:17
@Dai I added what I am actually doing - for now putting the row into an array using GetRows
– DanG
Jan 2 at 20:18
|
show 2 more comments
Why are you callingMoveLastandMoveFirstbefore entering the loop?
– Dai
Jan 2 at 20:15
@Dai IIRC, it's a bug with the ADODB.Recordsets. Usually do this to get an accurate RecordCount.
– Ryan Wildry
Jan 2 at 20:16
Also, please show the actual code inside yourWhileloop - it's possible you're seeing side-effects.
– Dai
Jan 2 at 20:16
Can you replicate this with a contrived file that follows the same format? I wonder if it is how the file is formatted.
– Ryan Wildry
Jan 2 at 20:17
@Dai I added what I am actually doing - for now putting the row into an array using GetRows
– DanG
Jan 2 at 20:18
Why are you calling
MoveLast and MoveFirst before entering the loop?– Dai
Jan 2 at 20:15
Why are you calling
MoveLast and MoveFirst before entering the loop?– Dai
Jan 2 at 20:15
@Dai IIRC, it's a bug with the ADODB.Recordsets. Usually do this to get an accurate RecordCount.
– Ryan Wildry
Jan 2 at 20:16
@Dai IIRC, it's a bug with the ADODB.Recordsets. Usually do this to get an accurate RecordCount.
– Ryan Wildry
Jan 2 at 20:16
Also, please show the actual code inside your
While loop - it's possible you're seeing side-effects.– Dai
Jan 2 at 20:16
Also, please show the actual code inside your
While loop - it's possible you're seeing side-effects.– Dai
Jan 2 at 20:16
Can you replicate this with a contrived file that follows the same format? I wonder if it is how the file is formatted.
– Ryan Wildry
Jan 2 at 20:17
Can you replicate this with a contrived file that follows the same format? I wonder if it is how the file is formatted.
– Ryan Wildry
Jan 2 at 20:17
@Dai I added what I am actually doing - for now putting the row into an array using GetRows
– DanG
Jan 2 at 20:18
@Dai I added what I am actually doing - for now putting the row into an array using GetRows
– DanG
Jan 2 at 20:18
|
show 2 more comments
1 Answer
1
active
oldest
votes
The .GetRows(1) method I was using was already moving to the next record after retrieving the current record. By using the .MoveNext as well it was causing it to jump by 2 each loop.
I removed the .MoveNext and it appears to be working properly (jumping by 1)
answered Jan 2 at 20:24
DanGDanG
236
You would get the same result by putting MoveNext back and removing currentRow = currentRow + 1.
– Karlomanio
Jan 2 at 20:27
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54012518%2fadodb-recordset-movenext-going-up-by-2%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The .GetRows(1) method I was using was already moving to the next record after retrieving the current record. By using the .MoveNext as well it was causing it to jump by 2 each loop.
I removed the .MoveNext and it appears to be working properly (jumping by 1)
answered Jan 2 at 20:24
DanGDanG
236
You would get the same result by putting MoveNext back and removing currentRow = currentRow + 1.
– Karlomanio
Jan 2 at 20:27
add a comment |
The .GetRows(1) method I was using was already moving to the next record after retrieving the current record. By using the .MoveNext as well it was causing it to jump by 2 each loop.
I removed the .MoveNext and it appears to be working properly (jumping by 1)
answered Jan 2 at 20:24
DanGDanG
236
You would get the same result by putting MoveNext back and removing currentRow = currentRow + 1.
– Karlomanio
Jan 2 at 20:27
add a comment |
The .GetRows(1) method I was using was already moving to the next record after retrieving the current record. By using the .MoveNext as well it was causing it to jump by 2 each loop.
I removed the .MoveNext and it appears to be working properly (jumping by 1)
answered Jan 2 at 20:24
DanGDanG
236
The .GetRows(1) method I was using was already moving to the next record after retrieving the current record. By using the .MoveNext as well it was causing it to jump by 2 each loop.
I removed the .MoveNext and it appears to be working properly (jumping by 1)
answered Jan 2 at 20:24
DanGDanG
236
answered Jan 2 at 20:24
DanGDanG
236
answered Jan 2 at 20:24
DanGDanG
236
answered Jan 2 at 20:24
DanGDanG
236
236
You would get the same result by putting MoveNext back and removing currentRow = currentRow + 1.
– Karlomanio
Jan 2 at 20:27
add a comment |
You would get the same result by putting MoveNext back and removing currentRow = currentRow + 1.
– Karlomanio
Jan 2 at 20:27
You would get the same result by putting MoveNext back and removing currentRow = currentRow + 1.
– Karlomanio
Jan 2 at 20:27
You would get the same result by putting MoveNext back and removing currentRow = currentRow + 1.
– Karlomanio
Jan 2 at 20:27
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54012518%2fadodb-recordset-movenext-going-up-by-2%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Why are you calling
MoveLastandMoveFirstbefore entering the loop?– Dai
Jan 2 at 20:15
@Dai IIRC, it's a bug with the ADODB.Recordsets. Usually do this to get an accurate RecordCount.
– Ryan Wildry
Jan 2 at 20:16
Also, please show the actual code inside your
Whileloop - it's possible you're seeing side-effects.– Dai
Jan 2 at 20:16
Can you replicate this with a contrived file that follows the same format? I wonder if it is how the file is formatted.
– Ryan Wildry
Jan 2 at 20:17
@Dai I added what I am actually doing - for now putting the row into an array using GetRows
– DanG
Jan 2 at 20:18