Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA to submit data to SQL Server From Excel 2000 (VBA/excel 2000/sal datab

    I have an excel 2000 workbook that contains information that I would like my users to fill out and then submit to a MS SQL server database using a submit button from within the excel workbook. I am assuming that VBA can be used to accomplish this task. Please help...

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA to submit data to SQL Server From Excel 20

    Okay, I'm not sure how far along you are...

    The first step in a development project like this usually is to mock up your entry form and map your cells to your database fields so you know exactly what is going to go where. Second, consider that end-users are imperfect. What can go wrong that would cause an error in your submission to the database or put junk in there? Sketch out the rules (using English, little equations, whatever you like) for how you are going to validate (double-check) the user's entries before you start talking to the database. Also, if you plan to retrieve anything from the database, either to start the user out or to give a confirmation of a successful submission, think about how that is going to work. What information will be needed to track down the new record in the database?

    Once you have the general outline of your application, you can pick up the details of how to use VBA to extract data from a cell in an Excel workbook, examine and compare the data, change it to a new type if needed, format it if needed, and gather it all up. Then you can learn how to use ADO to connect to your database and execute a query that submits the data to create a new record. There is a lot to both parts, so if you have very little time, you might consider hiring some help. On the other hand, the Lounge contains tons of examples of how others have done the same thing in their applications, so if you have some time to study and learn and make a few mistakes along the way (how bad would it be if the database update is done wrong??), you've come to a good place.

  3. #3
    New Lounger
    Join Date
    May 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to submit data to SQL Server From Excel 20

    Thanks for the advice. I have the excel workbook created and data flow mapped out in english. I will look at the board to try to find the examples of how to use VBA/ADO to extract data and gather it all up. I only have about a week to make this happen. I am a web designer so I am a little out of my element here. Any other resources ideas? or how much would hiring some help cost?

    Thanks again.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA to submit data to SQL Server From Excel 20

    Do you work with ASP? There probably are a lot of ADO examples going from a web form to a database. (All of my examples go to MS Access, they don't let me write to SQL Server directly around here. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> ) I've found the WROX book ADO 2.6 Programmer's Reference very useful, but it's not a great starter book. Better for troubleshooting and optimization.

    The Excel board probably will have code examples for getting the data out of Excel, in addition to what you find here on this board. I try to program Excel only when my life depends on it (and I can't use Word), so I'm of little use there.

    As for hiring someone, people usually will contact you privately if they are available to help in a commercial way.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA to submit data to SQL Server From Excel 20

    ADO has fairly extensive help, with examples. To get it, do the following:
    - Activate the Visual Basic Editor (Alt+F11)
    - Select Tools | References...
    - Scroll down the list to find Microsoft ActiveX Data Objects 2.1 Library
    - Tick the check box for this reference, then click OK.
    You can now ask for help by typing a keyword and pressing F1. The Object Browser is also very useful: press F2 to activate it.

    The keyword for creating a new record is AddNew. If you type AddNew and press F1, you'll see the syntax. Click Example to get an example (select the Visual Basic one). The example shows how to connect to a SQL Server database, open a recordset and add a record.

    The same help is available on the MSDN site, e.g. AddNew Method (ADO).

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to submit data to SQL Server From Excel 2000 (VBA/excel 2000/sal d

    I like this site, many ADO exaples:

    http://www.erlandsendata.no/english/...php?t=envbadac
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    New Lounger
    Join Date
    May 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to submit data to SQL Server From Excel 2000 (VBA/excel 2000/sal d

    Thanks to everyone for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •