Mixing
Connecting Excel VBA to sql server using ODBC Connections and Objects
I am new to coding, i am recently working on a Stock Control Program using Excel and VBA, at first i can run and get results on my designer, but the more buttons and labels that i add on it becomes a problem to run!
- Error 1
I Keep getting an automation error.
I understood that the error was related to database connection, so i proceeded to connecting to the database. using the code bellow:
Sub DbConnection()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
strConn = "Driver={SQL Server}; Server=servername; Database=Dbname; VID=username; PWD=password"
cn.Open strConn
cn.Close
Set cn = Nothing
MsgBox "Connected"
End Sub
I used the Microsoft ActiveX Data Object 6.1 library.
but i realized that it is not working and was recommended to use ODBC Connection, and now i am stuck, Can anyone help me on this?How do i use this connection for my ODBC Connection?
Anything I am doing Wrong? Which Reference Object do i have to use?
PS: I am using Ms Excel 2016 and SSMS 2017. Thanks
sql-server excel vba odbc edited Nov 21 '18 at 16:34
Cindy Meister
15.3k102235
asked Nov 21 '18 at 15:55
Teodorico MazivialaTeodorico Maziviala
11
add a comment |
I am new to coding, i am recently working on a Stock Control Program using Excel and VBA, at first i can run and get results on my designer, but the more buttons and labels that i add on it becomes a problem to run!
- Error 1
I Keep getting an automation error.
I understood that the error was related to database connection, so i proceeded to connecting to the database. using the code bellow:
Sub DbConnection()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
strConn = "Driver={SQL Server}; Server=servername; Database=Dbname; VID=username; PWD=password"
cn.Open strConn
cn.Close
Set cn = Nothing
MsgBox "Connected"
End Sub
I used the Microsoft ActiveX Data Object 6.1 library.
but i realized that it is not working and was recommended to use ODBC Connection, and now i am stuck, Can anyone help me on this?How do i use this connection for my ODBC Connection?
Anything I am doing Wrong? Which Reference Object do i have to use?
PS: I am using Ms Excel 2016 and SSMS 2017. Thanks
sql-server excel vba odbc edited Nov 21 '18 at 16:34
Cindy Meister
15.3k102235
asked Nov 21 '18 at 15:55
Teodorico MazivialaTeodorico Maziviala
11
2
Microsoft ActiveX Data Object 6.1is fine to use for early binding ADODB objects. If this connection was working before - it is not likely causing the issue. You said you started getting errors when you added more controls. What code is running behind these "problem" controls?
– ArcherBird
Nov 21 '18 at 16:12
"You said you started getting errors when you added more controls. What code is running behind these "problem" controls?" Well, that is the problem, I have not yet used any code on! this is all happening with the designer userform!
– Teodorico Maziviala
Nov 22 '18 at 15:29
add a comment |
I am new to coding, i am recently working on a Stock Control Program using Excel and VBA, at first i can run and get results on my designer, but the more buttons and labels that i add on it becomes a problem to run!
- Error 1
I Keep getting an automation error.
I understood that the error was related to database connection, so i proceeded to connecting to the database. using the code bellow:
Sub DbConnection()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
strConn = "Driver={SQL Server}; Server=servername; Database=Dbname; VID=username; PWD=password"
cn.Open strConn
cn.Close
Set cn = Nothing
MsgBox "Connected"
End Sub
I used the Microsoft ActiveX Data Object 6.1 library.
but i realized that it is not working and was recommended to use ODBC Connection, and now i am stuck, Can anyone help me on this?How do i use this connection for my ODBC Connection?
Anything I am doing Wrong? Which Reference Object do i have to use?
PS: I am using Ms Excel 2016 and SSMS 2017. Thanks
sql-server excel vba odbc edited Nov 21 '18 at 16:34
Cindy Meister
15.3k102235
asked Nov 21 '18 at 15:55
Teodorico MazivialaTeodorico Maziviala
11
I am new to coding, i am recently working on a Stock Control Program using Excel and VBA, at first i can run and get results on my designer, but the more buttons and labels that i add on it becomes a problem to run!
- Error 1
I Keep getting an automation error.
I understood that the error was related to database connection, so i proceeded to connecting to the database. using the code bellow:
Sub DbConnection()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
strConn = "Driver={SQL Server}; Server=servername; Database=Dbname; VID=username; PWD=password"
cn.Open strConn
cn.Close
Set cn = Nothing
MsgBox "Connected"
End Sub
I used the Microsoft ActiveX Data Object 6.1 library.
but i realized that it is not working and was recommended to use ODBC Connection, and now i am stuck, Can anyone help me on this?How do i use this connection for my ODBC Connection?
Anything I am doing Wrong? Which Reference Object do i have to use?
PS: I am using Ms Excel 2016 and SSMS 2017. Thanks
sql-server excel vba odbc sql-server excel vba odbc edited Nov 21 '18 at 16:34
Cindy Meister
15.3k102235
asked Nov 21 '18 at 15:55
Teodorico MazivialaTeodorico Maziviala
11
edited Nov 21 '18 at 16:34
Cindy Meister
15.3k102235
asked Nov 21 '18 at 15:55
Teodorico MazivialaTeodorico Maziviala
11
edited Nov 21 '18 at 16:34
Cindy Meister
15.3k102235
edited Nov 21 '18 at 16:34
Cindy Meister
15.3k102235
edited Nov 21 '18 at 16:34
Cindy Meister
15.3k102235
15.3k102235
asked Nov 21 '18 at 15:55
Teodorico MazivialaTeodorico Maziviala
11
asked Nov 21 '18 at 15:55
Teodorico MazivialaTeodorico Maziviala
11
asked Nov 21 '18 at 15:55
Teodorico MazivialaTeodorico Maziviala
11
11
2
Microsoft ActiveX Data Object 6.1is fine to use for early binding ADODB objects. If this connection was working before - it is not likely causing the issue. You said you started getting errors when you added more controls. What code is running behind these "problem" controls?
– ArcherBird
Nov 21 '18 at 16:12
"You said you started getting errors when you added more controls. What code is running behind these "problem" controls?" Well, that is the problem, I have not yet used any code on! this is all happening with the designer userform!
– Teodorico Maziviala
Nov 22 '18 at 15:29
add a comment |
2
Microsoft ActiveX Data Object 6.1is fine to use for early binding ADODB objects. If this connection was working before - it is not likely causing the issue. You said you started getting errors when you added more controls. What code is running behind these "problem" controls?
– ArcherBird
Nov 21 '18 at 16:12
"You said you started getting errors when you added more controls. What code is running behind these "problem" controls?" Well, that is the problem, I have not yet used any code on! this is all happening with the designer userform!
– Teodorico Maziviala
Nov 22 '18 at 15:29
2
2
Microsoft ActiveX Data Object 6.1 is fine to use for early binding ADODB objects. If this connection was working before - it is not likely causing the issue. You said you started getting errors when you added more controls. What code is running behind these "problem" controls?– ArcherBird
Nov 21 '18 at 16:12
Microsoft ActiveX Data Object 6.1 is fine to use for early binding ADODB objects. If this connection was working before - it is not likely causing the issue. You said you started getting errors when you added more controls. What code is running behind these "problem" controls?– ArcherBird
Nov 21 '18 at 16:12
"You said you started getting errors when you added more controls. What code is running behind these "problem" controls?" Well, that is the problem, I have not yet used any code on! this is all happening with the designer userform!
– Teodorico Maziviala
Nov 22 '18 at 15:29
"You said you started getting errors when you added more controls. What code is running behind these "problem" controls?" Well, that is the problem, I have not yet used any code on! this is all happening with the designer userform!
– Teodorico Maziviala
Nov 22 '18 at 15:29
add a comment |
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%2f53415874%2fconnecting-excel-vba-to-sql-server-using-odbc-connections-and-objects%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%2f53415874%2fconnecting-excel-vba-to-sql-server-using-odbc-connections-and-objects%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
2
Microsoft ActiveX Data Object 6.1is fine to use for early binding ADODB objects. If this connection was working before - it is not likely causing the issue. You said you started getting errors when you added more controls. What code is running behind these "problem" controls?– ArcherBird
Nov 21 '18 at 16:12
"You said you started getting errors when you added more controls. What code is running behind these "problem" controls?" Well, that is the problem, I have not yet used any code on! this is all happening with the designer userform!
– Teodorico Maziviala
Nov 22 '18 at 15:29