How to execute SQL strored procedure from Access

[Vinod]

Hi All,

There is a SQL stored procedure in SQL Server 2005 which restroes one of the
database with client's backup.

Is it possible to execute the same stored procedure from MS Access, if yes
how it will be?

Advanced Thanks
Vinod

 

[Albert D. Kallal]

There is quite a ways, but my favorite way is to simply build a pass through
query, and then simply execute that query....

Execute MyStoredProcName

Then, in code, you can go:

dim rst as dao.RecordSet
set rst = currentdb.QueryDefs("MyPassThought").Execute

If you want to execute a stored procedure of *your* choice at runtime, NOT
as a saved query, then again you can use the above proc, ad simple
change the sql.

eg:

Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset


Set qdfPass = CurrentDb.QueryDefs("MyPass")
qdfPass.SQL = "exec sp_myProc"
qdfPass.Execute

So, you can build ONE pass-though query in the query builder and THEN use
that query over and over to send any sql, or in fact any command to the sql
server...including the Execute "you proc name".

In fact, I usually have two queries, one has the properties set to NOT
return
records, and the other one does...(you will find this setting in the query
builder design view when you display the property sheet for that query in
the query builder...

 

[Vinod]

Thanks Albert for your immediate response.

Here is my intension of the stored procedure functionality for SQL Server
2005:

create procedure Restore_DB
As
Begin
--find all the connection processes to the database and kill them
Declare @dbname sysname
Set @dbname = 'test'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End

--Rollback specifies if anything is executing after that period they will
be
--rolled back. So it provides some protection.
ALTER DATABASE [test] SET OFFLINE WITH ROLLBACK AFTER 60 SECONDS

--Restore database
RESTORE DATABASE [test]
FROM DISK = N'D:\sqldata\data\test.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
end

Please provide your guidence to work above functionality through your logic.

Advanced Thanks,
Vinod

 

[Albert D. Kallal]

To run that proc, just create a pass-through query with the folwing sql text
inside of it:

exec Restore_DB

Now, in ms-access to run the above simply go:

currentdb.QueryDefs("NameOfAboveQueryYouSavedItAs").Execute

 

[Vinod]

Sorry Albert, I'm not getting.

I'm new to the connection strings with other database for Access.
I'm not using any data source, I have to open connection for SQL Server
through connection string.

I'm creating linked tables in following way, without using any data source.
Public Function AttachTables()
Dim tdTemp As TableDef

Set tdTemp = CurrentDb.CreateTableDef("customer")
tdTemp.SourceTableName = "dbo.customer"
tdTemp.Connect = "ODBC;DRIVER={SQL
Server};SERVER=PERSONAL-C4775E\SQLEXPRESS;DATABASE=test;Trusted_Connection"
CurrentDb.TableDefs.Append tdTemp

End Function

Same as above I would like to execute strored procedure using connection
string which required in your example.

Please try to provide each and every line of code the way how you write if
you are in my position as per said requirement.

Advanced Thanks,
Vinod

 

[Albert D. Kallal]

The code I posted will work:

currentdb.QueryDefs("NameOfAboveQueryYouSavedItAs").Execute

It takes ONE LINE.

What you have to do is build the pass-though query. when you build that
query, you type in the sql text as:

exec Restore_DB

You have of course set this query as pass-though,a nd when you do that, you
get chance to enter your connection string...

You can paste in your connection string that works here. We only have to
write ONE LINE of code here.

That one line is:
currentdb.QueryDefs("NameOfAboveQueryYouSavedItAs").Execute

The steps are:

create new query, you choose design view

You get a prompt for the tables, just hit close as we are not going to add a
table to the sql query.

Now, you have a blank query grid (query designer). You go query->sql
specific->pass though

At this point you can type in your command, eg:

exec Restore_DB

Now, make sure you display the property sheet for this query.
from the menu, you go view->Properties (note that if the property sheet is
already displayed, then this will hide the property sheet).

In the property sheet, you see the ODBC Connect Str..you can click on this
box and paste in your existing string, or simply hit the [...] button that
displays, and you can launch a wizard that will allow you to build/setup the
connection string. Or, if you want, simply paste in your connection string
that you had in your past post...

Make sure you set the option "Returns records" to yes, or no depending on if
your stored proc returns data or not.....

Now, save this query.

Then, execute, we use that one line of code to run that pass-though query

currentdb.QueryDefs("NameOfAboveQueryYouSavedItAs").Execute

Of course you can try running the query by clicking on it to see if it
works....

Please try to provide each and every line of code

There is ONLY ONE LINE of code here...again...ONE LINE..it is:

currentdb.QueryDefs("NameOfAboveQueryYouSavedItAs").Execute

 

[a a r o n _ k e m p f]

Dude it is not just ONE LINE of code

it is ONE LINE OF CODE to _EXECUTE_ and many lines of code to set the
correct arguments (for the sproc).

Or you can just move to ADP and have a real solid enterprise ready
database.

The code I posted will work:

currentdb.QueryDefs("NameOfAboveQueryYouSavedItAs").Execute

It takes ONE LINE.

What you have to do is build the pass-though query. when you build that
query, you type in the sql text as:

exec Restore_DB

You have of course set this query as pass-though,a nd when you do that, you
get  chance to enter your connection string...

You can paste in your connection string that works here. We only have to
write ONE LINE of code here.

That one line is:
currentdb.QueryDefs("NameOfAboveQueryYouSavedItAs").Execute

The steps are:

create new query, you choose design view

You get a prompt for the tables, just hit close as we are not going to add a
table to the sql query.

Now, you have a blank query grid (query designer). You go query->sql
specific->pass though

At this point you can type in your command, eg:

exec Restore_DB

Now, make sure you display the property sheet for this query.
from the menu, you go view->Properties (note that if the property sheet is
already displayed, then this will hide the property sheet).

In the property sheet, you see the ODBC Connect Str..you can click on this
box and paste in your existing string, or simply hit the [...] button that
displays, and you can launch a wizard that will allow you to build/setup the
connection string. Or, if you want, simply paste in your connection string
that you had in your past post...

Make sure you set the option "Returns records" to yes, or no depending onif
your stored proc returns data or not.....

Now, save this query.

Then, execute, we use that one line of code to run that pass-though query

currentdb.QueryDefs("NameOfAboveQueryYouSavedItAs").Execute

Of course you can try running the query by clicking on it to see if it
works....

Please try to provide each and every line of code

There is ONLY ONE LINE of code here...again...ONE LINE..it is:

currentdb.QueryDefs("NameOfAboveQueryYouSavedItAs").Execute