Uploading data using VBA ADO Transaction
I'm having problem while uploading data from excel file (VBA) to Access backend.
I'm trying to upload 2 records in one transaction. It's done using loop.
Relevant part of code.
cnn.BeginTrans
'(some test data)
SomeNumber = 2
NewText = "new text"
For i = 0 To 1
Set cmd = New ADODB.Command
'(increase [counter] field in database)
With cmd
.ActiveConnection = cnn
.CommandType = adCmdText
.CommandText = "UPDATE [tbltest] " & _
"SET [counter] = [counter] + 1 " & _
"WHERE [counter] > ?"
.Parameters.Append .CreateParameter(, adInteger, adParamInput, , SomeNumber)
.Execute
End With
Set cmd = New ADODB.Command
'(insert new row in gap)
With cmd
.ActiveConnection = cnn
.CommandType = adCmdText
.CommandText = "INSERT INTO [tbltest] ([counter], [txtfield]) " & _
"VALUES (?, ?)"
.Parameters.Append .CreateParameter(, adInteger, adParamInput, , SomeNumber + 1)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, NewText & i)
.Execute
End With
Next i
cnn.CommitTrans
tblTest at begining
Counter, txtfield
1 , Sometext1
2 , Sometext2
3 , Sometext3
4 , Sometext4
Expected output
Counter, txtfield
1 , Sometext1
2 , Sometext2
5 , Sometext3
6 , Sometext4
3 , Sometext1
4 , Sometext0
but what i get is
Counter, txtfield
1 , Sometext1
2 , Sometext2
5 , Sometext3
6 , Sometext4
3 , Sometext1
3 , Sometext0
So it looks like counter inserted in first loop isn't updated in second.
I've tested same code using SQL Server as backend and it works as intended.
But for some reason it's working different with Access backend.
Am I missing something or it can't be done this way using Access as backend?
EDIT
Line Set cmd = New ADODB.Command should be inside for loop before update command, not outside.
sql excel vba excel-vba ms-access
edited Dec 12 '18 at 11:11
krish KM
4,1751727
asked Nov 23 '18 at 11:35
SphinxSphinx
437413
|
show 3 more comments
I'm having problem while uploading data from excel file (VBA) to Access backend.
I'm trying to upload 2 records in one transaction. It's done using loop.
Relevant part of code.
cnn.BeginTrans
'(some test data)
SomeNumber = 2
NewText = "new text"
For i = 0 To 1
Set cmd = New ADODB.Command
'(increase [counter] field in database)
With cmd
.ActiveConnection = cnn
.CommandType = adCmdText
.CommandText = "UPDATE [tbltest] " & _
"SET [counter] = [counter] + 1 " & _
"WHERE [counter] > ?"
.Parameters.Append .CreateParameter(, adInteger, adParamInput, , SomeNumber)
.Execute
End With
Set cmd = New ADODB.Command
'(insert new row in gap)
With cmd
.ActiveConnection = cnn
.CommandType = adCmdText
.CommandText = "INSERT INTO [tbltest] ([counter], [txtfield]) " & _
"VALUES (?, ?)"
.Parameters.Append .CreateParameter(, adInteger, adParamInput, , SomeNumber + 1)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, NewText & i)
.Execute
End With
Next i
cnn.CommitTrans
tblTest at begining
Counter, txtfield
1 , Sometext1
2 , Sometext2
3 , Sometext3
4 , Sometext4
Expected output
Counter, txtfield
1 , Sometext1
2 , Sometext2
5 , Sometext3
6 , Sometext4
3 , Sometext1
4 , Sometext0
but what i get is
Counter, txtfield
1 , Sometext1
2 , Sometext2
5 , Sometext3
6 , Sometext4
3 , Sometext1
3 , Sometext0
So it looks like counter inserted in first loop isn't updated in second.
I've tested same code using SQL Server as backend and it works as intended.
But for some reason it's working different with Access backend.
Am I missing something or it can't be done this way using Access as backend?
EDIT
Line Set cmd = New ADODB.Command should be inside for loop before update command, not outside.
sql excel vba excel-vba ms-access
edited Dec 12 '18 at 11:11
krish KM
4,1751727
asked Nov 23 '18 at 11:35
SphinxSphinx
437413
SomeNumber is always 2
– Nathan_Sav
Nov 23 '18 at 11:46
I don't see how that could work with any back end when you don't incrementsomenumberinside the loop so you're always inserting 3 as the newcountervalue.
– Rory
Nov 23 '18 at 11:46
Yes but it should be incremented byUPDATEin next loop (when i = 1)
– Sphinx
Nov 23 '18 at 11:49
Is the SQL definately getting passed?cnn.CommitTrans
– Nathan_Sav
Nov 23 '18 at 12:23
1
Have you used any break points to check your tables after each insert?
– Nathan_Sav
Nov 23 '18 at 12:49
|
show 3 more comments
I'm having problem while uploading data from excel file (VBA) to Access backend.
I'm trying to upload 2 records in one transaction. It's done using loop.
Relevant part of code.
cnn.BeginTrans
'(some test data)
SomeNumber = 2
NewText = "new text"
For i = 0 To 1
Set cmd = New ADODB.Command
'(increase [counter] field in database)
With cmd
.ActiveConnection = cnn
.CommandType = adCmdText
.CommandText = "UPDATE [tbltest] " & _
"SET [counter] = [counter] + 1 " & _
"WHERE [counter] > ?"
.Parameters.Append .CreateParameter(, adInteger, adParamInput, , SomeNumber)
.Execute
End With
Set cmd = New ADODB.Command
'(insert new row in gap)
With cmd
.ActiveConnection = cnn
.CommandType = adCmdText
.CommandText = "INSERT INTO [tbltest] ([counter], [txtfield]) " & _
"VALUES (?, ?)"
.Parameters.Append .CreateParameter(, adInteger, adParamInput, , SomeNumber + 1)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, NewText & i)
.Execute
End With
Next i
cnn.CommitTrans
tblTest at begining
Counter, txtfield
1 , Sometext1
2 , Sometext2
3 , Sometext3
4 , Sometext4
Expected output
Counter, txtfield
1 , Sometext1
2 , Sometext2
5 , Sometext3
6 , Sometext4
3 , Sometext1
4 , Sometext0
but what i get is
Counter, txtfield
1 , Sometext1
2 , Sometext2
5 , Sometext3
6 , Sometext4
3 , Sometext1
3 , Sometext0
So it looks like counter inserted in first loop isn't updated in second.
I've tested same code using SQL Server as backend and it works as intended.
But for some reason it's working different with Access backend.
Am I missing something or it can't be done this way using Access as backend?
EDIT
Line Set cmd = New ADODB.Command should be inside for loop before update command, not outside.
sql excel vba excel-vba ms-access
edited Dec 12 '18 at 11:11
krish KM
4,1751727
asked Nov 23 '18 at 11:35
SphinxSphinx
437413
I'm having problem while uploading data from excel file (VBA) to Access backend.
I'm trying to upload 2 records in one transaction. It's done using loop.
Relevant part of code.
cnn.BeginTrans
'(some test data)
SomeNumber = 2
NewText = "new text"
For i = 0 To 1
Set cmd = New ADODB.Command
'(increase [counter] field in database)
With cmd
.ActiveConnection = cnn
.CommandType = adCmdText
.CommandText = "UPDATE [tbltest] " & _
"SET [counter] = [counter] + 1 " & _
"WHERE [counter] > ?"
.Parameters.Append .CreateParameter(, adInteger, adParamInput, , SomeNumber)
.Execute
End With
Set cmd = New ADODB.Command
'(insert new row in gap)
With cmd
.ActiveConnection = cnn
.CommandType = adCmdText
.CommandText = "INSERT INTO [tbltest] ([counter], [txtfield]) " & _
"VALUES (?, ?)"
.Parameters.Append .CreateParameter(, adInteger, adParamInput, , SomeNumber + 1)
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50, NewText & i)
.Execute
End With
Next i
cnn.CommitTrans
tblTest at begining
Counter, txtfield
1 , Sometext1
2 , Sometext2
3 , Sometext3
4 , Sometext4
Expected output
Counter, txtfield
1 , Sometext1
2 , Sometext2
5 , Sometext3
6 , Sometext4
3 , Sometext1
4 , Sometext0
but what i get is
Counter, txtfield
1 , Sometext1
2 , Sometext2
5 , Sometext3
6 , Sometext4
3 , Sometext1
3 , Sometext0
So it looks like counter inserted in first loop isn't updated in second.
I've tested same code using SQL Server as backend and it works as intended.
But for some reason it's working different with Access backend.
Am I missing something or it can't be done this way using Access as backend?
EDIT
Line Set cmd = New ADODB.Command should be inside for loop before update command, not outside.
sql excel vba excel-vba ms-access
sql excel vba excel-vba ms-access
edited Dec 12 '18 at 11:11
krish KM
4,1751727
asked Nov 23 '18 at 11:35
SphinxSphinx
437413
edited Dec 12 '18 at 11:11
krish KM
4,1751727
asked Nov 23 '18 at 11:35
SphinxSphinx
437413
edited Dec 12 '18 at 11:11
krish KM
4,1751727
edited Dec 12 '18 at 11:11
krish KM
4,1751727
edited Dec 12 '18 at 11:11
krish KM
4,1751727
4,1751727
asked Nov 23 '18 at 11:35
SphinxSphinx
437413
asked Nov 23 '18 at 11:35
SphinxSphinx
437413
asked Nov 23 '18 at 11:35
SphinxSphinx
437413
437413
SomeNumber is always 2
– Nathan_Sav
Nov 23 '18 at 11:46
I don't see how that could work with any back end when you don't incrementsomenumberinside the loop so you're always inserting 3 as the newcountervalue.
– Rory
Nov 23 '18 at 11:46
Yes but it should be incremented byUPDATEin next loop (when i = 1)
– Sphinx
Nov 23 '18 at 11:49
Is the SQL definately getting passed?cnn.CommitTrans
– Nathan_Sav
Nov 23 '18 at 12:23
1
Have you used any break points to check your tables after each insert?
– Nathan_Sav
Nov 23 '18 at 12:49
|
show 3 more comments
SomeNumber is always 2
– Nathan_Sav
Nov 23 '18 at 11:46
I don't see how that could work with any back end when you don't incrementsomenumberinside the loop so you're always inserting 3 as the newcountervalue.
– Rory
Nov 23 '18 at 11:46
Yes but it should be incremented byUPDATEin next loop (when i = 1)
– Sphinx
Nov 23 '18 at 11:49
Is the SQL definately getting passed?cnn.CommitTrans
– Nathan_Sav
Nov 23 '18 at 12:23
1
Have you used any break points to check your tables after each insert?
– Nathan_Sav
Nov 23 '18 at 12:49
SomeNumber is always 2
– Nathan_Sav
Nov 23 '18 at 11:46
SomeNumber is always 2
– Nathan_Sav
Nov 23 '18 at 11:46
I don't see how that could work with any back end when you don't increment
somenumber inside the loop so you're always inserting 3 as the new counter value.– Rory
Nov 23 '18 at 11:46
I don't see how that could work with any back end when you don't increment
somenumber inside the loop so you're always inserting 3 as the new counter value.– Rory
Nov 23 '18 at 11:46
Yes but it should be incremented by
UPDATE in next loop (when i = 1)– Sphinx
Nov 23 '18 at 11:49
Yes but it should be incremented by
UPDATE in next loop (when i = 1)– Sphinx
Nov 23 '18 at 11:49
Is the SQL definately getting passed?
cnn.CommitTrans– Nathan_Sav
Nov 23 '18 at 12:23
Is the SQL definately getting passed?
cnn.CommitTrans– Nathan_Sav
Nov 23 '18 at 12:23
1
1
Have you used any break points to check your tables after each insert?
– Nathan_Sav
Nov 23 '18 at 12:49
Have you used any break points to check your tables after each insert?
– Nathan_Sav
Nov 23 '18 at 12:49
|
show 3 more comments
0
active
oldest
votes
Your Answer
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%2f53445959%2fuploading-data-using-vba-ado-transaction%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53445959%2fuploading-data-using-vba-ado-transaction%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
SomeNumber is always 2
– Nathan_Sav
Nov 23 '18 at 11:46
I don't see how that could work with any back end when you don't increment
somenumberinside the loop so you're always inserting 3 as the newcountervalue.– Rory
Nov 23 '18 at 11:46
Yes but it should be incremented by
UPDATEin next loop (when i = 1)– Sphinx
Nov 23 '18 at 11:49
Is the SQL definately getting passed?
cnn.CommitTrans– Nathan_Sav
Nov 23 '18 at 12:23
1
Have you used any break points to check your tables after each insert?
– Nathan_Sav
Nov 23 '18 at 12:49