Pass multiple values from MS Access form to Sql Server table with INSERT

Posted on 2009-04-16
Last Modified: 2013-12-05
I have a form with several combo boxes and I want to create a stored proc to insert the values from the form into a sql server table. I've done this with simple SP's using the InputParameters property on the form but I'm not sure how to do it with the INSERT statement.
Question by:myndwire
    LVL 65

    Expert Comment

    You know, if you didnt want a stored procedure, you can build up your SQL INSERT statement (eg INSERT INTO mytable (f1, f2, f3) VALUES (1,2,3) then use the Execute method of your ado connection object
    LVL 65

    Accepted Solution

    stored procedure example. Not sure if this is what you have done already or the info you are looking for

        Set cmd = New ADODB.Command
        cmd.ActiveConnection = conn
        cmd.CommandText = "StoredProcName"
        cmd.CommandType = adCmdStoredProc
        cmd.ActiveConnection.CursorLocation = adUseServer     'to run on server

        cmd.Parameters.Append cmd.CreateParameter("ParameterName", Parameter type, parameter direction, length, Value)

        Set rs = cmd.Execute

    If you double click on CreateParameter in order to highlight it then hit F1 it will take you to the help pages where it lists the enums of the parameter type and direction

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now