MS Access call SQL Server stored procedure

sql-server vba ms-access stored-procedures ms-access-2007
ihorko picture ihorko · Sep 14, 2013 · Viewed 67.7k times · Source

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!

Answer

ihorko picture ihorko · Oct 6, 2013

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

Related questions

How to programmatically create an ODBC Linked Table to a SQL Server View and have it be editable?

When I create a DSN connection to SQL Server using the wizard, I am able to link it to a view. In this case, Access recognizes it as an editable table. But if I use a DSN-less connection to a …

Read More
Access VBA Parameter in passthrough query to SQL Server

I have several queries in an MS Access database. Some of these use parameters. I use the following code in VBA to provide the query with these parameters: VBA Dim startDate As Date Dim endDate As Date Dim dbs As …

Read More
How to link Access table to SQL Server with VBA

I am trying to create a linked (or imported) table in Access with a SQL Server backend. Basically the business users periodically need a copy of the table [SQL Rulesnew] (yes, with the space, sigh) so we want to give …

Read More