Mixing
Increase speed of multiple inserts into Access DB from VBA Dictionary
0
I am attempting to take a VBA Dictionary and either:
- Insert a new row into the database if it does not exist
- Update the row if it does
While my current code works for this, it runs extremely slowly for the thousands of records I may need to update, and other solutions I have found on this site do not really achieve what I am after. Could anyone help me achieve this? My code so far is below:
Sub UpdateDatabase(dict As Object)
Dim Conn As Object, StrSQL As String, Rs As Object
Dim hmm As ADODB.Recordset
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Microsoft.ACE.OLEDB.12.0"
Conn.Open "C:XXXXcfrv2.accdb"
dictCount = dict.Count
counter = 0
For Each varKey In dict.Keys()
Application.StatusBar = Str(counter) & "/" & Str(dictCount)
counter = counter + 1
StrSQL = "SELECT * FROM `All SAMs Backlog` WHERE [LOCID] = '" & varKey & "'"
Set hmm = Conn.Execute(StrSQL)
If hmm.BOF And hmm.EOF Then
StrSQL = "INSERT INTO `ALL SAMs Backlog` ([SAM], [LOCID], [RTC Date], [CFR Status], [CFR Completed Date], [CFR On Hold Reason], [MDU], [ICWB Issue], [Obsolete]) VALUES (dict.Item(varKey)(0), '" & varKey & "', '20/12/2018', '" & dict.Item(varKey)(1) & "', '02/01/2019', '" & dict.Item(varKey)(2) & "' , '" & dict.Item(varKey)(3) & "' , '" &dict.Item(varKey)(4) & "' , '" & dict.Item(varKey)(5) & "')"
Conn.Execute (StrSQL)
Else
'Update the LOC in the table
StrSQL = "UPDATE `All SAMs Backlog` SET ([CFR Status] = '" & dict.Item(varKey)(1) & "', [CFR On Hold Reason] = '" & dict.Item(varKey)(2) & "', [MDU] = '" & dict.Item(varKey)(3) & "', [ICWB Issue] = '" & dict.Item(varKey)(4) & "', [Obsolete] = '" & dict.Item(varKey)(5) & "')"
Conn.Execute (StrSQL)
End If
Next
Conn.Close
End Sub
Any help is appreciated.
sql vba ms-access adodb
asked Jan 2 at 5:58
PL200PL200
548212
add a comment |
0
I am attempting to take a VBA Dictionary and either:
- Insert a new row into the database if it does not exist
- Update the row if it does
While my current code works for this, it runs extremely slowly for the thousands of records I may need to update, and other solutions I have found on this site do not really achieve what I am after. Could anyone help me achieve this? My code so far is below:
Sub UpdateDatabase(dict As Object)
Dim Conn As Object, StrSQL As String, Rs As Object
Dim hmm As ADODB.Recordset
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Microsoft.ACE.OLEDB.12.0"
Conn.Open "C:XXXXcfrv2.accdb"
dictCount = dict.Count
counter = 0
For Each varKey In dict.Keys()
Application.StatusBar = Str(counter) & "/" & Str(dictCount)
counter = counter + 1
StrSQL = "SELECT * FROM `All SAMs Backlog` WHERE [LOCID] = '" & varKey & "'"
Set hmm = Conn.Execute(StrSQL)
If hmm.BOF And hmm.EOF Then
StrSQL = "INSERT INTO `ALL SAMs Backlog` ([SAM], [LOCID], [RTC Date], [CFR Status], [CFR Completed Date], [CFR On Hold Reason], [MDU], [ICWB Issue], [Obsolete]) VALUES (dict.Item(varKey)(0), '" & varKey & "', '20/12/2018', '" & dict.Item(varKey)(1) & "', '02/01/2019', '" & dict.Item(varKey)(2) & "' , '" & dict.Item(varKey)(3) & "' , '" &dict.Item(varKey)(4) & "' , '" & dict.Item(varKey)(5) & "')"
Conn.Execute (StrSQL)
Else
'Update the LOC in the table
StrSQL = "UPDATE `All SAMs Backlog` SET ([CFR Status] = '" & dict.Item(varKey)(1) & "', [CFR On Hold Reason] = '" & dict.Item(varKey)(2) & "', [MDU] = '" & dict.Item(varKey)(3) & "', [ICWB Issue] = '" & dict.Item(varKey)(4) & "', [Obsolete] = '" & dict.Item(varKey)(5) & "')"
Conn.Execute (StrSQL)
End If
Next
Conn.Close
End Sub
Any help is appreciated.
sql vba ms-access adodb
asked Jan 2 at 5:58
PL200PL200
548212
Depending on how many inserts vs updates you have, you could first run the update and check on the "records affected" value, then if that's zero run the insert. stackoverflow.com/questions/12676747/…
– Tim Williams
Jan 2 at 6:35
1
I am surprised code works. Should use[ ]to delimit table and field names, not apostrophes:[All SAMs Backlog]. If the LocID is common key, can't you join tables?
– June7
Jan 2 at 7:11
Possible duplicate of Upserting in MS-access
– June7
Jan 2 at 7:11
add a comment |
0
0
0
I am attempting to take a VBA Dictionary and either:
- Insert a new row into the database if it does not exist
- Update the row if it does
While my current code works for this, it runs extremely slowly for the thousands of records I may need to update, and other solutions I have found on this site do not really achieve what I am after. Could anyone help me achieve this? My code so far is below:
Sub UpdateDatabase(dict As Object)
Dim Conn As Object, StrSQL As String, Rs As Object
Dim hmm As ADODB.Recordset
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Microsoft.ACE.OLEDB.12.0"
Conn.Open "C:XXXXcfrv2.accdb"
dictCount = dict.Count
counter = 0
For Each varKey In dict.Keys()
Application.StatusBar = Str(counter) & "/" & Str(dictCount)
counter = counter + 1
StrSQL = "SELECT * FROM `All SAMs Backlog` WHERE [LOCID] = '" & varKey & "'"
Set hmm = Conn.Execute(StrSQL)
If hmm.BOF And hmm.EOF Then
StrSQL = "INSERT INTO `ALL SAMs Backlog` ([SAM], [LOCID], [RTC Date], [CFR Status], [CFR Completed Date], [CFR On Hold Reason], [MDU], [ICWB Issue], [Obsolete]) VALUES (dict.Item(varKey)(0), '" & varKey & "', '20/12/2018', '" & dict.Item(varKey)(1) & "', '02/01/2019', '" & dict.Item(varKey)(2) & "' , '" & dict.Item(varKey)(3) & "' , '" &dict.Item(varKey)(4) & "' , '" & dict.Item(varKey)(5) & "')"
Conn.Execute (StrSQL)
Else
'Update the LOC in the table
StrSQL = "UPDATE `All SAMs Backlog` SET ([CFR Status] = '" & dict.Item(varKey)(1) & "', [CFR On Hold Reason] = '" & dict.Item(varKey)(2) & "', [MDU] = '" & dict.Item(varKey)(3) & "', [ICWB Issue] = '" & dict.Item(varKey)(4) & "', [Obsolete] = '" & dict.Item(varKey)(5) & "')"
Conn.Execute (StrSQL)
End If
Next
Conn.Close
End Sub
Any help is appreciated.
sql vba ms-access adodb
asked Jan 2 at 5:58
PL200PL200
548212
I am attempting to take a VBA Dictionary and either:
- Insert a new row into the database if it does not exist
- Update the row if it does
While my current code works for this, it runs extremely slowly for the thousands of records I may need to update, and other solutions I have found on this site do not really achieve what I am after. Could anyone help me achieve this? My code so far is below:
Sub UpdateDatabase(dict As Object)
Dim Conn As Object, StrSQL As String, Rs As Object
Dim hmm As ADODB.Recordset
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Microsoft.ACE.OLEDB.12.0"
Conn.Open "C:XXXXcfrv2.accdb"
dictCount = dict.Count
counter = 0
For Each varKey In dict.Keys()
Application.StatusBar = Str(counter) & "/" & Str(dictCount)
counter = counter + 1
StrSQL = "SELECT * FROM `All SAMs Backlog` WHERE [LOCID] = '" & varKey & "'"
Set hmm = Conn.Execute(StrSQL)
If hmm.BOF And hmm.EOF Then
StrSQL = "INSERT INTO `ALL SAMs Backlog` ([SAM], [LOCID], [RTC Date], [CFR Status], [CFR Completed Date], [CFR On Hold Reason], [MDU], [ICWB Issue], [Obsolete]) VALUES (dict.Item(varKey)(0), '" & varKey & "', '20/12/2018', '" & dict.Item(varKey)(1) & "', '02/01/2019', '" & dict.Item(varKey)(2) & "' , '" & dict.Item(varKey)(3) & "' , '" &dict.Item(varKey)(4) & "' , '" & dict.Item(varKey)(5) & "')"
Conn.Execute (StrSQL)
Else
'Update the LOC in the table
StrSQL = "UPDATE `All SAMs Backlog` SET ([CFR Status] = '" & dict.Item(varKey)(1) & "', [CFR On Hold Reason] = '" & dict.Item(varKey)(2) & "', [MDU] = '" & dict.Item(varKey)(3) & "', [ICWB Issue] = '" & dict.Item(varKey)(4) & "', [Obsolete] = '" & dict.Item(varKey)(5) & "')"
Conn.Execute (StrSQL)
End If
Next
Conn.Close
End Sub
Any help is appreciated.
sql vba ms-access adodb
sql vba ms-access adodb
asked Jan 2 at 5:58
PL200PL200
548212
asked Jan 2 at 5:58
PL200PL200
548212
asked Jan 2 at 5:58
PL200PL200
548212
asked Jan 2 at 5:58
PL200PL200
548212
asked Jan 2 at 5:58
PL200PL200
548212
548212
Depending on how many inserts vs updates you have, you could first run the update and check on the "records affected" value, then if that's zero run the insert. stackoverflow.com/questions/12676747/…
– Tim Williams
Jan 2 at 6:35
1
I am surprised code works. Should use[ ]to delimit table and field names, not apostrophes:[All SAMs Backlog]. If the LocID is common key, can't you join tables?
– June7
Jan 2 at 7:11
Possible duplicate of Upserting in MS-access
– June7
Jan 2 at 7:11
add a comment |
Depending on how many inserts vs updates you have, you could first run the update and check on the "records affected" value, then if that's zero run the insert. stackoverflow.com/questions/12676747/…
– Tim Williams
Jan 2 at 6:35
1
I am surprised code works. Should use[ ]to delimit table and field names, not apostrophes:[All SAMs Backlog]. If the LocID is common key, can't you join tables?
– June7
Jan 2 at 7:11
Possible duplicate of Upserting in MS-access
– June7
Jan 2 at 7:11
Depending on how many inserts vs updates you have, you could first run the update and check on the "records affected" value, then if that's zero run the insert. stackoverflow.com/questions/12676747/…
– Tim Williams
Jan 2 at 6:35
Depending on how many inserts vs updates you have, you could first run the update and check on the "records affected" value, then if that's zero run the insert. stackoverflow.com/questions/12676747/…
– Tim Williams
Jan 2 at 6:35
1
1
I am surprised code works. Should use
[ ] to delimit table and field names, not apostrophes: [All SAMs Backlog]. If the LocID is common key, can't you join tables?– June7
Jan 2 at 7:11
I am surprised code works. Should use
[ ] to delimit table and field names, not apostrophes: [All SAMs Backlog]. If the LocID is common key, can't you join tables?– June7
Jan 2 at 7:11
Possible duplicate of Upserting in MS-access
– June7
Jan 2 at 7:11
Possible duplicate of Upserting in MS-access
– June7
Jan 2 at 7:11
add a comment |
1 Answer
1
active
oldest
votes
1
Either:
Write the content of the dictionary to a temp table, then run a query as described here:
Update or insert data in table
or:
Open [All SAMs Backlog] as a recordset, loop the dictionary to add or edit records as needed, then close the recordset.
answered Jan 2 at 7:22
GustavGustav
30.4k52037
add a comment |
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
});
}
});
draft saved
draft discarded
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%2f54001872%2fincrease-speed-of-multiple-inserts-into-access-db-from-vba-dictionary%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
1
Either:
Write the content of the dictionary to a temp table, then run a query as described here:
Update or insert data in table
or:
Open [All SAMs Backlog] as a recordset, loop the dictionary to add or edit records as needed, then close the recordset.
answered Jan 2 at 7:22
GustavGustav
30.4k52037
add a comment |
1
Either:
Write the content of the dictionary to a temp table, then run a query as described here:
Update or insert data in table
or:
Open [All SAMs Backlog] as a recordset, loop the dictionary to add or edit records as needed, then close the recordset.
answered Jan 2 at 7:22
GustavGustav
30.4k52037
add a comment |
1
1
1
Either:
Write the content of the dictionary to a temp table, then run a query as described here:
Update or insert data in table
or:
Open [All SAMs Backlog] as a recordset, loop the dictionary to add or edit records as needed, then close the recordset.
answered Jan 2 at 7:22
GustavGustav
30.4k52037
Either:
Write the content of the dictionary to a temp table, then run a query as described here:
Update or insert data in table
or:
Open [All SAMs Backlog] as a recordset, loop the dictionary to add or edit records as needed, then close the recordset.
answered Jan 2 at 7:22
GustavGustav
30.4k52037
answered Jan 2 at 7:22
GustavGustav
30.4k52037
answered Jan 2 at 7:22
GustavGustav
30.4k52037
answered Jan 2 at 7:22
GustavGustav
30.4k52037
30.4k52037
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f54001872%2fincrease-speed-of-multiple-inserts-into-access-db-from-vba-dictionary%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
Depending on how many inserts vs updates you have, you could first run the update and check on the "records affected" value, then if that's zero run the insert. stackoverflow.com/questions/12676747/…
– Tim Williams
Jan 2 at 6:35
1
I am surprised code works. Should use
[ ]to delimit table and field names, not apostrophes:[All SAMs Backlog]. If the LocID is common key, can't you join tables?– June7
Jan 2 at 7:11
Possible duplicate of Upserting in MS-access
– June7
Jan 2 at 7:11