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?