none
Save Custom Form Data to SQL Server RRS feed

  • Question

  • Hello,

    I am creating a survey,using a custom form in Outlook 2007. Is there a way I can save the data to SQL Server?

    I have everything ready to go with the VBA code, but I am having a hard time figuring out how to make the data connection and then pass that data to a stored procedure that inserts the information to a table. I've tried different variations of ADO, but I can't get it right.

    Any help would be greatly appreciated.

    Thank you.

    ~J


    • Edited by Jenna_Fire Friday, October 11, 2013 7:44 PM added more info
    Friday, October 11, 2013 7:39 PM

Answers

  • As mentioned earlier - been far too long since I've played with the Script Editor and data access and unfortunately don't have the time to test things to definitively answer your question but somehow doubt that you would explicitly need to reference ADO on every machine (will gladly stand corrected on that if wrong - been a few years)

    That said, a little puzzled about why you are having trouble finding the right "connection string" if you've accessed your SQL server in VBA before - would be the same process. All comes down to what your configurations have and how you want/need to connect to your database - i.e. SQLNative Client / ODBC etc along with what version of SQL Server you're using which may or may not require the SQL backward compatibility components (not to ignore login credentials for the specific DB/Table etc etc )

    Beyond that, your questions are really VBA/SQL Server based and not sure what forum would be the most applicable for this combination.


    Karl Timmermans [Outlook MVP] "Outlook Contact Import/Export/Data Mgmt" http://www.contactgenie.com

    • Marked as answer by Jenna_Fire Wednesday, October 16, 2013 2:48 PM
    Monday, October 14, 2013 7:56 PM
  • >>Dim rst As ADODB.Recordset <<

    Just

    Dim rst
    
    Set rst= objConn.Execute (strQuery)

    or

    Dim rst
    
    Set rst = CreateObject("ADODB.Recordset")



    • Edited by Andy_mic Tuesday, October 15, 2013 3:08 PM
    • Marked as answer by Jenna_Fire Wednesday, October 16, 2013 2:48 PM
    Tuesday, October 15, 2013 3:06 PM
  • Yes.

    Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

    Direction

    Optional. A ParameterDirectionEnum value that specifies the type of Parameter object.

    refer to

    http://msdn.microsoft.com/en-us/library/windows/desktop/ms677209(v=vs.85).aspx

    http://msdn.microsoft.com/en-us/library/windows/desktop/ms678273(v=vs.85).aspx

    • Edited by Andy_mic Tuesday, October 15, 2013 4:51 PM
    • Marked as answer by Jenna_Fire Wednesday, October 16, 2013 2:47 PM
    Tuesday, October 15, 2013 4:49 PM

