Moving Data From Access to Powerpoint

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In an effort to provide more accurate metrics on work production, I am trying
to create and integrate a production database (which will show production on
several different areas for different production types). At the moment I
have some dummy data in a single table so that I can develop on that that
table and move from there. The slides that I want to populate with
information have several elements, but I am unsure of how to get the data
from Access to PowerPoint in a form that I know how to use.

I am comfortable with Arrays and other (non-database) data types, and I am
pretty quick on the uptake, but I am having a difficult time with this one.
I had originally expected that I could open a new Access Application Object,
Open the database that I was using, Run a query on the table and put the
result into a dynamically dimensioned Array. However I was unprepared for
the plethora of new object types and expressions, Recordsets, ADO objecs and
the like.

Also I originally intended to initiate the procedure from Powerpoint, but
now I am starting to reconsider. Perhaps the best way would be to send the
data from Powerpoint, and have Access execute code in the powerpoint
presentation.

Anyone know of a good jumping off point or of a good way to put
multi-field/multirecord data into a data object where it can be manipulated a
little other than pulling it into Excel and then into Powerpoint?

I am using Office XP(2002) on a network that doesnot have access to the
internet, and any add-ins will have to go through a long process of
adjudication to be put on the netowrk. However coding can be easilly done.
Any takers?

Cory
 
Each Office application has an object model and can be manipulated by another
Office application using "Automation" (used to be called COM). It happens
that Powerpoint has one of the more difficult object models to use. That is
possibly because Microsoft did not originally write Powerpoint, they acquired
it and folded it into Office.
If you are not familiar with using Automation, you have a steep learning
curve, but it can be done. It will take a significant volume of VBA code.
A shortcut to consider is to create your presentations in Access reports and
output them as snp (SnapShot) files. snp files can easily be imported into
Powerpoint as slides.

If you want to have a go at automation, you will need to add Microsoft
Powerpoint to your VBA References. Then, for starters, open the Object
Browser and have a look at the Powerpoint objects and their methods and
properties.
 
I have dabbled in what I believe you are talking about. I have several excel
workbooks and Word Documents that share information and formatting back and
forth amongst each other, instantiate each others applications and open
files/navigate the filesystem.

I ended up deciding to have the bulk of the code work in Access. Since the
actual data will be residing there, user will use a form to initiate
"preparing the data" which will put it into a seperate table, which will then
feed that information into the powerpoint presentation. Unless the Snapshot
files work similar to slides in that they have independant shapes that can be
manipulated, I will have to forgo that. User wants to be able to override
the data that is automagically inserted to the slides if they want to. (Kind
of defeats the purpose of automating if you ask me. If you have to change
the data, then the program is not working the way it should.... Or you are
cooking the books...)

Cory
 
The Snap Shot idea will not work for your. It is a static image of a report
and they could not tweek the data. You will have to play with the
Automation idea.
I do agree, if managers just want accurate data, they will always want every
task as automated as possible. If they want to be able to manipulate the
data, you can bet it is to make it look as good as possible.

I worked one place where they did not want any reports as Access reports.
Every report had to be an Excel spreadsheet. Many of these reports were for
the purpose of presenting to the client to prove what a great job we were
doing. Need I say more?
 
I am happy to report that through the use of two books: Access VBA
Programming and Mastering the Relational Database Language, I have made this
a reality. All of the major functions work in the code (of course right now
I am only sending two data elements to th powerpoint presentation). I ended
up using SELECT and UPDATE queries in nested By-Month, By-Region, and
By-Product For Loops.

The entire operation conducts 864 SELECT queries (In a Recordset.Open) and
takes the Recordset.Recordcount (or whatever it is, can't remember off-hand)
and UPDATEs another table with the values. A seperate module takes the
values in that table and creates a new instance of Powerpoint, Opens a copy
of the blank presentation template, and fills it with the data in the table.

Next is going to be a different part. Updating the Chart opjects with some
of that data also. But I am told that there is a special abject model for
doing just that.

Thanks again for your assistance.

Cory
 
If you really are using the RecordCount property, recognize that it's not
guaranteed to be correct unless you move to the last row of the recordset
first.

If all you're doing is getting row counts, you might be better off using
Select Count(*) ... to return a single row with a single field that you can
retrieve, rather than returning a recordset containing all of the rows.
 
So then I could do something like:

Dim strResultVal as String
Dim SQLStatement as String
SQLStatement = "SELECT COUNT(*) " & _
"FROM 'Table' " & _
"WHERE MONTH(TableReviewedDate) = 06
strResult = DoCmd.RunSQL SQLStatement
SQLStatement = "UPDATE Table2 " & _
"SET ReviewedRecs = '" & strResult & "' " & _
"WHERE Month = 'Jun'"
DoCmd.RunSQL SWLStatement


Or how would I store the returned number of records?

Cory
 
You can't use RunSQL with Select statements: RunSQL is only for Action
queries (INSERT INTO, UPDATE, DELETE)

You either need to continue using a recordset:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lngRecordCount As Long
Dim strSQL As String

Set dbCurr = CurrentDb()
strSQL= "SELECT COUNT(*) " & _
"FROM TableName " & _
"WHERE MONTH(TableReviewedDate) = 06"
Set rsCurr = dbCurr.OpenRecordset(strSQL)
lngRecordCount = rsCurr.Fields(0)
' Do whatever you need to do with the recordcount

or simply to use DCount:

Dim lngRecordCount As Long

lngRecordCount = DCount("*", "TableName", "MONTH(TableReviewedDate) = 06")
 
Back
Top