Faster export of records from Access to Project?

[dgmoore]

I have a process that exports large numbers of records from Access to
Project. I'm currently creating the import map on the fly in Access
using MapEdit, and then importing the data using FileOpen.

This works fine, but I now have a recordset with more than 10,000
records and it takes nearly an hour to load the records into Project.
The data I'm exporting goes to MSP_TASKS, MSP_FLAG_FIELDS, and
MSP_DATE_FIELDS. Can anybody suggest a different, and faster, way of
getting this data into Project?

Thanks
Dave

 

[John]

I have a process that exports large numbers of records from Access to
Project. I'm currently creating the import map on the fly in Access
using MapEdit, and then importing the data using FileOpen.

This works fine, but I now have a recordset with more than 10,000
records and it takes nearly an hour to load the records into Project.
The data I'm exporting goes to MSP_TASKS, MSP_FLAG_FIELDS, and
MSP_DATE_FIELDS. Can anybody suggest a different, and faster, way of
getting this data into Project?

Thanks
Dave

dgmore,
Wow that does sound like a slow process, especially since you are only
exporting 3 field types. I've never worked between Access and Project
but if I had that kind of performance I would first try using an import
map from Project. If that didn't work any better, I would use VBA.

Just a suggestion.

John
Project MVP

 

[dgmoore]

John,

This application is based on VBA in Access: I use Project's FileOpen
method on a table in Access using an import map that I create on the
fly in Access using the MapEdit method. This process is fast enough
with up to several hundred records, but the monster 10,000 records I'm
importing really causes the whole thing to choke. The process runs
every day and generates several thousand Gantt gifs which are published
to web pages, allowing us to present current schedule info on hundreds
of projects on the company intranet.

Dave

 

[Rod Gill]

How about just doing a file, saveas to the access database. It will export
all data, but it will be as efficient as possible. You can then create Views
to limit the data your software uses to read the MSP_ tables.

--

Rod Gill
Project MVP
Visit www.msproject-systems.com for Project Companion Tools and more
Project VBA Book now in stock: visit www.projectvbabook.com

 

[dgmoore]

Rod,
I know I can save mpp as mdb, but I'm not aware of a way to save mdb as
mpp. The data doesn't exist in Project until I import it from SQL
Server via Access VBA - it's the trip from Access to Project that is
the problem.

Dave

 

[John]

John,

This application is based on VBA in Access: I use Project's FileOpen
method on a table in Access using an import map that I create on the
fly in Access using the MapEdit method. This process is fast enough
with up to several hundred records, but the monster 10,000 records I'm
importing really causes the whole thing to choke. The process runs
every day and generates several thousand Gantt gifs which are published
to web pages, allowing us to present current schedule info on hundreds
of projects on the company intranet.

Dave

Dave,
I wonder if a VBA macro based in Project would have the same speed
issue. It might be worth a try although if the process has to generate
thousand of GIFs, I would expect it to take some time.

Just an observation. It is hard to imagine that more than a few of the
Gantt chart graphics change each day so re-generating all of them each
day seems like a waste.

John

 

[Rod Gill]

So why not save it in the format defined in the pjdb.htm file? That way the
project opens nearly as fast as when opening any project of similar size
from a .mdb file?

This will be slower because Project normally just reads from the Binary
fields, however it should be faster then importing via a map.

--

Rod Gill
Project MVP
Visit www.msproject-systems.com for Project Companion Tools and more
Project VBA Book now in stock: visit www.projectvbabook.com

 

[dgmoore]

John,

You're quite correct that many - in fact, the majority - of the Gantts
do not change from day to day, and acutally nobody ever looks at lots
of them, but there are two issues: 1) the current date gridline on the
Gantts is required to coincide with the calendar date; and 2) because
of the nature of the data, it is not easy to determine what data has
changed from day to day. The process runs unattended and requires no
intervention, so producing the Gantts redundantly doesn't really cost
anything, but the slow import is eating up a lot of server time and
interferes with other things that need to run.

Dave

 

[Rod Gill]

Maybe there is another way to go about this. How about adding a timestamp to
each SQL record and only import and merge changed records since the last
import?

--

Rod Gill
Project MVP
Visit www.msproject-systems.com for Project Companion Tools and more


Project VBA Book now in stock: visit www.projectvbabook.com

 

[dgmoore]

I'd have to do the timestamping and comparison externally to the SQL
Server db because I do not control it or write access to modify (e.g.,
timestamp) records. I think the best I could do would be to compare key
data fields - there aren't too many of those - from day to day and see
if anything has changed, but that still doesn't solve the problem of
having the current date gridline on the Gantt in sync with the calendar
date. If I can get the customer to ease up on that requirement, we
could certainly save time and eliminate unproductive duplication of
Gantts from day to day. I think I'll look into that possibility.

Thanks
Dave

 

[John]

John,

You're quite correct that many - in fact, the majority - of the Gantts
do not change from day to day, and acutally nobody ever looks at lots
of them, but there are two issues: 1) the current date gridline on the
Gantts is required to coincide with the calendar date; and 2) because
of the nature of the data, it is not easy to determine what data has
changed from day to day. The process runs unattended and requires no
intervention, so producing the Gantts redundantly doesn't really cost
anything, but the slow import is eating up a lot of server time and
interferes with other things that need to run.

Dave

Dave,
Let me just read back your last statement to you. "... so producing the
Gantts redundantly doesn't really cost anything, but the slow import is
eating up a lot of server time and interferes with other things that
need to run."

If you believe time is money, and in the business world it is, then that
statement directly contradicts itself (i.e. the redundancy DOES cost
something and probably a lot more than the powers that be realize). Make
your case, and get some control of this inefficient process.

John

 

[Bill Bordiuk]

This may be stupid (but stupid often helps). Do you have automatic
calculation turned on? MSP will recalc the project on every task you import,
if you do. That would account for your decease in performance with large
recordsets.

Bill B

 

[JackD]

Have you read this article:
http://zo-d.com/blog/archives/microsoft-project/microsoft-project-performance-limitations.html
It might give you some idea of how performance varies exponentially with
file-size. Maybe working with smaller sub-projects would help?
One thing which can make a big difference is to turn off auto-calc in
project.

-Jack

 

[dgmoore]

Thanks Gents,

Auto-calc is off. The data doesn't lend itself to separation into
sub-projects; anyway, I think if I split the recordset into smaller
chunks the transfer time for all of the chunks would probably add up to
the same amount I'm currently experiencing, if not more.

Dave

 

[JackD]

Then your last resort is to throw hardware at it.
More memory would be the first thing I'd try.
-Jack Dahlgren

 

[Rod Gill]

How about a message "No change since last published" then you don't need to
update the image? You only need to import projects that have changed. That
would reduce the data to transfer and communicate clearly which schedules
have not been updated, which can be important information in its own right.

--

Rod Gill
Project MVP
Visit www.msproject-systems.com for Project Companion Tools and more

NEW!! Project VBA Book now in stock, for details visit:
www.projectvbabook.com