469,091 Members | 1,154 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,091 developers. It's quick & easy.

Connection string to access SQL Server stored procedure not working

patjones
931 Expert 512MB
Hi,

This may be a SQL Server forum question, but I thought I might try here first. I'm attempting to execute a stored procedure in SQL Server from Access, using VBA. My pertinent code is as follows:

Expand|Select|Wrap|Line Numbers
  1. Dim qrySP As QueryDef
  2. Set qrySP = CurrentDb.CreateQueryDef("SaveGrant")
  3.  
  4. With qrySP
  5.  
  6.     .Connect = "ODBC; DATABASE = XXX; UID = XXX; PWD = XXX; DSN = XXX"
  7.     .ReturnsRecords = False
  8.     .SQL = "EXEC dbo.sproc_SaveGrant " & Nz(DMax("fldGrantID", "dbo_tblPlanning"), 0) + 1 & ",'" & Me.txtProjectName & "'," & CInt(Me.txtYearIssued) & "," & Me.cboGrantSourceType
  9.     .Execute
  10.  
  11.     .Close
  12.  
  13. End With

I'm using 'XXX' here to block out sensitive system information.

This code runs fine, but the problem is that even though I'm specifying the DSN in the connection string, a box pops up back in the Access window asking for it (see my attachment for the screen shot). When I enter the DSN as I have it in the connection string, all goes well and my record is added to the table in SQL Server (the stored procedure is an INSERT with some parameters).

Any idea how I can prevent that box from popping up? Thanks.

Pat
Attached Images
File Type: jpg DSN Entry Box.jpg (59.0 KB, 365 views)
Apr 21 '11 #1
5 4221
pod
298 100+
these are the different connection strings I use for my little projects :)

...hope that helps


Expand|Select|Wrap|Line Numbers
  1.     Dim MSAdbPathAndName As string
  2.     MSAdbPathAndName = "C:\myMSAccessDB.mdb"
  3.     Dim DBS As String
  4.     Dim UID As String 
  5.     Dim PWD As String 
  6.     Dim DBN As String 
  7.     DBS = "websrvr1"
  8.     UID = "user1"
  9.     PWD = "pwd1"
  10.     DBN = "testDB"
  11.  
  12.  
  13.     ORACLE_ConnString = "Provider=msdaora;Data Source=" & DBN & _
  14.                         ";User Id=" & UID & ";Password=" & PWD & ";"
  15.  
  16.     SQL_ConnString = "Provider=SQLOLEDB;DATA SOURCE=" & DBN & _
  17.                         ";UID=" & UID & _
  18.                         ";PWD=" & PWD & _
  19.                         ";Initial Catalog=" & DBN & ";"
  20.  
  21.     MSACCESS_ConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
  22.                             MSAdbPathAndName & ";"
Apr 26 '11 #2
patjones
931 Expert 512MB
Hi,

Thanks for replying. I have tried various connection strings, including your SQL_ConnString, to no avail. If I may ask, you have DATA SOURCE and INITIAL CATALOG set to the same thing; but isn't one the server name and the other the database name?

I'm not really sure where to go with this. Thanks.

Pat
Apr 26 '11 #3
pod
298 100+
Sorry for that, you are right, there was a typo there, it happened when I was cleaning up the code, should have left it as is :)
The Data Source is the servername [DBS] and catalog the database name [DBN]

found the string on:
http://www.sqlstrings.com/SQL-Server...on-strings.htm


Expand|Select|Wrap|Line Numbers
  1.  Dim DBS As String 
  2.     Dim UID As String  
  3.     Dim PWD As String  
  4.     Dim DBN As String  
  5.     DBS = "websrvr1" 
  6.     UID = "user1" 
  7.     PWD = "pwd1" 
  8.     DBN = "testDB" 
  9.  SQL_ConnString = "Provider=SQLOLEDB;DATA SOURCE=" & DBS & _
  10.                         ";UID=" & UID & _
  11.                         ";PWD=" & PWD & _
  12.                         ";Initial Catalog=" & DBN & ";"
Apr 27 '11 #4
patjones
931 Expert 512MB
Hi,

A little more information. It turns out that if I use

Expand|Select|Wrap|Line Numbers
  1.     .Connect = "ODBC; DATABASE=database09; UID=GrantsDBUser; PWD=XXX; DSN=J:\BENEFITS\Pat\Access\Grants Watchlist\GrantsDBLink"
  2.     .ReturnsRecords = False
  3.     .SQL = "EXEC dbo.sproc_SaveGrant '" & Me.txtPIN & "', '" & Me.txtProjectName & "', " & CInt(Me.txtYearIssued) & ", " & Me.cboGrantProgram & ", " & Me.txtAwardAmt & ", " & Me.cboBoro & ", '" & Me.txtDescription & "', '" & Me.txtExpiration & "'"

then I get the message 3151 ODBC--connection to 'J:\BENEFITS\Pat\Access\Grants Watchlist\GrantsDBLink' failed. This connection string is what the Access help pages say to use. On the other hand, if I say

Expand|Select|Wrap|Line Numbers
  1.     .Connect = "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=database09;UID=GrantsDBUser;PWD=XXX;APP=2007 Microsoft Office"

then I get 229[Microsoft][SQL Server Native Client 10.0][SQL Server] The EXECUTE permission was denied on the object 'sproc_SaveGrant', database 'GrantsWatchList', schema 'dbo'. This is the connection string that I get when I hover over one of the SQL Server-linked tables in the Navigation Pane, with the PWD specification added in by me. At least with this one, there is some indication that the code is communicating with SQL Server, whereas with the first one the link fails altogether.

I've tried numerous other connection strings, to no avail. Any ideas, anyone? This is driving me up a wall. Thanks.

Pat
Apr 27 '11 #5
patjones
931 Expert 512MB
Hi,

Got it working with the second connection string pointed out in my previous post:

Expand|Select|Wrap|Line Numbers
  1. .Connect = "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=database09;UID=GrantsDBUser;PWD=XXX;APP=2007 Microsoft Office"

In order to fix the issue of not having EXECUTE permission, as mentioned in my previous reply, I had to go into SQL Server Management Studio > Database Properties > Permissions, and grant Execute permission to the user GrantsDBUser. It then works.

What is interesting though is that while it shows sensitivity to changing the DRIVER or SERVER parameters, I can change UID to anything and it still works. Not sure how this is possible, but at least I have a basic level of functionality now.

Pat
Apr 28 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Lauren Quantrell | last post: by
1 post views Thread by David C. Allen | last post: by
3 posts views Thread by Niyazi | last post: by
2 posts views Thread by honcho | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.