MS Access call SQL Server stored procedure
The right answer found out, it should be like:
Dim qdef As DAO.QueryDefSet qdef = CurrentDb.CreateQueryDef("")qdef.Connect = CurrentDb.TableDefs("[ANY LINKED TABLE TO MS SQL SERVER]").Connectqdef.SQL = "EXEC sp_CopyData"qdef.ReturnsRecords = False ''avoid 3065 errorqdef.Execute
Create a pass-though query, and you can then use this throught the WHOLE application anytime you need to execute some T-SQL.
The code this becomes:
With CurrentDb.QueryDefs("qPass") .SQL = "exec sp_copydata" .ReturnsRecords = False ''avoid 3065 error .ExecuteEnd With
The code in MS Access works for me:
Dim cmd As ADODB.CommandSet cmd = New ADODB.Commandcmd.ActiveConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=[DB];Data Source=[PC];Integrated Security=SSPI;"cmd.CommandType = adCmdStoredProccmd.CommandText = "sp_CopyData"cmd.Parameters.Append cmd.CreateParameter("@param", adVarChar, adParamInput, 255, param)cmd.Execute