?
Solved

Linking MS Project to Sql Server

Posted on 2011-09-13
5
Medium Priority
?
516 Views
Last Modified: 2013-11-15
Currently we have users using MS Project 2003 & 2007.  Our main business system resides in  a SQL Server 2005 db.  Is it possible to read data, from the SQL Server db inside MS Project?   We are specifically looking to keep dates in sync.  Dates are originally inputted in the business system and PM's enter them in MS Project as well... the trouble is keeping them in sync.  The dates can be volatile...so we are looking for a way to see if we can tie Project to the SQL db and somehow change them automatically in Project?  Sounds impossible to me but it would be wonderful if we could do that somehow...  Thanks.
0
Comment
Question by:snyperj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:Dr. Thomas Henkelmann
ID: 36537754
Hi,

have you thought about Projectserver already? With this ALL project data resides in a SQL Server anyway and Projectserver also offers an API to sync data from external systems e.g. your business system. If you don't want to go this way you need to start some custom development in Microsoft Project's VBA. You can of course read from SQL Server and update dates for specific tasks. However this needs to be triggered by the project manager after opening his/her project as there is literally NO reliable way to "remote control" Project and push the sync.

Hope this helps

Thomas
0
 

Author Comment

by:snyperj
ID: 36538678
Project Server is not an option.

"you need to start some custom development in Microsoft Project's VBA. You can of course read from SQL Server and update dates for specific tasks. However this needs to be triggered by the project manager after opening his/her project"

This is what I am interested in learning how to do in Project.  I am familiar with VBA from Access... just need to be pointed in the right direction.  Where can I find more info?
0
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 36545769
You may elect to store your VBA in Access.   Include the Project reference libraries in your VBA project.   This would permit an Access switchboard to be used to fire the code.
0
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 36545772
FYI, Project is an Access database (Jet)
0
 
LVL 12

Accepted Solution

by:
Dr. Thomas Henkelmann earned 2000 total points
ID: 36557440
Some really good basic stuff on Microsoft Project VBA can be found in http://zo-d.com/blog/archives/programming.html

I think you mostly need something like:
- connect to SQL Server: use the ADODB library in VBA, build a connectstring, create recordsets and run SQL queries to get your data
- run through all tasks in the project using the object model and set the desired task attribute(s) by running through the recordset (or start by running through the recordset, search for the related task and update it)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Online collaboration is quickly becoming embedded in the workplace, and its benefits are tangible. See what the current landscape looks like and what the future holds for collaboration tools and the future of work.
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question