Syntax error or access violation in MS-Access stored proc?

Question

I'm attempting to create a very simple stored procedure with one parameter in MS-Access 2003.

CREATE PROC procTest(lname VARCHAR(50)) AS 
SELECT * FROM T_Employees t WHERE t.LastName = lname;

I get the error:

Error: [Microsoft][ODBC Microsoft Access Driver]Syntax error or access violation
SQLState:  37000
ErrorCode: 55

I'm a real n00b at stored procedures so I'm not sure what the issue is here...

Nope. It's just MS-Access 2003. I'm working on importing it into SQL Server though... — leeand00, Oct 12 '12 at 18:49

score 2 · Accepted Answer · answered Oct 10 '12 at 15:41

You're attempting to create the procedure from an ODBC connection.

Error: [Microsoft][ODBC Microsoft Access Driver]

CREATE PROCEDURE was introduced in Jet 4, and AFAIK can only be executed from ADO, and ODBC supports only DAO, not ADO.

I suspect you will not be able to create that parameter query from Squirrel SQL. You must either create it from within Access or via an ADO connection.

Or if you can shell out from Squirrel to run a VBScript, create one which uses an ADO connection to create that query.

Syntax error or access violation in MS-Access stored proc?

1 Answers1