MS Access call SQL Server stored procedure

Question

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!

Cuneo

Side note: you should not use the 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 avoid sp_ and use something else as a prefix - or no prefix at all! – Firedog

@MitchWheat: I don't think so - see Aaron's take on it – Firedog

Answer 1

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

Cuneo

For any using this to run a stored procedure that doesn't return anything, you may get a "(3065) Cannot execute a select query." error like I did. I was able to fix this issue by adding the line qdef.ReturnsRecords = False before I execute the query. – Suzannesuzerain

What is "ANY LINKED TABLE TO MS SQL SERVER"? It seems like your involving a table which has nothing to do with the stored procedure being executed. That's bad form. – Kirmess

@johnywhy If you don't want ot hard code the connection string in your routine, then you need to grab if from one of the existing tables. You are free to use one that is involved in your query, just for looks and to prevent complaints, but that runs the risk of people thinking that it is more related than it is. – Disapproval

Answer 2

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

Avaricious

It worked for me with both inputs from ihorko and Kallal but one little piece was missing: .ReturnsRecords = False on the DAO.QueryDef object. – Dowie

Answer 3

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

Santanasantayana

Answer 4

Try:

CurrentProject.Connection.Execute "EXEC sp_CopyData"

References: http://msdn.microsoft.com/en-us/library/office/ff821478(v=office.14).aspx

Succubus

It's like CurrentProject.Connection has a connection to current Access DB, not to linked SQL SERVER... I need to call it from SQL Server... – Cuneo

CurrentProject.Connection is probably OK when used in an ADP. They are deprecated anyway. – Lacilacie