MS Access DNSless connection to MS SQL Server userid/pass fail
I am connecting to a MS SQL server from a laptop with MS Access 2016. My intent is to insert some local data into the SQL server. I have a user account on the SQL server with the user type setting of, 'SQL user with login'. I use the connection string below:
connStr = "ODBC;Driver={ODBC Driver 13 for SQL Server};" & _
"Server=" & dbServer & ";" & _
"Database=" & dbName & ";" & _
"User=" & UID & ";" & _
"Password=" & PWD & ";"
Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = connStr
qdf.SQL = "INSERT INTO theTble (field1, field2) VALUES ('data1', 'data2');"
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError <--- this line opens the dilogue box
When the line runs to execute the query I get a dialogue box:
If i change the Login ID field contents to the login used in the connection string and add the password to the password field and click OK, the connection is made and the SQL insert query is executed properly.
I have tried a number of connection strings; so far this is the only one that will get me this close. I need this to run without having to manually type in the userID/Pass.
Also, a linked table is not going to work in my situation.
Thanks for looking.
sql-server ms-access-2016 asked Nov 21 '18 at 16:55
Chris OChris O
564414
add a comment |
I am connecting to a MS SQL server from a laptop with MS Access 2016. My intent is to insert some local data into the SQL server. I have a user account on the SQL server with the user type setting of, 'SQL user with login'. I use the connection string below:
connStr = "ODBC;Driver={ODBC Driver 13 for SQL Server};" & _
"Server=" & dbServer & ";" & _
"Database=" & dbName & ";" & _
"User=" & UID & ";" & _
"Password=" & PWD & ";"
Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = connStr
qdf.SQL = "INSERT INTO theTble (field1, field2) VALUES ('data1', 'data2');"
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError <--- this line opens the dilogue box
When the line runs to execute the query I get a dialogue box:
If i change the Login ID field contents to the login used in the connection string and add the password to the password field and click OK, the connection is made and the SQL insert query is executed properly.
I have tried a number of connection strings; so far this is the only one that will get me this close. I need this to run without having to manually type in the userID/Pass.
Also, a linked table is not going to work in my situation.
Thanks for looking.
sql-server ms-access-2016 asked Nov 21 '18 at 16:55
Chris OChris O
564414
Put a break point on the line of code where you set the connection string up and run it through the debugger. What are the values of UID and PWD when you debug?
– squillman
Nov 21 '18 at 17:06
The values are the sql auth userid and password.
– Chris O
Nov 21 '18 at 17:13
@squillman, I just added some more of my code and the fact that the qdf.Execute function caused the dialogue box to be opened, not the connect function
– Chris O
Nov 21 '18 at 17:36
Isn't the parameter for Password in the connection string supposed to be PWD? e.g. "User=" & UID & ";" & _ "PWD=" & PWD & ";"
– Allumearz
Nov 21 '18 at 18:34
Also, @Chris O: you are not calling the Connect function - you are setting the Connect property of the query to the connection string. The connect is actually being attempted at the point of query execution.
– Allumearz
Nov 21 '18 at 18:37
add a comment |
I am connecting to a MS SQL server from a laptop with MS Access 2016. My intent is to insert some local data into the SQL server. I have a user account on the SQL server with the user type setting of, 'SQL user with login'. I use the connection string below:
connStr = "ODBC;Driver={ODBC Driver 13 for SQL Server};" & _
"Server=" & dbServer & ";" & _
"Database=" & dbName & ";" & _
"User=" & UID & ";" & _
"Password=" & PWD & ";"
Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = connStr
qdf.SQL = "INSERT INTO theTble (field1, field2) VALUES ('data1', 'data2');"
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError <--- this line opens the dilogue box
When the line runs to execute the query I get a dialogue box:
If i change the Login ID field contents to the login used in the connection string and add the password to the password field and click OK, the connection is made and the SQL insert query is executed properly.
I have tried a number of connection strings; so far this is the only one that will get me this close. I need this to run without having to manually type in the userID/Pass.
Also, a linked table is not going to work in my situation.
Thanks for looking.
sql-server ms-access-2016 asked Nov 21 '18 at 16:55
Chris OChris O
564414
I am connecting to a MS SQL server from a laptop with MS Access 2016. My intent is to insert some local data into the SQL server. I have a user account on the SQL server with the user type setting of, 'SQL user with login'. I use the connection string below:
connStr = "ODBC;Driver={ODBC Driver 13 for SQL Server};" & _
"Server=" & dbServer & ";" & _
"Database=" & dbName & ";" & _
"User=" & UID & ";" & _
"Password=" & PWD & ";"
Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = connStr
qdf.SQL = "INSERT INTO theTble (field1, field2) VALUES ('data1', 'data2');"
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError <--- this line opens the dilogue box
When the line runs to execute the query I get a dialogue box:
If i change the Login ID field contents to the login used in the connection string and add the password to the password field and click OK, the connection is made and the SQL insert query is executed properly.
I have tried a number of connection strings; so far this is the only one that will get me this close. I need this to run without having to manually type in the userID/Pass.
Also, a linked table is not going to work in my situation.
Thanks for looking.
sql-server ms-access-2016 sql-server ms-access-2016 asked Nov 21 '18 at 16:55
Chris OChris O
564414
asked Nov 21 '18 at 16:55
Chris OChris O
564414
edited Nov 21 '18 at 17:34
Chris O
asked Nov 21 '18 at 16:55
Chris OChris O
564414
asked Nov 21 '18 at 16:55
Chris OChris O
564414
asked Nov 21 '18 at 16:55
Chris OChris O
564414
564414
Put a break point on the line of code where you set the connection string up and run it through the debugger. What are the values of UID and PWD when you debug?
– squillman
Nov 21 '18 at 17:06
The values are the sql auth userid and password.
– Chris O
Nov 21 '18 at 17:13
@squillman, I just added some more of my code and the fact that the qdf.Execute function caused the dialogue box to be opened, not the connect function
– Chris O
Nov 21 '18 at 17:36
Isn't the parameter for Password in the connection string supposed to be PWD? e.g. "User=" & UID & ";" & _ "PWD=" & PWD & ";"
– Allumearz
Nov 21 '18 at 18:34
Also, @Chris O: you are not calling the Connect function - you are setting the Connect property of the query to the connection string. The connect is actually being attempted at the point of query execution.
– Allumearz
Nov 21 '18 at 18:37
add a comment |
Put a break point on the line of code where you set the connection string up and run it through the debugger. What are the values of UID and PWD when you debug?
– squillman
Nov 21 '18 at 17:06
The values are the sql auth userid and password.
– Chris O
Nov 21 '18 at 17:13
@squillman, I just added some more of my code and the fact that the qdf.Execute function caused the dialogue box to be opened, not the connect function
– Chris O
Nov 21 '18 at 17:36
Isn't the parameter for Password in the connection string supposed to be PWD? e.g. "User=" & UID & ";" & _ "PWD=" & PWD & ";"
– Allumearz
Nov 21 '18 at 18:34
Also, @Chris O: you are not calling the Connect function - you are setting the Connect property of the query to the connection string. The connect is actually being attempted at the point of query execution.
– Allumearz
Nov 21 '18 at 18:37
Put a break point on the line of code where you set the connection string up and run it through the debugger. What are the values of UID and PWD when you debug?
– squillman
Nov 21 '18 at 17:06
Put a break point on the line of code where you set the connection string up and run it through the debugger. What are the values of UID and PWD when you debug?
– squillman
Nov 21 '18 at 17:06
The values are the sql auth userid and password.
– Chris O
Nov 21 '18 at 17:13
The values are the sql auth userid and password.
– Chris O
Nov 21 '18 at 17:13
@squillman, I just added some more of my code and the fact that the qdf.Execute function caused the dialogue box to be opened, not the connect function
– Chris O
Nov 21 '18 at 17:36
@squillman, I just added some more of my code and the fact that the qdf.Execute function caused the dialogue box to be opened, not the connect function
– Chris O
Nov 21 '18 at 17:36
Isn't the parameter for Password in the connection string supposed to be PWD? e.g. "User=" & UID & ";" & _ "PWD=" & PWD & ";"
– Allumearz
Nov 21 '18 at 18:34
Isn't the parameter for Password in the connection string supposed to be PWD? e.g. "User=" & UID & ";" & _ "PWD=" & PWD & ";"
– Allumearz
Nov 21 '18 at 18:34
Also, @Chris O: you are not calling the Connect function - you are setting the Connect property of the query to the connection string. The connect is actually being attempted at the point of query execution.
– Allumearz
Nov 21 '18 at 18:37
Also, @Chris O: you are not calling the Connect function - you are setting the Connect property of the query to the connection string. The connect is actually being attempted at the point of query execution.
– Allumearz
Nov 21 '18 at 18:37
add a comment |
1 Answer
1
active
oldest
votes
Following on from my comments, I thought I'd submit an actual answer.
The dialog box appears to have picked up the data from the connection string with the exception of the password. That leads me to believe that the Password parameter in the connection string is incorrect. I'm fairly sure that for ODBC connection strings, the parameter for password should be PWD={password}. (As a side note, I'm fairly sure the User should be UID={username} too, but the dialog seems to have picked your username up, so presumably User={username} is a suitable alternative.
Here's a Microsoft Docs Link - scroll down to the connection string format... it shows UID={username};PWD={password} in the connection string.
Failing that, does your SQL user have INSERT permission on to the table?
answered Nov 21 '18 at 18:49
AllumearzAllumearz
1896
The UID and PWD as recommended did work. The link to MS docs showed removing the 'ODBC;' from the beginning of the string but that made it fail. Put it back in and used UID and PWD names and all good now.
– Chris O
Nov 21 '18 at 23:30
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
});
}
});
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%2f53417038%2fms-access-dnsless-connection-to-ms-sql-server-userid-pass-fail%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
Following on from my comments, I thought I'd submit an actual answer.
The dialog box appears to have picked up the data from the connection string with the exception of the password. That leads me to believe that the Password parameter in the connection string is incorrect. I'm fairly sure that for ODBC connection strings, the parameter for password should be PWD={password}. (As a side note, I'm fairly sure the User should be UID={username} too, but the dialog seems to have picked your username up, so presumably User={username} is a suitable alternative.
Here's a Microsoft Docs Link - scroll down to the connection string format... it shows UID={username};PWD={password} in the connection string.
Failing that, does your SQL user have INSERT permission on to the table?
answered Nov 21 '18 at 18:49
AllumearzAllumearz
1896
The UID and PWD as recommended did work. The link to MS docs showed removing the 'ODBC;' from the beginning of the string but that made it fail. Put it back in and used UID and PWD names and all good now.
– Chris O
Nov 21 '18 at 23:30
add a comment |
Following on from my comments, I thought I'd submit an actual answer.
The dialog box appears to have picked up the data from the connection string with the exception of the password. That leads me to believe that the Password parameter in the connection string is incorrect. I'm fairly sure that for ODBC connection strings, the parameter for password should be PWD={password}. (As a side note, I'm fairly sure the User should be UID={username} too, but the dialog seems to have picked your username up, so presumably User={username} is a suitable alternative.
Here's a Microsoft Docs Link - scroll down to the connection string format... it shows UID={username};PWD={password} in the connection string.
Failing that, does your SQL user have INSERT permission on to the table?
answered Nov 21 '18 at 18:49
AllumearzAllumearz
1896
The UID and PWD as recommended did work. The link to MS docs showed removing the 'ODBC;' from the beginning of the string but that made it fail. Put it back in and used UID and PWD names and all good now.
– Chris O
Nov 21 '18 at 23:30
add a comment |
Following on from my comments, I thought I'd submit an actual answer.
The dialog box appears to have picked up the data from the connection string with the exception of the password. That leads me to believe that the Password parameter in the connection string is incorrect. I'm fairly sure that for ODBC connection strings, the parameter for password should be PWD={password}. (As a side note, I'm fairly sure the User should be UID={username} too, but the dialog seems to have picked your username up, so presumably User={username} is a suitable alternative.
Here's a Microsoft Docs Link - scroll down to the connection string format... it shows UID={username};PWD={password} in the connection string.
Failing that, does your SQL user have INSERT permission on to the table?
answered Nov 21 '18 at 18:49
AllumearzAllumearz
1896
Following on from my comments, I thought I'd submit an actual answer.
The dialog box appears to have picked up the data from the connection string with the exception of the password. That leads me to believe that the Password parameter in the connection string is incorrect. I'm fairly sure that for ODBC connection strings, the parameter for password should be PWD={password}. (As a side note, I'm fairly sure the User should be UID={username} too, but the dialog seems to have picked your username up, so presumably User={username} is a suitable alternative.
Here's a Microsoft Docs Link - scroll down to the connection string format... it shows UID={username};PWD={password} in the connection string.
Failing that, does your SQL user have INSERT permission on to the table?
answered Nov 21 '18 at 18:49
AllumearzAllumearz
1896
answered Nov 21 '18 at 18:49
AllumearzAllumearz
1896
answered Nov 21 '18 at 18:49
AllumearzAllumearz
1896
answered Nov 21 '18 at 18:49
AllumearzAllumearz
1896
1896
The UID and PWD as recommended did work. The link to MS docs showed removing the 'ODBC;' from the beginning of the string but that made it fail. Put it back in and used UID and PWD names and all good now.
– Chris O
Nov 21 '18 at 23:30
add a comment |
The UID and PWD as recommended did work. The link to MS docs showed removing the 'ODBC;' from the beginning of the string but that made it fail. Put it back in and used UID and PWD names and all good now.
– Chris O
Nov 21 '18 at 23:30
The UID and PWD as recommended did work. The link to MS docs showed removing the 'ODBC;' from the beginning of the string but that made it fail. Put it back in and used UID and PWD names and all good now.
– Chris O
Nov 21 '18 at 23:30
The UID and PWD as recommended did work. The link to MS docs showed removing the 'ODBC;' from the beginning of the string but that made it fail. Put it back in and used UID and PWD names and all good now.
– Chris O
Nov 21 '18 at 23:30
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%2f53417038%2fms-access-dnsless-connection-to-ms-sql-server-userid-pass-fail%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
Put a break point on the line of code where you set the connection string up and run it through the debugger. What are the values of UID and PWD when you debug?
– squillman
Nov 21 '18 at 17:06
The values are the sql auth userid and password.
– Chris O
Nov 21 '18 at 17:13
@squillman, I just added some more of my code and the fact that the qdf.Execute function caused the dialogue box to be opened, not the connect function
– Chris O
Nov 21 '18 at 17:36
Isn't the parameter for Password in the connection string supposed to be PWD? e.g. "User=" & UID & ";" & _ "PWD=" & PWD & ";"
– Allumearz
Nov 21 '18 at 18:34
Also, @Chris O: you are not calling the Connect function - you are setting the Connect property of the query to the connection string. The connect is actually being attempted at the point of query execution.
– Allumearz
Nov 21 '18 at 18:37