I have an MS Access application that contains all tables linked to SQL Server, so in MS Access VBA code or query I work with those tables very simple, I access them via name, like [Customers].
Also I have a stored procedure in SQL Server called sp_CopyData which I need to call from my VBA code. How can I do that without creating new connection to SQL Server (I already have it somewhere!? because I have access to tables)?
Or it's impossible? Appreciate any help. Thanks!
The right answer found out, it should be like:
Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.CreateQueryDef("")
qdef.Connect = CurrentDb.TableDefs("[ANY LINKED TABLE TO MS SQL SERVER]").Connect
qdef.SQL = "EXEC sp_CopyData"
qdef.ReturnsRecords = False ''avoid 3065 error
qdef.Execute
qdef.ReturnsRecords = False before I execute the query.
Create a pass-through query, and you can then use this through 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
.Execute
End With
.ReturnsRecords = False on the DAO.QueryDef object.The code in MS Access works for me:
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=[DB];Data Source=[PC];Integrated Security=SSPI;"
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_CopyData"
cmd.Parameters.Append cmd.CreateParameter("@param", adVarChar, adParamInput, 255, param)
cmd.Execute
Try:
CurrentProject.Connection.Execute "EXEC sp_CopyData"
References: http://msdn.microsoft.com/en-us/library/office/ff821478(v=office.14).aspx
sp_prefix for your stored procedures in SQL Server. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_and use something else as a prefix - or no prefix at all!