powerpoint vba to get data from SQL

[sanath_p7]

I am using Powerpoint 2013. I have written the following VBA code from powerpoint which should automatically list a combobox based on the SQL query. But nothing is showing up when I execute. Can somebody help ?


Option Explicit

Private Sub ComboBox21_Change()


     Dim conn As ADODB.Connection
     Dim rs As ADODB.Recordset
     Dim cs As String
     Dim query As String
     Dim row As Integer

     Set conn = CreateObject("adodb.connection")
     Set rs = CreateObject("adodb.recordset")

     cs = "Provider=SQLOLEDB;Data Source=ausitsmprd02trial.com;Initial Catalog=ServicesAnalysts;Integrated Security=SSPI;"

     'parameters here are connectionSring, username, password
     'you will need to put the actual username and password in
     'quotes here for this code to work.
     conn.Open cs

     query = "select top 10 * from dbo.[DimPerson]"
     rs.Open query, conn

     row = 0
     Do Until rs.EOF
        Debug.Print rs.Fields("PersonName").Value
       rs.MoveNext
     Loop

     rs.Close
     Set rs = Nothing

     conn.Close
     Set conn = Nothing

End Sub

[Bob Phillips]

Cross-posted at StackOverflow http://stackoverflow.com/questions/2...-data-from-sql

[Bob Phillips]

I did a simple test, amended your code to one of my databases, and it output the details fine.

[sanath_p7]

Thanks XLD. Can you please share with me the amended code that you used.
Did the data get populated into combobox/textbox ?

[Bob Phillips]

By amended, I mean that it was essentially your code with the Source and Initial Catalog in the connection string changed to my server and database. So nothing to see, the code works.

[sanath_p7]

Hi xld,

I tried that once again but could not figure it out. I checked the Tools> References to be sure ADO is selected.
Could you please attach the code & the database that worked for you.

Are you using userform from power point. Any rowsource field that you are mentioning?

I am sure it will be helpful that lots of other folks who want to see this truly amazing tasks of powerpoint vba.

Regards,
SKP

[Bob Phillips]

The database was just a simple table of dimPerson, with PersonID, PersonName, and eMail, with 12 rows of data. The code was just yours with this line changed

cs = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=VBAXTest;Integrated Security=SSPI;"

I ran it in PowerPoint, but in a code module not from a userform. I am not sure where rowsource comes into it.

[sanath_p7]

hi xld,

That's exactly my problem too. I execute the code and it does not give any error.
But I am not able to see where the data has been populated as nothing is shown in the combobox.
I tried both code module and userform and still could not get to see the data being populated.Was it the same in your case too?

Regards,
SKP

[Bob Phillips]

The code puts the output into a recordset, and then just shows it using debug.print (are you seeing those values). You need to add extra code to put it into your combobox, it doesn't just happen.

[sanath_p7]

hi xld,

I finally got one that could populate in combo box using the UserForm. Now I want to use a textbox which will show up the values based on the selection in combo box?

Code:

Private Sub UserForm_Initialize()
    On Error GoTo UserForm_Initialize_Err
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=C:\ProjectStatusDetails.accdb"
    rst.Open "SELECT ProjectName FROM ProjectStatusDetail;", _
             cnn, adOpenStatic
    rst.MoveFirst
    With Me.ComboBox1
        .Clear
        Do
            .AddItem rst![ProjectName]
            rst.MoveNext
        Loop Until rst.EOF
    End With
UserForm_Initialize_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
UserForm_Initialize_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume UserForm_Initialize_Exit
End Sub

[Bob Phillips]

Private Sub ComboBox1_Change()
    Me.TextBox1.Text = Me.ComboBox1.Value
End Sub

[sanath_p7]

