Calling stored procedure using VBA

Question

I am working in Access 2010 user front-end with a Microsoft SQL Server 2008 back-end.

The tables in Access are all linked to the SQL server database.

I have a stored procedure that inserts new values (supplied by the parameters) into a table.

I asked a similar question previously and got a good answer Calling Stored Procedure while passing parameters from Access Module in VBA

I do not know how to find the information required for making a connection string (ex: I don't know the provider/server name/server address).

I found a question on here that stated "If you already have an Access linked table pointing to the SQL Server database then you can simply use its .Connect string with a DAO.QueryDef object to execute the Stored Procedure" - Connection string for Access to call SQL Server stored procedure

I tried to implement this code. To pass parameters, I tried using a previous example.

I got the error

call failed

at the line Set rst = qdf.OpenRecordset(dbOpenSnapshot) (not to mention my passing parameters code is probably way off).

Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = CurrentDb.TableDefs("tblInstrumentInterfaceLog").Connect
qdf.sql = "EXEC dbo.upInsertToInstrumentInterfaceLog"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

qdf.Parameters.Append qdf.CreateParameter("@BatchID", adVarChar, adParamInput, 60, BatchID)
qdf.Parameters.Append qdf.CreateParameter("@InstrumentName", adVarChar, adParamInput, 60, InstrumentName)
qdf.Parameters.Append qdf.CreateParameter("@FileName", adVarChar, adParamInput, 60, FileName)
qdf.Parameters.Append qdf.CreateParameter("@QueueId", adVarChar, adParamInput, 60, QuenueId)

rst.Close
Set rst = Nothing
Set qdf = Nothing

Could anyone tell me what could be wrong with my code and why I am getting this error?

is this a standard Access database or an Access Data Project? i.e. are the tables linked via ODBC (standard database) or have you connected your data project directly to a SQL Server database? — The Dumb Radish, Jun 17 '14 at 15:16
I am not really sure I did not create the database I am just writing procedures for it. Is there a way to tell? — VictoriaJay, Jun 17 '14 at 15:38
Not sure for Access 2010, but things to look at...are the tables linked tables or do they just look like native tables? Under the File menu option do you have a Connection option where the connection to the database is set? — The Dumb Radish, Jun 17 '14 at 15:53
The tables are linked tables and there is no connection option in the file menu but there is a module that controls what database the frontend is connected to. The databases are stored on the server. I don't know if that helps. — VictoriaJay, Jun 17 '14 at 16:14
That's all fine. I'll take a look and come up with something for you. — The Dumb Radish, Jun 17 '14 at 16:24

score 18 · Accepted Answer · edited Oct 18 '19 at 14:32

18

Victoria,

You can run a stored procedure using ADO, like below...

Set mobjConn = New ADODB.Connection
mobjConn.Open "your connection string"
Set mobjCmd = New ADODB.Command
With mobjCmd
    .ActiveConnection = mobjConn 
    .CommandText = "your stored procedure"
    .CommandType = adCmdStoredProc
    .CommandTimeout = 0
    .Parameters.Append .CreateParameter("your parameter name", adInteger, adParamInput, , your parameter value)
    ' repeat as many times as you have parameters

    .Execute
End With

To get your connection string, you can use the line

Debug.Print CurrentDb.TableDefs("tblInstrumentInterfaceLog").Connect

in the Immediate Window and that should show you a connection string which you can use.

Would you try that and let me know if you have any problems.

Ash

edited Oct 18 '19 at 14:32

Oliver

8,169
3
15
37

answered Jun 17 '14 at 16:34

The Dumb Radish

886
7
18

2

The connection string might need to be different since CurrentDB.TableDef objects use DAO by default, and this connection is using ADO. http://www.connectionstrings.com is a pretty useful resource for figuring out the necessary changes. – Hannah Vernon Jun 17 '14 at 17:45
2

What is `Me.Connection` ? It thows error `invalid use of Me keyword`. – Muflix Oct 10 '18 at 08:12

score 1 · Answer 2 · answered Sep 16 '19 at 05:52

Can also formulate a stored proc call that returns a result set as a select statement.

As per this example:

Sub Macro2()


'
' Macro1 Macro
'
    'Declare variables'
        Dim mySql As String
        Set objMyConn = New ADODB.Connection
        objMyConn.CommandTimeout = 0
        Set objMyCmd = New ADODB.Command
        objMyCmd.CommandTimeout = 0
        Set objMyRecordset = New ADODB.Recordset
        objMyConn.ConnectionString = CStr(Range("ADOConnectString").Value)
        objMyConn.Open
        Set objMyRecordset.ActiveConnection = objMyConn
        Set objMyCmd.ActiveConnection = objMyConn


   ' call dbo.TotalLHCLoadingRate  Range("TotalLHCLoadingRate")

        mySql = "select dbo.TotalLHCLoadingRate ( " _
    + CStr(Range("MemberNo").Value) _
    + ", getdate() ) "
        MsgBox "TotalLHCLoadingRate SQL : " + mySql
        objMyCmd.CommandText = mySql
        objMyCmd.CommandType = adCmdText
        objMyCmd.Execute
        objMyRecordset.Open objMyCmd
        Range("TotalLHCLoadingRate ").Value = ""
        Range("TotalLHCLoadingRate ").CopyFromRecordset (objMyRecordset)
        Range("TotalLHCLoadingRate ").Interior.ColorIndex = 37
        MsgBox "TotalLHCLoadingRate  : " + CStr(Range("TotalLHCLoadingRate ").Value)
        objMyRecordset.Close
End Sub

Calling stored procedure using VBA

2 Answers2

Linked

Related