Access

[Nick]

I have searched, and almost found what I need (using an Access DB to
populate a PowerPoint Presentation). Most posts seem to point to the MSKB
article discussing the Kiosk Option, and another post gave great code
example using ADO to pull data from an access DB where a combo-box is used
to limit data in a list box. Unfortunately I cannot seem to get these to
work for what I need.

My organization has a weekly staff meeting discussing all kinds of metrics
which are stored in our access DB. This staff meeting is orchestrated
through a pre-done PowerPoint presentation which is updated by hand each
week. It is important to note that this presentation also includes other
announcments not in the DB. BUT it is safe to assume that slides 1-5 each
contain data from the DB. Which means that I cannot "create" a new
presentation each week because others have "static" slides to include.

So I guess what I am envisioning is that I have a presentation where the
first 5 or more slides "grab" the results of a query from my DB, and are
updated "on the fly". It seems that it should be easy to set a list box to
grab data through a connection, but doing so has proven to be very hard. I
guess if I can figure out how to do it ONCE, can replicate it over and
over....

Slide "1"
contains a listbox named:
"lstPeople"
which gets a list of all people from
manning.mdb, tblPeople, column "people"

Any ideas?

VR,
Nick Phillips

(e-mail address removed)

 

[Nick]

So, any ideas from anybody here?

 

[Kurt]

Hi Nick,
Have a look at this: www.take-off.as/datapoint
Maybe it helps you but it is not working for listboxes but you can link
dynamically to access databases.
Kurt

 

[Brian Reilly, MS MVP]

I posted this a while back. You could have gottten it off Google.

It works with a database I have in Access

Option Explicit
Private Sub cboCustomer_Change()
On Error GoTo Err_Handler
Dim strSql As String
Dim AdoCnn As ADODB.Connection, AdoRs As ADODB.Recordset

If IsNull(Me.cboCustomer.Value) Then Exit Sub
If Trim(CStr(Me.cboCustomer.Value)) = "" Then Exit Sub

'Get a connection to database
Set AdoCnn = GetDatabaseConnection()
If AdoCnn Is Nothing Then
Exit Sub
End If
AdoCnn.Open

strSql = "select [Project Name] from [tblProject] where [Customer
ID]=" & Me.cboCustomer.Value
'Instantiate ADODB Recordset object
Set AdoRs = New ADODB.Recordset
'Open Recordset
AdoRs.Open strSql, AdoCnn, adOpenForwardOnly, adLockOptimistic
Me.lstProjects.Clear

'Populate Projects listbox
Do While Not AdoRs.EOF
Me.lstProjects.AddItem AdoRs![Project Name]
AdoRs.MoveNext
Loop
'Close Recordset object
AdoRs.Close

'Close ADODB Connection Object
AdoCnn.Close

Set AdoRs = Nothing
Set AdoCnn = Nothing

Exit Sub
Err_Handler:
MsgBox Err.Description, vbInformation
End Sub

Private Sub cboCustomer_DropButtonClick()
On Error GoTo Err_Handler
Dim strSql As String
Dim AdoCnn As ADODB.Connection, AdoRs As ADODB.Recordset

'Check if customer dropdown is already populated
If Me.cboCustomer.ListCount > 0 Then Exit Sub

'Get a connection to database
Set AdoCnn = GetDatabaseConnection()
If AdoCnn Is Nothing Then
Exit Sub
End If
AdoCnn.Open

strSql = "select [Customer ID], [Cust Name] from [tblCustomer]
order by [Cust Name]"
'Instantiate the ADODB Recordset object
Set AdoRs = New ADODB.Recordset
'Open Recordset
AdoRs.Open strSql, AdoCnn, adOpenForwardOnly, adLockOptimistic
Me.cboCustomer.Clear

'Populate the first value (blank) of the Customer dropdown
Me.cboCustomer.AddItem "0"
Me.cboCustomer.Column(1, Me.cboCustomer.ListCount - 1) = ""
'Populate the Customer dropdown with the list of values form the
database table
Do While Not AdoRs.EOF
Me.cboCustomer.AddItem AdoRs![Customer ID]
Me.cboCustomer.Column(1, Me.cboCustomer.ListCount - 1) =
AdoRs![Cust Name]
AdoRs.MoveNext
Loop
'Close Recordset Object
AdoRs.Close

