How to execute SQL stored procedure from Access

V

Vinod

Hi All,

I'm trying to execute below code which will restore test backup database in
sql server 2005 using stored procedure 'Restore_DB'

I'm getting an error 'Can not execute a select query' error no:'3065' while
'qdfPass.Execute' statement is running.

Where as I'm able to get select query result with same connection string but
stored procedure is not working.

Sub test1()
Dim qdfPass As DAO.QueryDef

Set qdfPass = Nothing
Set qdfPass = CurrentDb.CreateQueryDef("qryPass")
qdfPass.Connect = "ODBC;DRIVER={SQL
Server};SERVER=PERSONAL-C4775E\SQLEXPRESS;DATABASE=test;Trusted_Connection"
qdfPass.sql = "execute Restore_DB"

qdfPass.Execute
End Sub

Please help me resolving the issue.

Advanced Thanks
Vinod
 
D

Douglas J. Steele

One of the properties of a pass-through query is "Returns Records". Assuming
your stored procedure doesn't return records, set it to No.

If it does return records, you'll need to create a recordset based on the
query.
 
V

Vinod

Thanks Steele for your response.

Yes you are correct my stored procedure won't return any records.

Could you please guide me how to set it to 'No'?

Advanced Thanks
Vinod
 
V

Vinod

Thanks Steele for your response.

Just to update I've tried like this
CurrentProject.Connection.Execute "qryPass" 'instead of using qdfPass.Execute

This time it work fine. Anyway I would like to hear from you is how set it
to 'No' for return records. Since I tried to execute "qryPass" manually
(double clicking on query in database window) it is working fine at the end
it has been displaying an error saying 'Pass-through query with returns
records property set to TRUE did not return any records.' by clicking 'ok' I
knew that my stored procedure executed successfully by checking SQL Server.

Thanks,
Vinod
 
D

Douglas J. Steele

Open the query in Design view and look at the Properties window for it. One
of the properties is Returns Records.
 
A

a a r o n _ k e m p f

Dude, use a ADP

Docmd.RunSql "EXEC mySproc"
or
Docmd.OpenStoredProcedure "mySproc"

Jet just has too many layers to be practical, so it makes a lot of
sense for professional developers to move to a direct interface with
SQL Server.

In Access 2003, File, New, Project (existing data)

and presto-chango-- you don't need to use 12 different layers (like
Jet does)
 
V

Vinod

Hey Aaron_Kempf,

Its not match with my requirement as you said ADP. I do want to maintain
some talbes in Access which won't come through SQL Database. And the stored
procedure which I was talking is to restore the SQL backup copy. So very
offently it is required to restore the database with client's backup.

regards
Vinod
 
A

a a r o n _ k e m p f

why would you possibly want to keep data anywhere else than 'in the
database'?
 
A

a a r o n _ k e m p f

you can use TSQL to archive / restore databases into a .BAK file.

it doesn't take anything special, you just write the script that you
need.
Or you can use 'SQL Server Management Studio' to build the script that
you need

BACKUP DATABASE [AdventureWorksDW2008]
TO DISK = N'c:\ARC_20081221_AdventureWorksDW2008.bak'
WITH NOFORMAT, NOINIT,
NAME = N'AdventureWorksDW2008-Full Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top