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

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