'Close the Connection object
AdoCnn.Close

Set AdoRs = Nothing
Set AdoCnn = Nothing
Exit Sub
Err_Handler:
MsgBox Err.Description, vbInformation
End Sub

Private Function GetDatabaseConnection() As ADODB.Connection
Dim dbPath As String
Dim AdoCnn As ADODB.Connection

Set GetDatabaseConnection = Nothing
On Error GoTo Err_Handler

dbPath = ActivePresentation.Path
If VBA.Right(VBA.Trim(dbPath), 1) <> "\" Then
dbPath = dbPath & "\"
End If
dbPath = dbPath & "CustomerSolutions.mdb"

Set AdoCnn = New ADODB.Connection
AdoCnn.Provider = "Microsoft.JET.OLEDB.4.0"
AdoCnn.Properties("Data Source") = dbPath
AdoCnn.Properties("Jet OLEDBatabase Locking Mode") = 1
AdoCnn.CursorLocation = adUseServer

Set GetDatabaseConnection = AdoCnn
Exit Function
Err_Handler:
MsgBox Err.Description, vbInformation, "Get Database Connection"
End Function

Brian Reilly, PowerPoint MVP

 

[John Langhans [MSFT]]

[CRITICAL UPDATE - Anyone using Office 2003 should install the critical
update as soon as possible. From PowerPoint, choose "Help -> Check for
Updates".]
[TOP ISSUE - Are you having difficulty opening presentations in PowerPoint
that you just created (you can save, but not open)? -
http://support.microsoft.com/?id=329820]

Hello,

If none of the suggestions provided give you the functionality that you
were looking for or, if you (or anyone else reading this message) have
suggestions for how and why you think PowerPoint should provide this
functionality (or make it easier), don't forget to send your feedback (in
YOUR OWN WORDS, please) to Microsoft at:

http://register.microsoft.com/mswish/suggestion.asp

It's VERY important that, for EACH wish, you describe in detail, WHY it is
important TO YOU that your product suggestion be implemented. A good wish
submssion includes WHAT scenario, work-flow, or end-result is blocked by
not having a specific feature, HOW MUCH time and effort ($$$) is spent
working around a specific limitation of the current product, etc. Remember
that Microsoft receives THOUSANDS of product suggestions every day and we
read each one but, in any given product development cycle, there are ONLY
sufficient resources to address the ones that are MOST IMPORTANT to our
customers so take the extra time to state your case as CLEARLY and
COMPLETELY as possible so that we can FEEL YOUR PAIN.

IMPORTANT: Each submission should be a single suggestion (not a list of
suggestions).

John Langhans
Microsoft Corporation
Supportability Program Manager
Microsoft Office PowerPoint for Windows
Microsoft Office Picture Manager for Windows

For FAQ's, highlights and top issues, visit the Microsoft PowerPoint
support center at: http://support.microsoft.com/default.aspx?pr=ppt
Search the Microsoft Knowledge Base at:
http://support.microsoft.com/default.aspx?pr=kbhowto

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

 

[Nick]

I have the code posted prior. I guess I am attempting to do something that
is harder than I originally thought. I have now figured out how to connect
to a DB through ADO, grab the required data using SQL, and display it in a
PPT slide. Now, I am trying to think of creative way to span data across
many slides (as many as it might take to display all the data for a
particular subject). So, to recap as of right now using a text box I can
populate it with data from a database, but does anybody have any ideas on
other ways to put the data on a slide, or in the least methods I might use
to show a lot of data aross many slides?

 

[Steve Rindsberg]

PPT slide. Now, I am trying to think of creative way to span data across

many slides (as many as it might take to display all the data for a
particular subject). So, to recap as of right now using a text box I can
populate it with data from a database, but does anybody have any ideas on
other ways to put the data on a slide, or in the least methods I might use
to show a lot of data aross many slides?

Powerpoint won't break your data up across multiple slides for you, so you'll
pretty much need to do it yourself.

Knowing the size and interline/interparagraph spacing of the text box you'll
drop the text into, you may be able to calculate its height for a given number
of lines of text. Once it exceeds a predetermined "target" height, you could
advance to the next slide, so to speak.


--
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================
Featured Presenter, PowerPoint Live 2004
October 10-13, San Diego, CA www.PowerPointLive.com
================================================