All replies

  • Your question deals with how to access/use SQL Server via VBA which is unrelated to Outlook specifically (other then retrieving Outlook field properties)

    If you do a google search using the phrase "accessing sql server using vba" (or any variation thereof) you will get numerous results pointing you to sample VBA code including correct connection strings etc

    One of the many results to get you started is

    How to write VBA to SQL Server sub routine that calls a Stored procedure
    http://custom-designed-databases.com/wordpress/2011/how-to-write-vba-sub-routine-that-calls-a-sql-stored-procedure/


    Karl Timmermans [Outlook MVP] "Outlook Contact Import/Export/Data Mgmt" http://www.contactgenie.com

    Monday, October 14, 2013 12:31 PM
  • Karl,

    I really appreciate your response. I understand that it is more of a VBA question, but here is where I thought it was more specific to Outlook: 

    Since this is a custom form, the only way for me to enter code is through the not-terribly-functional Script Editor on the custom form. Normally, I can and have used VBA and connect to SQL Server, but since this form is going to be published, I am not clear on how to add the Microsoft Active X Data Objects (so it will be part of the form not just Outlook on my computer) as per your link.  Will the users have that same reference when they receive the form? 

    Thank you.

    ~J

    Monday, October 14, 2013 3:31 PM
  • Been a very long time since I've used the script editor especially as it relates to data access. However, on a generic level, references made in code are done with the expectation that the target machines have the required components installed (ADO should be available by default in all cases when Office is installed and as long as the client's ADO lib is the same version or later then the one you're referencing - should be no issue).

    In the case of SQL - there are multiple ways to access the DB (ODCB, SqlOleDB, SQL Native Client) so, as a for instance, at a minimum you're going to need to make sure that SQL Native Client is installed if that's the route you take.

    SQL Server connection strings
    http://www.connectionstrings.com/sql-server/

    (also, as a side note, going to assume that you're dealing with someone opening the same survey more then once etc)


    Karl Timmermans [Outlook MVP] "Outlook Contact Import/Export/Data Mgmt" http://www.contactgenie.com

    Monday, October 14, 2013 6:13 PM
  • Let's say I write the code to use ADO as was shown in the link you provided. I would need to make the specific reference to the DLL. While the DLL is on everyone's computer, they are not going to make the reference in Outlook.

    Would me making the reference on my machine, publishing the form, alllow it to work on the other user's computers? My assumption is that it wouldn't. But I thought I would ask you because I still need to get through a bunch of hurdles before this can be published.

    I will definitely try some of the other connection strings you provided. In this organization, we all have the same basic installations so it should work - once I find the correct string.

    I really appreciate your quick and detailed responses. You have helped me out a great deal!

    Thank you.

    ~J


    Monday, October 14, 2013 7:34 PM
  • As mentioned earlier - been far too long since I've played with the Script Editor and data access and unfortunately don't have the time to test things to definitively answer your question but somehow doubt that you would explicitly need to reference ADO on every machine (will gladly stand corrected on that if wrong - been a few years)

    That said, a little puzzled about why you are having trouble finding the right "connection string" if you've accessed your SQL server in VBA before - would be the same process. All comes down to what your configurations have and how you want/need to connect to your database - i.e. SQLNative Client / ODBC etc along with what version of SQL Server you're using which may or may not require the SQL backward compatibility components (not to ignore login credentials for the specific DB/Table etc etc )

    Beyond that, your questions are really VBA/SQL Server based and not sure what forum would be the most applicable for this combination.


    Karl Timmermans [Outlook MVP] "Outlook Contact Import/Export/Data Mgmt" http://www.contactgenie.com

    • Marked as answer by Jenna_Fire Wednesday, October 16, 2013 2:48 PM
    Monday, October 14, 2013 7:56 PM
  • Just something to think about if you're using Office '2010 or earlier depending on what your overall requirements are which is to use the MS Access Data Collection function to send out a survey email and capture the info by getting the user to fill in the info and send it back. Would save all the aggravation of designing/using custom forms along with the associated code. The data collection functions are no longer available in Access '2013 but anything designed in '2010 or earlier would be handled correctly (as per published specs).

    Using the above process, data collected is saved in an MS Access database which can then be imported into SQL Server when everything is all said and done.


    Karl Timmermans [Outlook MVP] "Outlook Contact Import/Export/Data Mgmt" http://www.contactgenie.com

    Monday, October 14, 2013 8:13 PM

  • That said, a little puzzled about why you are having trouble finding the right "connection string" if you've accessed your SQL server in VBA before - would be the same process. All comes down to what your configurations have and how you want/need to connect to your database - i.e. SQLNative Client / ODBC etc along with what version of SQL Server you're using which may or may not require the SQL backward compatibility components (not to ignore login credentials for the specific DB/Table etc etc )

    When I use the same code that worked in "straight VBA" I get errors such as "Expected end of statement."

    The VB in the form appears to be a variation of regular VB. A line such as:

     Dim rst As ADODB.Recordset  

    returns an "Expected end of statement" error.

    Tuesday, October 15, 2013 2:46 PM
  • >>Dim rst As ADODB.Recordset <<

    Just

    Dim rst
    
    Set rst= objConn.Execute (strQuery)

    or

    Dim rst
    
    Set rst = CreateObject("ADODB.Recordset")



    • Edited by Andy_mic Tuesday, October 15, 2013 3:08 PM
    • Marked as answer by Jenna_Fire Wednesday, October 16, 2013 2:48 PM
    Tuesday, October 15, 2013 3:06 PM
  • Andy_mic,

    Thank you for that. 

    I've been googling and having a hard time finding some examples. Would you know of any that show passing parameters to a stored procedure?

    Tuesday, October 15, 2013 3:32 PM
  • Do you mean call a stored procedure and pass some parameters in VBA?

    If so, you can search "vba call stored procedure with parameters" or "vba execute stored procedure with parameters" on google. There are many results or samples talking about this.

    Here is a sample from MS KB:

    http://support.microsoft.com/kb/185125

    Tuesday, October 15, 2013 3:43 PM
  • Thank you again. 

    OK. I think I found the issue and >I know I am getting into strict VBA stuff now. When I did this:

       Set p = cmd.CreateParameter("@inputParam", adVarChar, adParamInput, 5, "ALFKI")

    I get this error:  "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"

    Then I found that you have to use a 1, instead of the adParamInput. 

    Maybe this is considered VBScript?

    Tuesday, October 15, 2013 3:53 PM
  • Yes.

    Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

    Direction

    Optional. A ParameterDirectionEnum value that specifies the type of Parameter object.

    refer to

    http://msdn.microsoft.com/en-us/library/windows/desktop/ms677209(v=vs.85).aspx

    http://msdn.microsoft.com/en-us/library/windows/desktop/ms678273(v=vs.85).aspx

    • Edited by Andy_mic Tuesday, October 15, 2013 4:51 PM
    • Marked as answer by Jenna_Fire Wednesday, October 16, 2013 2:47 PM
    Tuesday, October 15, 2013 4:49 PM
  • Andy_mic and Karl,

    Thank you both so much for your patience, time and sharing your knowledge. I really needed this guidance and it was a great help. You saved me so much time and taught me a great deal also.

    I really appreciate all your help.

    ~J

    Wednesday, October 16, 2013 2:47 PM