Connection string to access SQL Server stored procedure not working

[patjones]

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:

Dim qrySP As QueryDef
Set qrySP = CurrentDb.CreateQueryDef("SaveGrant")
 
With qrySP
 
    .Connect = "ODBC; DATABASE = XXX; UID = XXX; PWD = XXX; DSN = XXX"
    .ReturnsRecords = False
    .SQL = "EXEC dbo.sproc_SaveGrant " & Nz(DMax("fldGrantID", "dbo_tblPlanning"), 0) + 1 & ",'" & Me.txtProjectName & "'," & CInt(Me.txtYearIssued) & "," & Me.cboGrantSourceType
    .Execute
 
    .Close
 
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

[pod]

these are the different connection strings I use for my little projects :)

...hope that helps

    Dim MSAdbPathAndName As string
    MSAdbPathAndName = "C:\myMSAccessDB.mdb"
    Dim DBS As String
    Dim UID As String 
    Dim PWD As String 
    Dim DBN As String 
    DBS = "websrvr1"
    UID = "user1"
    PWD = "pwd1"
    DBN = "testDB"
 
 
    ORACLE_ConnString = "Provider=msdaora;Data Source=" & DBN & _
                        ";User Id=" & UID & ";Password=" & PWD & ";"
 
    SQL_ConnString = "Provider=SQLOLEDB;DATA SOURCE=" & DBN & _
                        ";UID=" & UID & _
                        ";PWD=" & PWD & _
                        ";Initial Catalog=" & DBN & ";"
 
    MSACCESS_ConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
                            MSAdbPathAndName & ";"

[patjones]

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

[pod]

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

 Dim DBS As String 
    Dim UID As String  
    Dim PWD As String  
    Dim DBN As String  
    DBS = "websrvr1" 
    UID = "user1" 
    PWD = "pwd1" 
    DBN = "testDB" 
 SQL_ConnString = "Provider=SQLOLEDB;DATA SOURCE=" & DBS & _
                        ";UID=" & UID & _
                        ";PWD=" & PWD & _
                        ";Initial Catalog=" & DBN & ";"

[patjones]

Hi,

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

    .Connect = "ODBC; DATABASE=database09; UID=GrantsDBUser; PWD=XXX; DSN=J:\BENEFITS\Pat\Access\Grants Watchlist\GrantsDBLink"
    .ReturnsRecords = False
    .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

    .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

[patjones]

Hi,

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

.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