My mistake. I put the question incorrectly. What[COLOR=#333333] I want to achieve is to use a textbox which will show up other values based on the selection in combo box?
Ex. In the combo box I have selected [ProjectName]. Now in the textbox I want the [ProjectManager] name to be showed up.
[ProjectManager] is another field in the same database.

Option Explicit

Public Sub UserForm_Initialize()
    On Error GoTo UserForm_Initialize_Err
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=C:\ProjectStatusDetails.accdb"
    rst.Open "SELECT ProjectName FROM ProjectStatusDetail;", _
             cnn, adOpenStatic
    rst.MoveFirst
    With Me.ComboBox1
        .Clear
        Do
            .AddItem rst![ProjectName]
            rst.MoveNext
        Loop Until rst.EOF
    End With
    
    ComboBox1.ListIndex = 0
    
UserForm_Initialize_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
UserForm_Initialize_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume UserForm_Initialize_Exit
End Sub


Sub ComboBox1_change()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=C:\ProjectStatusDetails.accdb"


    rst.Open "SELECT [PMName] FROM ProjectStatusDetail where [ProjectName] ='" & Str(Me.ComboBox1.Value) & "'", cnn, adOpenStatic
 
If rst.EOF = False Then
Me.TextBox2.Value = rst!PMName
Me.TextBox3.Value = rst![PMCoachName]
Else
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
End If

rst.Close
Set rst = Nothing
Set cnn = Nothing

End Sub

[Bob Phillips]

Option Explicit

Public Sub UserForm_Initialize()
    On Error GoTo UserForm_Initialize_Err
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=C:\ProjectStatusDetails.accdb"
    rst.Open "SELECT ProjectName, PMName, CoachName FROM ProjectStatusDetail;", _
             cnn, adOpenStatic
    rst.MoveFirst
    With Me.ComboBox1
        .Clear
        Do
            .AddItem rst![ProjectName]
            .List(.ListCount - 1, 1) = rst!PMName
            .List(.ListCount - 1, 2) = rst!CoachName
            rst.MoveNext
        Loop Until rst.EOF
    End With
    
    ComboBox1.ListIndex = 0
    
UserForm_Initialize_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
UserForm_Initialize_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume UserForm_Initialize_Exit
End Sub

Sub ComboBox1_change()
    With Me.ComboBox1
    
        Me.TextBox2.Text = .List(.ListIndex, 1)
        Me.TextBox3.Text = .List(.ListIndex, 2)
    End With
End Sub
b

[sanath_p7]

XLD - > Excellent. That code is working fine with the userforms.

[Bob Phillips]

And just the one round-trip to the database

[sanath_p7]

hi,

My Last Question on this:
When I am trying to use the same code without UserForms I am getting the following error message :


-2147467259 Error ODBC Microsoft Access Driver: The database has been placed in a state by an unknown user that prevents it from being opened or locked.
I checked the MS KB article id : 307640. But is there a way to execute without the sample error handler.




Option Explicit


Private Sub ComboBox1_change()


On Error GoTo Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim errorcount As Integer

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Direct Reporting Module\ProjectStatusDetails.accdb"
rst.Open "SELECT ProjectName, PMName, PMCoachName FROM ProjectStatusDetail;", _
cnn, adOpenStatic
rst.MoveFirst
With Me.ComboBox1
'.Clear
Do
.AddItem rst![ProjectName]
.List(.ListCount - 1, 1) = rst!PMName
.List(.ListCount - 1, 2) = rst!PMCoachName
rst.MoveNext
Loop Until rst.EOF
End With

ComboBox1.ListIndex = 0
With Me.ComboBox1

Me.TextBox2.Text = .List(.ListIndex, 1)
Me.TextBox3.Text = .List(.ListIndex, 2)
End With


Initialize_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub

Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume Initialize_Exit


End Sub

[Bob Phillips]

If you are not using userforms, why are you trying to use userform controls, that is obviously a no-no.

[sanath_p7]

Ok. I have the powerpoint main template with pre-built information. So I want to use 1 combobox & 3 textbox on one of the slide without using the userforms. The 1 combobox & 3 textbox will not be part of the userform but I have inserted them into the slide.
Is it not possible ?

[Bob Phillips]

I'm no PowerPoint programmer, and I have no desire to become one, and this would require ActiveX controls (yuk!), and application events I think - so I am out of here.

You seem to have no more idea than me on PowerPoint programming, so why not stick to the form, it works.

[sanath_p7]

Well Friends,
The userform did not go well with my executives and they wanted me to use only ActiveX. So the solution I used was :

Option Explicit
Public Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
Dim rst
Set rst = CreateObject("ADODB.Recordset")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\ProjectStatusDetails.accdb"
rst.Open "SELECT ProjectName FROM ProjectStatusDetail;", _
cnn, adOpenStatic
rst.MoveFirst
With Me.ComboBox1
.Clear
Do
.AddItem rst![ProjectName]
rst.MoveNext
Loop Until rst.EOF
End With

ComboBox1.ListIndex = 0

UserForm_Initialize_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub




Sub ComboBox1_change()
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
Dim rst
Set rst = CreateObject("ADODB.Recordset")

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\ProjectStatusDetails.accdb"

rst.Open "SELECT [Scope],[Time],[Cost],[Quality],[StartDate],[FinishDate] FROM ProjectStatusDetail where [ProjectName]= '" & ComboBox1.Value & "'", _
cnn, adOpenStatic


If rst.EOF = False Then
Me.TextBox1.Value = rst![Scope]
Me.TextBox2.Value = rst![Time]
Me.TextBox3.Value = rst![Cost]
'Me.TextBox4.Value = rst![Quality]
Me.TextBox5.Value = rst![StartDate]
Me.TextBox6.Value = rst![FinishDate]
Else
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
'Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
End If


End Sub