Execute a stored procedure from Access VBA (1 Viewer)

speedball

Registered User.
Local time
Today, 00:08
Joined
Oct 21, 2009
Messages
44
Hello

I want to switch from an all Access 2003 database to Access 2010 on the front end and SQL Server 2008 on the back end. Before I spend a lot of time on this I'm trying to see if I can get a few simple things to work.

I'm trying to execute a stored procedure in SQL Server from Access VBA. I have found and modifed the following code from searching on the internet:

Code:
Private Sub StoredProcedureTest()
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
 
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=COMPUTERNAME\SQLINSTANCENAME;Initial Catalog=DatabaseName;User ID=LoginName;Password=UsersPassword"
    cnn.Open
    Set rs = New ADODB.Recordset
    Set rs = cnn.Execute("EXEC uspSimple")
    Debug.Print rs(0), rs(1), rs(2)
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

When I run this I get an error when it gets to the cnn.open part with an error message of "Login failed for user 'LoginName'.

I have the following questions:

1. Am I taking the correct approach?

2. If I'm using Windows Authentication for the database, how do I include my windows login and password

3. How do I run the stored procedure without including the login and password in the Access VBA code. I assume that this must be possible!

Thank you in advance
Speedball
 

speedball

Registered User.
Local time
Today, 00:08
Joined
Oct 21, 2009
Messages
44
Thanks Galaxiom

I did some more searching on the internet before your reply and found that I can include Trusted_Connection = Yes in place of the User ID and Password. So I now have the following, which works:

Code:
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=COMPUTERNAME\SQLINSTANCENAME;Initial Catalog=DatabaseName;Trusted_Connection=Yes"

I've had a look on the website that you have suggested and found this, which also works:

Code:
cnn.ConnectionString = "Provider=SQLNCLI10;Server=COMPUTERNAME\SQLINSTANCENAME;Database=DatabaseName; Trusted_Connection=yes;"

I'm not surprised that there is more than one way to achieve the same result and am sure there are other ways as well.

But I would like to know if the generaal approach that I'm taking is a good one to take. Also, if you have any suggestions of websites or books that give an easy to understand explanation what is happening when you connect from Access to SQL Server I'd be grateful as this would help me to move on from my current approach of finding something that works and using it without really understanding what I'm doing.

Thanks
Speedball
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 11:08
Joined
Jan 20, 2009
Messages
12,849
SQLNCLI10 is not included on Windows XP systems. It can be installed from the msi in the SQL Server installer.

Running Stored Procedures is a very good way to operate. Generally you would have some parameters for them to increase their versatility.

I just Google what I need to know.
 

speedball

Registered User.
Local time
Today, 00:08
Joined
Oct 21, 2009
Messages
44
Okay, thanks for your help and the point about SQLNCLI10 not being included on Windows XP systems.

Thanks
Speedball
 

mdlueck

Sr. Application Developer
Local time
Yesterday, 20:08
Joined
Jun 23, 2011
Messages
2,631

speedball

Registered User.
Local time
Today, 00:08
Joined
Oct 21, 2009
Messages
44
Thanks Michael

I've a had a look at the code from the threads that you referenced and will try to incorporate into my code.

One question. Your code includes references to the following:

Code:
.ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()
.CommandText = "clsObjProductsTbl_LocateProductByID"
.CommandType = adCmdStoredProc

I've had a look on www.connectionstrings.com as suggested by Galaxiom, but can't find anything that looks like this. Am I missing something?

Thanks
Speedball
 

mdlueck

Sr. Application Developer
Local time
Yesterday, 20:08
Joined
Jun 23, 2011
Messages
2,631
Your code includes references to the following:

Am I missing something?

Oops, my bad. That was taken from code to run a Stored Procedure. Try this in its place...

Code:
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdText
    .CommandText = strSQL
Oh... you really wanted to run a SP according to the top of the thread.

I just happen to have a class object which keeps track of the connection to the BE DB. You may disreguard that and simply paste in the connection string = "connectionstring" style to get going.


.CommandType = adCmdStoredProc is correct for a Stored Procedure

Fill in the name of your SP in the:
.CommandText = "SPNameGoesHere"

Further Oh, I guess it would help if I slowed down. ADO, not DAO... :banghead:

Right, In the case of ADO that Class Object keeps the one global (to the FE application) ADO Connection object floating about to be passed to ADO objects which require it. (With DAO objects you pass the connection string.)

All right, so this is where the ADO Connection object comes from...

Code:
Rem /************************************************************************************/
Rem /* FILENAME       :  clsObjBEDBConnection                                           */
Rem /* TYPE           :  VBA Class                                                      */
Rem /* DESCRIPTION    :  Class containing DSN-less ADO DB connection object for other   */
Rem /*                   ADO DB objects to utilize                                      */
Rem /*                                                                                  */
Rem /* AUTHOR         :  Michael D Lueck                                                */
Rem /*                   mlueck@lueckdatasystems.com                                    */
Rem /*                                                                                  */
Rem /* NEEDS          :                                                                 */
Rem /*                                                                                  */
Rem /* USAGE          :                                                                 */
Rem /*                                                                                  */
Rem /* REVISION HISTORY                                                                 */
Rem /*                                                                                  */
Rem /* DATE       REVISED BY DESCRIPTION OF CHANGE                                      */
Rem /* ---------- ---------- -------------------------------------------------------    */
Rem /* 08/22/2011 MDL        Initial Creation                                           */
Rem /* 08/25/2011 MDL        Added explicit function return declaration                 */
Rem /* 11/04/2011 MDL        Updated to use getMSysObjectsConnectString call rather than*/
Rem /*                       reinventing the wheel, added Terminate                     */
Rem /* 10/24/2012 MDL        Updated to make ADODBConnectionObj a class property        */
Rem /* 10/24/2012 MDL        Insured that return in the On Error of a Property Get which*/
Rem /*                       returns an Object will return Nothing                      */
Rem /* 10/24/2012 MDL        Correct return object type of ADODBConnectionObj           */
Rem /* 10/26/2012 MDL        Greatly enhanced the error checking in this class          */
Rem /* 11/13/2012 MDL        Enhancements to Class Initialization error handling        */
Rem /* 12/17/2012 MDL        Forced to move to a disposable adoConn obj as Win7 power   */
Rem /*                       save appears to kill it                                    */
Rem /************************************************************************************/

Option Compare Database
Option Explicit

'Attributes
Dim lngFailCount As Long
Dim lngMaxFailCount As Long

Private Sub Class_Initialize()
On Error GoTo Err_Class_Initialize

  'Reset the error counters
  lngFailCount = 0

  'Set Max Fail Count
  lngMaxFailCount = 4

Exit_Class_Initialize:
  Exit Sub

Err_Class_Initialize:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Subroutine: Class_Initialize()")
  'Disable further error handling. Since the code was already handling an error, if we raised the error without first
  'turning it off, that would result in an "Automation Error" aka Double Trap
  On Error GoTo 0
  'Raise the error to the caller program
  Err.Raise Number:=vbObjectError + 1, _
            Source:="Class: " & TypeName(Me) & ", Subroutine: Class_Initialize()", _
            Description:="Failed to Class_Initialize() class instance"
  Resume Exit_Class_Initialize

End Sub

Public Function ADODBConnectionObj() As Object
On Error GoTo Err_ADODBConnectionObj

  Dim adoConn As Object
  Dim strDBConnectionString As String

  'Obtain the correct connection string for ADO type objects
  strDBConnectionString = ObjAppSettings.MSysObjectsConnectString()

  'Next create a new Connection object
  Set adoConn = CreateObject("ADODB.Connection")

  'And connect to the BE DB
  adoConn.Open strDBConnectionString

  'Check the Connection object
  If Me.Check(adoConn) = False Then
    'Bad connection, so trash the object and return an error
    Set adoConn = Nothing
  End If

  'Return the Connection object
  Set ADODBConnectionObj = adoConn

Exit_ADODBConnectionObj:
  'Clean up the connection to the database
  Set adoConn = Nothing

  Exit Function

Err_ADODBConnectionObj:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: ADODBConnectionObj()")
  Set ADODBConnectionObj = Nothing
  Resume Exit_ADODBConnectionObj

End Function

Public Function Check(ByRef adoConn As Object) As Boolean
On Error GoTo Err_Check

  Dim lngRC As Long

  'Check the State of the Connection object
  lngRC = adoConn.State

  'Process the return code
  If lngRC <> adStateOpen Then
    Select Case lngRC
      Case adStateClosed
        Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: Check(), Error: adoConn.State = adStateClosed")
      Case adStateConnecting
        Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: Check(), Error: adoConn.State = adStateConnecting")
      Case adStateExecuting
        Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: Check(), Error: adoConn.State = adStateExecuting")
      Case adStateFetching
        Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: Check(), Error: adoConn.State = adStateFetching")
      Case Else
        Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: Check(), Error: Unknown adoConn.State = " & lngRC)
    End Select

    'Bump the Failed Connection Counter +1
    Call Me.IncrementFailCount

    'Bad return code
    Check = False
  Else
    'Good return code
    Check = True
  End If

Exit_Check:
  Exit Function

Err_Check:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: Check()")
  Check = False
  Resume Exit_Check

End Function

Public Function FailCount() As Long

  FailCount = lngFailCount

End Function

Public Function MaxFailCount() As Long

  MaxFailCount = lngMaxFailCount

End Function

Public Sub IncrementFailCount()
On Error GoTo Err_IncrementFailCount

  lngFailCount = lngFailCount + 1

  'Check if we have reached the MaxFailedCount
  If lngFailCount = lngMaxFailCount Then
    Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Subroutine: IncrementFailCount(), Error: Reached the Max Failed Connection Level of " & Me.MaxFailCount & ". Exiting Fandango FE NOW!")
    'Close all of Access... bye bye now bye bye!!
    DoCmd.Quit
  End If

Exit_IncrementFailCount:
  Exit Sub

Err_IncrementFailCount:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Subroutine: IncrementFailCount()")
  Resume Exit_IncrementFailCount

End Sub
And yes, you will see that required to create an instance is a connection string. The syntax of the connection string between ADO and DAO objects is a bit different. Here is where the connection strings come from:

Code:
Public Function ODBCConnectString() As String
  On Error GoTo Err_ODBCConnectString
  
  'LOC for a DSN'less connection
  ODBCConnectString = "ODBC;Driver=SQL Server;Server=" & Me.DBServer & ";Database=" & Me.DBDatabase & ";UID=" & Me.DBUID & ";PWD=" & Me.DBPWD & ";"

Exit_ODBCConnectString:
  Exit Function

Err_ODBCConnectString:
  Call errorhandler_MsgBox("Class: clsObjAppSettings, Function: ODBCConnectString()")
  ODBCConnectString = vbNullString
  Resume Exit_ODBCConnectString

End Function

Public Function MSysObjectsConnectString() As String
  On Error GoTo Err_MSysObjectsConnectString

  'LOC for a DSN'less connection
  MSysObjectsConnectString = "Driver=SQL Server;Server=" & Me.DBServer & ";Database=" & Me.DBDatabase & ";Description=Fandango FE;UID=" & Me.DBUID & ";PWD=" & Me.DBPWD & ";APP=2007 Microsoft Office system;"

Exit_MSysObjectsConnectString:
  Exit Function

Err_MSysObjectsConnectString:
  Call errorhandler_MsgBox("Class: clsObjAppSettings, Function: MSysObjectsConnectString()")
  MSysObjectsConnectString = vbNullString
  Resume Exit_MSysObjectsConnectString

End Function
The ODBCConnectString() is used by DAO objects.
The MSysObjectsConnectString() is used by ADO objects.
 
Last edited:

speedball

Registered User.
Local time
Today, 00:08
Joined
Oct 21, 2009
Messages
44
Thanks for the detailed response. I'll need to do some more reading up on this and will then return to your response once I have a better understanding of the fundamentals.

Thanks
Speedball
 

mdlueck

Sr. Application Developer
Local time
Yesterday, 20:08
Joined
Jun 23, 2011
Messages
2,631
there was SOME difference... maybe it was ODBC connections that needed a different string.... been too long, I just copy/paste working code and the new code works! ;)

Well this morning I went looking for what was using the ODBC string. Turns out that Pass-Through DAO objects use that string....

Code:
  'Build the BE PT Query
  Set daoQDFbe = daoDB.CreateQueryDef(strQryNameBE)
  With daoQDFbe
    .Connect = ObjAppSettings.ODBCConnectString()
    .SQL = strSQLbe
    .Close
  End With
And the various strings are as follows:

Code:
Public Property Get ODBCConnectString() As String
  'LOC for connection using a DSN
  'ODBCConnectString = "ODBC;DSN=" & dbDSN & ";UID=" & dbUID & ";PWD=" & dbPWD & ";"

  'LOC for a DSN'less connection
  ODBCConnectString = "ODBC;Driver=SQL Server;Server=" & dbServer & ";Database=" & dbDatabase & ";UID=" & dbUID & ";PWD=" & dbPWD & ";"
End Property

Public Property Get MSysObjectsConnectString() As String
  'LOC for connection using a DSN
  'MSysObjectsConnectString = "DSN=" & dbDSN & ";Description=Fandango Development;UID=" & dbUID & ";PWD=" & dbPWD & ";APP=2007 Microsoft Office system;"

  'LOC for a DSN'less connection
  MSysObjectsConnectString = "Driver=SQL Server;Server=" & dbServer & ";Database=" & dbDatabase & ";Description=Fandango FE;UID=" & dbUID & ";PWD=" & dbPWD & ";APP=2007 Microsoft Office system;"
End Property
As you can see from the commented LOC's... prior to DSN'less I was requiring a DSN to be configured / maintained on each node, next I moved to registry, and now I copy an INI file to the workstation via Software Distribution... same process that handles refreshing the FE DB. I have done those changes so that even on the most restrictive node permissions (Restricted User) my application is able to be deployed and work properly without any pop-up warnings.
 

Users who are viewing this thread

Top Bottom