Insert data to database using Excel VBA is not working
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
2
I have a macro that will upload data from multiple workbooks to the database. Table will differs based on what type of value/data being inserted. I have there tables. I can insert value in the first table just fine, but when it comes to second table, there's no value inserted, and im using the the exact same code as the first one but with different variables.
Set conn = CreateObject("ADODB.Connection")
Dim connection, insertData As String
connection = "Provider=; Database= Database; Integrated Security=SSPI;"
'open connection
conn.Open connection
insertData = "INSERT INTO Table2 ([Name], [AScore], [BScore], [CScore], [DScore], [EScore], [FScore]) " & _
" VALUES ('" & _
SummWb.Sheets("Sheet 2").Cells(13, "I").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(11, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(6, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(7, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(3, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(9, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(10, "J").Value & "')"
conn.Execute insertData
conn.close
I've tried to see if my code is wrong, and I also tried to drop the table and create a new table, and also using a new workbook to test the code, but the result is still the same. Can you please help to see what is wrong with my code? I am using SQL Server Management Studio.
microsoft-excel vba sql-server sql
asked Mar 4 at 7:22
user10236952user10236952
132
|
show 1 more comment
2
I have a macro that will upload data from multiple workbooks to the database. Table will differs based on what type of value/data being inserted. I have there tables. I can insert value in the first table just fine, but when it comes to second table, there's no value inserted, and im using the the exact same code as the first one but with different variables.
Set conn = CreateObject("ADODB.Connection")
Dim connection, insertData As String
connection = "Provider=; Database= Database; Integrated Security=SSPI;"
'open connection
conn.Open connection
insertData = "INSERT INTO Table2 ([Name], [AScore], [BScore], [CScore], [DScore], [EScore], [FScore]) " & _
" VALUES ('" & _
SummWb.Sheets("Sheet 2").Cells(13, "I").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(11, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(6, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(7, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(3, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(9, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(10, "J").Value & "')"
conn.Execute insertData
conn.close
I've tried to see if my code is wrong, and I also tried to drop the table and create a new table, and also using a new workbook to test the code, but the result is still the same. Can you please help to see what is wrong with my code? I am using SQL Server Management Studio.
microsoft-excel vba sql-server sql
asked Mar 4 at 7:22
user10236952user10236952
132
What version of SQL server.. Is the code about the second table?
– Dave
Mar 4 at 7:45
Hi @Dave its 2010 version and yes the code is for second table
– user10236952
Mar 4 at 7:55
I don't think we could solve it for you, you need to debug your code and find what's the issue. try to step your code (F8) until the line "insertData = ..." then insert your expression prededed by a?into watch window (e.g.? "INSERT INTO Table2...) and press Enter. That will show you how VBA compose the query string. Check whether it's correct. Also try to run it in SQL Server Management Studio. I suppose there will be issue related to data types (e.g. you put a number within's when it shouldn't be.
– Máté Juhász
Mar 4 at 8:12
1
Hi @MátéJuhász after a lot of try and error, the last resort for me was to type again, everything from the beginning, and somehow, it miraculously works. Thanks for your tips! Will use it in the future.
– user10236952
Mar 4 at 8:15
1
great it's solved! You can compare your current code with the one in the question, so you might learn what was the issue and will be easier to avoid it in the future. (You can even post the solution as an answer).
– Máté Juhász
Mar 4 at 8:18
|
show 1 more comment
2
2
2
I have a macro that will upload data from multiple workbooks to the database. Table will differs based on what type of value/data being inserted. I have there tables. I can insert value in the first table just fine, but when it comes to second table, there's no value inserted, and im using the the exact same code as the first one but with different variables.
Set conn = CreateObject("ADODB.Connection")
Dim connection, insertData As String
connection = "Provider=; Database= Database; Integrated Security=SSPI;"
'open connection
conn.Open connection
insertData = "INSERT INTO Table2 ([Name], [AScore], [BScore], [CScore], [DScore], [EScore], [FScore]) " & _
" VALUES ('" & _
SummWb.Sheets("Sheet 2").Cells(13, "I").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(11, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(6, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(7, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(3, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(9, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(10, "J").Value & "')"
conn.Execute insertData
conn.close
I've tried to see if my code is wrong, and I also tried to drop the table and create a new table, and also using a new workbook to test the code, but the result is still the same. Can you please help to see what is wrong with my code? I am using SQL Server Management Studio.
microsoft-excel vba sql-server sql
asked Mar 4 at 7:22
user10236952user10236952
132
I have a macro that will upload data from multiple workbooks to the database. Table will differs based on what type of value/data being inserted. I have there tables. I can insert value in the first table just fine, but when it comes to second table, there's no value inserted, and im using the the exact same code as the first one but with different variables.
Set conn = CreateObject("ADODB.Connection")
Dim connection, insertData As String
connection = "Provider=; Database= Database; Integrated Security=SSPI;"
'open connection
conn.Open connection
insertData = "INSERT INTO Table2 ([Name], [AScore], [BScore], [CScore], [DScore], [EScore], [FScore]) " & _
" VALUES ('" & _
SummWb.Sheets("Sheet 2").Cells(13, "I").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(11, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(6, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(7, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(3, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(9, "J").Value & "' , '" & _
SummWb.Sheets("Sheet 2").Cells(10, "J").Value & "')"
conn.Execute insertData
conn.close
I've tried to see if my code is wrong, and I also tried to drop the table and create a new table, and also using a new workbook to test the code, but the result is still the same. Can you please help to see what is wrong with my code? I am using SQL Server Management Studio.
microsoft-excel vba sql-server sql
microsoft-excel vba sql-server sql
asked Mar 4 at 7:22
user10236952user10236952
132
asked Mar 4 at 7:22
user10236952user10236952
132
asked Mar 4 at 7:22
user10236952user10236952
132
asked Mar 4 at 7:22
user10236952user10236952
132
asked Mar 4 at 7:22
user10236952user10236952
132
132
What version of SQL server.. Is the code about the second table?
– Dave
Mar 4 at 7:45
Hi @Dave its 2010 version and yes the code is for second table
– user10236952
Mar 4 at 7:55
I don't think we could solve it for you, you need to debug your code and find what's the issue. try to step your code (F8) until the line "insertData = ..." then insert your expression prededed by a?into watch window (e.g.? "INSERT INTO Table2...) and press Enter. That will show you how VBA compose the query string. Check whether it's correct. Also try to run it in SQL Server Management Studio. I suppose there will be issue related to data types (e.g. you put a number within's when it shouldn't be.
– Máté Juhász
Mar 4 at 8:12
1
Hi @MátéJuhász after a lot of try and error, the last resort for me was to type again, everything from the beginning, and somehow, it miraculously works. Thanks for your tips! Will use it in the future.
– user10236952
Mar 4 at 8:15
1
great it's solved! You can compare your current code with the one in the question, so you might learn what was the issue and will be easier to avoid it in the future. (You can even post the solution as an answer).
– Máté Juhász
Mar 4 at 8:18
|
show 1 more comment
What version of SQL server.. Is the code about the second table?
– Dave
Mar 4 at 7:45
Hi @Dave its 2010 version and yes the code is for second table
– user10236952
Mar 4 at 7:55
I don't think we could solve it for you, you need to debug your code and find what's the issue. try to step your code (F8) until the line "insertData = ..." then insert your expression prededed by a?into watch window (e.g.? "INSERT INTO Table2...) and press Enter. That will show you how VBA compose the query string. Check whether it's correct. Also try to run it in SQL Server Management Studio. I suppose there will be issue related to data types (e.g. you put a number within's when it shouldn't be.
– Máté Juhász
Mar 4 at 8:12
1
Hi @MátéJuhász after a lot of try and error, the last resort for me was to type again, everything from the beginning, and somehow, it miraculously works. Thanks for your tips! Will use it in the future.
– user10236952
Mar 4 at 8:15
1
great it's solved! You can compare your current code with the one in the question, so you might learn what was the issue and will be easier to avoid it in the future. (You can even post the solution as an answer).
– Máté Juhász
Mar 4 at 8:18
What version of SQL server.. Is the code about the second table?
– Dave
Mar 4 at 7:45
What version of SQL server.. Is the code about the second table?
– Dave
Mar 4 at 7:45
Hi @Dave its 2010 version and yes the code is for second table
– user10236952
Mar 4 at 7:55
Hi @Dave its 2010 version and yes the code is for second table
– user10236952
Mar 4 at 7:55
I don't think we could solve it for you, you need to debug your code and find what's the issue. try to step your code (F8) until the line "insertData = ..." then insert your expression prededed by a
? into watch window (e.g. ? "INSERT INTO Table2...) and press Enter. That will show you how VBA compose the query string. Check whether it's correct. Also try to run it in SQL Server Management Studio. I suppose there will be issue related to data types (e.g. you put a number within 's when it shouldn't be.– Máté Juhász
Mar 4 at 8:12
I don't think we could solve it for you, you need to debug your code and find what's the issue. try to step your code (F8) until the line "insertData = ..." then insert your expression prededed by a
? into watch window (e.g. ? "INSERT INTO Table2...) and press Enter. That will show you how VBA compose the query string. Check whether it's correct. Also try to run it in SQL Server Management Studio. I suppose there will be issue related to data types (e.g. you put a number within 's when it shouldn't be.– Máté Juhász
Mar 4 at 8:12
1
1
Hi @MátéJuhász after a lot of try and error, the last resort for me was to type again, everything from the beginning, and somehow, it miraculously works. Thanks for your tips! Will use it in the future.
– user10236952
Mar 4 at 8:15
Hi @MátéJuhász after a lot of try and error, the last resort for me was to type again, everything from the beginning, and somehow, it miraculously works. Thanks for your tips! Will use it in the future.
– user10236952
Mar 4 at 8:15
1
1
great it's solved! You can compare your current code with the one in the question, so you might learn what was the issue and will be easier to avoid it in the future. (You can even post the solution as an answer).
– Máté Juhász
Mar 4 at 8:18
great it's solved! You can compare your current code with the one in the question, so you might learn what was the issue and will be easier to avoid it in the future. (You can even post the solution as an answer).
– Máté Juhász
Mar 4 at 8:18
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1411058%2finsert-data-to-database-using-excel-vba-is-not-working%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
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1411058%2finsert-data-to-database-using-excel-vba-is-not-working%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
What version of SQL server.. Is the code about the second table?
– Dave
Mar 4 at 7:45
Hi @Dave its 2010 version and yes the code is for second table
– user10236952
Mar 4 at 7:55
I don't think we could solve it for you, you need to debug your code and find what's the issue. try to step your code (F8) until the line "insertData = ..." then insert your expression prededed by a
?into watch window (e.g.? "INSERT INTO Table2...) and press Enter. That will show you how VBA compose the query string. Check whether it's correct. Also try to run it in SQL Server Management Studio. I suppose there will be issue related to data types (e.g. you put a number within's when it shouldn't be.– Máté Juhász
Mar 4 at 8:12
1
Hi @MátéJuhász after a lot of try and error, the last resort for me was to type again, everything from the beginning, and somehow, it miraculously works. Thanks for your tips! Will use it in the future.
– user10236952
Mar 4 at 8:15
1
great it's solved! You can compare your current code with the one in the question, so you might learn what was the issue and will be easier to avoid it in the future. (You can even post the solution as an answer).
– Máté Juhász
Mar 4 at 8:18