Executing a SQL Stored Procedure from MS Access

[johnve]

Hi,

I have created a stored procedure in SQL Server and now I want to update a table within Access using the Stored Procedure.

I have searching the web and found this but I get a 3065 runtime error.

Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.CreateQueryDef("")
qdef.Connect = CurrentDb.TableDefs("dbo_Actuals_tbl").Connect
qdef.SQL = "EXEC sp_FinanceExecData1 2015, 9"
qdef.Execute

Any ideas on why and how I can fix it.

Thank you in advance.

John

 

[dhookom]

I almost always create a generic pass-through query to the SQL Server database. I then use faq701-7433 to update the SQL. Then simple use a line of code like:

CurrentDb.Execute "MyP-TQueryName", dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[johnve]

Thanks dhookom,

I'm still learning but would I replace the "SELECT statement" with "EXEC sp_FinanceExecData1"

strSQL = "SELECT field1, field2, field3 FROM tblMyTable " & _
"WHERE OrderDate BETWEEN #" & Me.txtStart & "# AND #" & _
Me.txtEnd & "# " & _
"ORDER BY field2, field1 DESC"

Thanks

John

 

[dhookom]

I would need to know what you mean by "update a table within Access". Are you going to append records to the Access table? The problem with P-T queries is they aren't updateable.

Duane
Hook'D on Access
MS Access MVP

 

[johnve]

Yes I want to update (replace) a table in Access from the data retrieved from the stored procedure.

 

[dhookom]

I would just use the code to replace the SQL of the pass-through query and then use it as the source of an append query.

Duane
Hook'D on Access
MS Access MVP