Calling stored procedure using VBA
Asked Answered
P

2

12

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?

Phenology answered 17/6, 2014 at 14:57 Comment(5)
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?Seattle
I am not really sure I did not create the database I am just writing procedures for it. Is there a way to tell?Phenology
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?Seattle
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.Phenology
That's all fine. I'll take a look and come up with something for you.Seattle
S
19

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

Seattle answered 17/6, 2014 at 16:34 Comment(2)
The connection string might need to be different since CurrentDB.TableDef objects use DAO by default, and this connection is using ADO. connectionstrings.com is a pretty useful resource for figuring out the necessary changes.Rubbish
What is Me.Connection ? It thows error invalid use of Me keyword.Culminate
L
1

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
Longhorn answered 16/9, 2019 at 5:52 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.