Create Stored Procedure in SQL server from Access VBA?

[Vinod]

Hi All,

Please help me out in creation of stored procedure from Access in SQL Server

I've been using following method:
strProc="Create Procedure test As Begin Delete * from EMP; Delete * From
temp; End"
Set qdfPass = Nothing
Set qdfPass = CurrentDb.QueryDefs("qryPass")
qdfPass.Connect = "ODBC;DRIVER={SQL
Server};SERVERPERSONAL-4B2728\SQLEXPRESS;DATABASE=master;Trusted_Connection"

qdfPass.SQL = strStrdProc
CurrentProject.Connection.Execute "qryPass"

I'm getting following error:
Invalid SQL statement; expected 'Delete', 'Insert', 'Update' or 'Select',
etc.,

Please share your thoughts and ideas in resolving above issue for successful
creation of stroed procedure in SQL Server 2005 from MS Access 2003.

Advanced Thanks
Vinod

 

[Norman Yuan]

In T-SQL (SQL Sever's version of SQL), the statement for deleting is like:

DELETE FROM TableName WHERE...

not (notice the "*"):

DELETE * FROM TableName WHERE...

 

[Piet Linden]

In T-SQL (SQL Sever's version of SQL), the statement for deleting is like:

DELETE FROM TableName WHERE...

not (notice the "*"):

DELETE * FROM TableName WHERE...

Why not just "TRUNCATE emp;"
Or did you want all that logging?

 

[Norman Yuan]

What are you talking???

In T-SQL (SQL Sever's version of SQL), the statement for deleting is like:

DELETE FROM TableName WHERE...

not (notice the "*"):

DELETE * FROM TableName WHERE...

Why not just "TRUNCATE emp;"
Or did you want all that logging?

 

[Vinod]

Thanks Norman for your response.

For sample I've given 'delete * from emp'. My actual stored procedure is
nearly 100 lines of script which is working SQL server 2005.

My intension is, needs to check whether that stored procedure is existed or
not in SQL Server 2005. I'm able to verify the existance of stored procedure
if it is existed I'm able to execute that stored procedure from MS Access
2003. If it is not existed I'm giving a message to user as 'Test' stored
procedure is not available in SQL Server.

Insted of displaying a message to user, if the required stored procedure is
not existed, MS Access 2003 has to create a stored procedure in SQL Server
2005 by retrieving stored procedure script from one of my Access table.

Please help me out in resolving the issue which will be appreciated.

Advanced Thanks
Vinod