[AccessD] Access 2013 -- MDB Back Ends Dead?

jwcolby jwcolby at colbyconsulting.com
Mon Jul 23 20:02:52 CDT 2012


Don't forget that the SP has to be assigned execute permissions in SQL Server for the user trying to 
execute the SP.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 7/22/2012 9:36 PM, Arthur Fuller wrote:
> Mark,
>
> Generating the SPs for Inserts a cinch. SSMS does most of the heavy lifting
> for you. To create an Insert stored procedure for any given table in the
> database of interest, do this:
>
> 1. In SSMS, expand the database and its Tables node.
> 2. Right-click on the table name and select Script Table as | Insert To |
> New Query window.
>
> You will get something like this (from the Northwind database):
>
> <sql>
> INSERT INTO [Northwind].[dbo].[Categories]
>             ([CategoryName]
>             ,[Description]
>             ,[Picture])
>       VALUES
>             (<CategoryName, nvarchar(15),>
>             ,<Description, ntext,>
>             ,<Picture, image,>)
> GO
> </sql>
>
> I picked a small table so I could save as much space as possible in this
> reply, but the same thing happens no matter how many columns your table
> has. All you have to do now is replace the toksns with their parameter
> equivalents, twice, once in the parameter list area and then again in the
> section beneath the VALUES keyword
>
> Parameter List:
>
>             (<CategoryName, nvarchar(15),>
>             ,<Description, ntext,>
>             ,<Picture, image,>)
>
> VALUES List:
>
>             (@CategoryName
>             , at Description
>             , at Picture)
>
> Save the script to an SP name. Next Table. How hard is that?
>
> In the Access FE, for each of these generated SPs you'll need to create a
> command object and add a parameter for each of the parameters your SP will
> accept. Once you've added one parameter, you can copy and past that line
> over and over until you've accounted for all the columns. All you'll need
> to do then is indicate where the parameter values are coming from (i.e. the
> controls on a form, most likely, which in all probability have names almost
> identical to the columns from which they derive). One last line to execute
> the command object and you're done.
>
> Granted, it would be nice on both sides (Access and SQL) if there were
> tools to do what I just outlined. But come on, how difficult is it to do
> what I just outlined? If the Access side takes you a minute and the SQL
> side takes another minute, and you have 100 tables, in less than two hours
> you're all done. And without breaking a sweat.
>
> Arthur
>



More information about the AccessD mailing list