19

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ihorko
  • 6,855
  • 25
  • 77
  • 116
  • 10
    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*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Sep 14 '13 at 10:49
  • @MitchWheat: [I don't think so - see Aaron's take on it](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix) – marc_s Sep 14 '13 at 10:59

4 Answers4

32

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
Smandoli
  • 6,919
  • 3
  • 49
  • 83
ihorko
  • 6,855
  • 25
  • 77
  • 116
  • 4
    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. – Nathan M. Jun 13 '17 at 13:29
  • 1
    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. – johny why May 05 '20 at 16:31
  • 1
    @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. – BWhite Apr 26 '21 at 17:11
16

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
June7
  • 19,874
  • 8
  • 24
  • 34
Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • 9
    It worked for me with both inputs from ihorko and Kallal but one little piece was missing: `.ReturnsRecords = False` on the DAO.QueryDef object. – Christoph Jun 25 '15 at 08:25
7

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
Lubart
  • 71
  • 1
  • 2
4

Try:

CurrentProject.Connection.Execute "EXEC sp_CopyData"

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

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
  • 1
    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... – ihorko Oct 06 '13 at 20:26
  • 2
    CurrentProject.Connection is probably OK when used in an ADP. They are deprecated anyway. – iDevlop May 22 '14 at 10:09