outlook custom form with command button not executing code

Outlook 2010.
vb script editor problem/issues

Custom form
Page "Survey" (p.2)

I have a command button on a custom  form.

I'm just trying the code out to see if i can get anything to execute. after pressing the command button.

I always get an error on this line:
Dim cnn As ADODB.Connection



Sub CommandButton2_Click()
  
Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection

   ' Open a Connection using an ODBC DSN named "Pubs".
   cnn.Open "ss_program_workflow", "webuser", "zwebuserprd"

   ' Find out if the attempt to connect worked.
   If cnn.State = adStateOpen Then
      MsgBox "Welcome to Pubs!"
   Else
      MsgBox "Sorry. No Pubs today."
   End If

   ' Close the connection.
   cnn.Close
End Sub

Open in new window



The only thing i can get to work is simple: hello world

Sub CommandButton2_Click()
  msgbox "Hello World"
End Sub


Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Najam UddinCommented:
Have you added reference Microsoft ActiveX Data Object library?
0
FordraidersAuthor Commented:
yes
0
Najam UddinCommented:
What is the error you get?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

FordraidersAuthor Commented:
expected end of statement
line 21

Remember i'm in "Script Editor" of the Custom Form

and line 21 is this
Dim cnn As ADODB.Connection
0
FordraidersAuthor Commented:
ok..

i did this:

Dim cnn
   Set cnn = New ADODB.Connection

got past my previous error:

got the msgbox

but now getting :
class not defined  ADODB
0
FordraidersAuthor Commented:
ok.

got it to work with this:
Dim cnn  
   Set cnn = CreateObject("ADODB.Connection")

Now, how do i declare recordset?
0
Najam UddinCommented:
You can get help from this
Sub adotest()

    Dim Cn As ADODB.Connection
    Dim Cm As ADODB.Command
    Dim Pm As ADODB.Parameter
    Dim Rs as ADODB.Recordset

    Set Cn = New ADODB.Connection
    Cn.Open "mystring"
    Set Cm = New ADODB.Command
    With Cm
        .ActiveConnection = Cn
        .CommandText = "SELECT * FROM table WHERE parentid=?;"
        .CommandType = adCmdText

        Set Pm = .CreateParameter("parentid", adNumeric, adParamInput)
        Pm.Value = 1

        .Parameters.Append Pm

        Set Rs = .Execute
    End With

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
ok i wil try it out..

It will take a few...
I will get back.,
Thanks
0
FordraidersAuthor Commented:
Dim cnn
   Set cnn = CreateObject("ADODB.Connection")

Dim rs1

dim connectstring
Dim tp

   ' Open a Connection using an ODBC DSN named "SS PROGRAM WORKFLOW".
 
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=dddddd025.com;UID=xxxxx;PWD=xxxxx;DATABASE=program_workflow "

sQuery = "Select * from temp_proposal_survey_results;"
 
 set rs1 = CreateObject("ADODB.Recordset")
 
              rs1.open sQuery, cnn, adOpenDynamic

tp =  rs1.recordcount

if tp = "" then
msgbox "hello recordcount"
end if

   ' Close the connection.
   cnn.Close

Not getting any errors but,,..not opening the recordset  either ??
0
FordraidersAuthor Commented:
finally...got this to work.
Const adUseClient = 3

Dim cnn
   Set cnn = CreateObject("ADODB.Connection")
Dim rs1
Dim connectstring
Dim tp
Dim q
   ' Open a Connection using an ODBC DSN named "SS PROGRAM WORKFLOW".
cnn.CursorLocation = adUseClient

cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=XXXXX.XXer.com;UID=XXXXXXuser;PWD=XXXXXuserprd;DATABASE=workflow"

sQuery = "Select * from [survey_results]"
Set rs1 = CreateObject("ADODB.Recordset")
rs1.open sQuery, cnn
rs1.MoveFirst
tp =  rs1.recordcount
If tp = 1 Then
MsgBox "One Record"
End if
   ' Close the connection.
   cnn.Close
Set cnn = nothing
0
FordraidersAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.