Search code examples
ms-access-2016

Create Procedure from code or in Access?

Windows 10, Access 2016 I am moving a very small database (14 tables and 40-50 stored procedures) from SQL Server to Access. I have tried to recreate the stored procedures from code using an OLEDB command object. This is a sample of a CommandText…

CREATE PROCEDURE DeleteOrderDetailByOrderID
        ([@ID] int)
        AS
        DELETE FROM OrderDetails
        WHERE (OrderID = @ID);

I get an error message that the Data Type of @ID is incorrect. It is not. When I remove the brackets from @ID all is forgiven and the code runs. However, Access strips the @ from @ID in the parameter section (not in the Where clause). I have had to go into Access and manually correct this. I do not like the idea of going through almost 5000 lines of code to correct parameter names in my program. I thought I could use the direct approach by pasting the SQL directly into Access but I get an error with this route saying syntax error in CREATE TABLE and it highlights the word PROCEDURE. This leads me to believe that you cannot use CREATE PROCEDURE directly in Access. Is this true? Is there another approach that I am missing?

Solution

  • You are missing, that T-SQL of SQL Server is not Access SQL.

    Access has UDFs - user defined functions - that can be used in queries also, but that is VBA code.

    If you just need a single-user file based database to hold your data, you may get away with the SQL Server Compact Edition which supports a subset of T-SQL.