Link to home
Start Free TrialLog in
Avatar of curiouswebster
curiouswebsterFlag for United States of America

asked on

Creating my first DB connection in VBA, Excel 2007

If this code throws no error, does that mean it's opening a connection? If so, to what?

It stops nicely at a breakpoint and exits the function gracefully.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "combo change to WS2"
    Dim Myconnection As Connection
    Dim Myrecordset As Recordset
    Dim MyWorkbook As String

    Set Myconnection = New Connection
    Set Myrecordset = New Recordset
 
    'Identify the workbook you are referencing
    MyWorkbook = Application.ThisWorkbook.FullName

    'Open connection to the workbook
    Myconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & MyWorkbook & ";" & _
                  "Extended Properties=Excel 8.0;" & _
                  "Persist Security Info=False"



End Sub
SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of curiouswebster

ASKER

Thanks for the awesome code!

BUT, is there a way I can run a very simple query and NOT require a sproc? I am not too quick with T-SQL.
Is this table specific code?

Parameters.Append .CreateParameter("QueryTextParam", adVarChar, adParamInput, 10, StringValue)
      .Parameters.Append .CreateParameter("QueryTextParam", adDouble, adParamInput, , DoubleValue)
      .Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LongValue)
      .Parameters.Append .CreateParameter("QueryDateParam", adDate, adParamInput, , DateValue)
      .Parameters.Append .CreateParameter("QueryDateTimeStampParam", adDBTimeStamp, adParamInput, , DateTimeValue)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks!