B
Bill Ehrreich
I know that similar questions have been asked many times, but I'm still
unclear about my options, so I would like to ask again.
I'm building an application in MS Access 2000 that, among other things,
will display (query) information from a myriad of MS project files on a
corporate server. This information should be up-to-the-minute as opposed
to refreshed daily or weekly.
If the company were using MS Project server, this would all be easy. I'd
just write a few stored procedures to get the project information I
wanted from the Project Server SQL database and display the results in
my application. Unfortunately, the company isn't using Project Server.
I need to display information that cuts across multiple project files.
Therefore, with my limited knowledge of MS Project, I see my options as
follows:
1. Keep a table listing the physical location of each MS project (MPP)
file. Loop through the appropriate files and by using VB automation,
open, extract data and close each file. My feeling is that this will be
slow and not very flexible. Also, a few experiments have shown that I
will often get unexpected prompts when opening a file (i.e., resource
file, macro security) that could bring my loop to a halt.
2. Keep an MS Access version (mdb) in parallel with the project file
(MPP). Place some code into each project file (MPP) so that when the
project is updated and saved, an MS Access file is overwritten. The
BeforeClose event on the ThisProject object would be fine for this. To
query the projects, loop through the same project list as in #1, but
open the mdb files instead and use ADO to run a SQL query of each
project mdb. Aggregate the data from each project and display.
I suppose an alternate to 2 would be to do away with the MPP files
entirely and use only the MDB files to store the project information.
Users could open the MDB files in MS project and perhaps wouldn't know
the difference. I'm not sure.
This method seems better than 1 because I can use SQL and therefore
easily change the way the projects are queried. Nevertheless, looping
through a list of mdb files, opening and closing is probably not very
efficient.
3. Save each project (MPP) to one central mdb or SQL server database,
perhaps again using the BeforeClose event of the project. Whenever a
project is updated, the appropriate information is updated in the
central mdb or SQL Server DB. This would make things almost as easy as
Project Server. However, I'm not sure how to tell a given project file
to update its own data in a central database. It seems as if I can only
append new data (therefore creating a duplicate set) or overwrite the
entire db. In fact, how can Project A know which data in a central db is
Project A data and make the necessary deletions/insertions/updates? I
don't see any unique project key generated when data is stored in this way.
If someone can tell me how to get #3 working, suggest a better
alternative or simply lead me in the right , I would be very grateful.
Thanks.
Bill Ehrreich
Hollywood, FL
unclear about my options, so I would like to ask again.
I'm building an application in MS Access 2000 that, among other things,
will display (query) information from a myriad of MS project files on a
corporate server. This information should be up-to-the-minute as opposed
to refreshed daily or weekly.
If the company were using MS Project server, this would all be easy. I'd
just write a few stored procedures to get the project information I
wanted from the Project Server SQL database and display the results in
my application. Unfortunately, the company isn't using Project Server.
I need to display information that cuts across multiple project files.
Therefore, with my limited knowledge of MS Project, I see my options as
follows:
1. Keep a table listing the physical location of each MS project (MPP)
file. Loop through the appropriate files and by using VB automation,
open, extract data and close each file. My feeling is that this will be
slow and not very flexible. Also, a few experiments have shown that I
will often get unexpected prompts when opening a file (i.e., resource
file, macro security) that could bring my loop to a halt.
2. Keep an MS Access version (mdb) in parallel with the project file
(MPP). Place some code into each project file (MPP) so that when the
project is updated and saved, an MS Access file is overwritten. The
BeforeClose event on the ThisProject object would be fine for this. To
query the projects, loop through the same project list as in #1, but
open the mdb files instead and use ADO to run a SQL query of each
project mdb. Aggregate the data from each project and display.
I suppose an alternate to 2 would be to do away with the MPP files
entirely and use only the MDB files to store the project information.
Users could open the MDB files in MS project and perhaps wouldn't know
the difference. I'm not sure.
This method seems better than 1 because I can use SQL and therefore
easily change the way the projects are queried. Nevertheless, looping
through a list of mdb files, opening and closing is probably not very
efficient.
3. Save each project (MPP) to one central mdb or SQL server database,
perhaps again using the BeforeClose event of the project. Whenever a
project is updated, the appropriate information is updated in the
central mdb or SQL Server DB. This would make things almost as easy as
Project Server. However, I'm not sure how to tell a given project file
to update its own data in a central database. It seems as if I can only
append new data (therefore creating a duplicate set) or overwrite the
entire db. In fact, how can Project A know which data in a central db is
Project A data and make the necessary deletions/insertions/updates? I
don't see any unique project key generated when data is stored in this way.
If someone can tell me how to get #3 working, suggest a better
alternative or simply lead me in the right , I would be very grateful.
Thanks.
Bill Ehrreich
Hollywood, FL