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?
To call a Sub procedure from another procedure, type the name of the procedure and include values for any required arguments. The Call statement is not required, but if you use it, you must enclose any arguments in parentheses. Use a Sub procedure to organize other procedures so they are easier to understand and debug.
1) turn the stored procedure into a view and connect your Excel spreadsheet to the view. 2) use VBA Excel's programming language VBA to run the stored procedure inside an Excel module. You can also run a query from Excel, you don't need to have the query saved into a stored procedure.
The EXEC command is used to execute a stored procedure, or a SQL string passed to it. You can also use full command EXECUTE which is the same as EXEC.
According to Microsoft standard, stored procedures cannot be executed inside the function, but technically it is possible with some tweaks.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With