powerpoint vba to get data from SQL

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

Code:

---

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

---

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

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

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

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.

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

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

Code:

---

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.

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

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.

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:

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

---

Code:

---

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

---

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.

Code:

---

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

---

Code:

---

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

---

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

And just the one round-trip to the database :yes

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

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

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 ?

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.

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