Opening a Project in SQL Server Using VBA

[Dustin Ventin]

I have an Access program that I'm upgrading from using an Access mde
front-end and mdb back-end to using a SQL Server back-end. This database
includes the tables and information neccisary to hold Microsoft Project
files, and I was able to write extensive VBA to manipulate and use the data
using some simple code, such as the following to open the MSP file:

ProjApp.FileOpen Name:=strPath, ReadOnly:=False, FormatID:="MSProject.MDB8"

Now, of course, the path "strPath" no longer works, because what's required
is a connection the the SQL Server. I figured this should be simple, and
used the macro VBA from Project of opening a project stored in my SQL Server
database. The code that came back was:

ProjApp.FileOpen Name:="<{SQL Server}>\Project1", ReadOnly:=False,
UserId:="sa", DatabasePassWord:="xyzzy49", FormatID:="MSProject.ODBC"

When I run this from Access, a box comes up from Microsoft Project and asks
for the name of the server. The problem is

1) When I type in the name of the server, it errors out and says "Project
cannot open the file."

2) I don't want to prompt the user, I want to replace the "{SQL Server}" bit
with the connection string, if I could.

Does anyone have any experience with this, or ideas on how to make this work?

Thank you,

Dustin

 

[Rod Gill]

Recoding a macro of you opening the project from SQL Server should provide
what you want. You need to specify not only the name of the Server (can be
(local) ) but also the name of the database.

Note that Project 2007 does not allow saving to database other than thru
Project Server.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Dustin Ventin]

I would have thought so, too, but what I got back from the macro was:

FileOpen Name:="<{SQL Server}>\Project1", ReadOnly:=False, UserId:="sa",
DatabasePassWord:="xyzzy49", FormatID:="MSProject.ODBC"

Now, admittedly I'm using a preset ODBC data source, but what I need is to
be able to place the actual server path and database name in the spot where
it says:=<{SQL Server}>. Now, I've gotten pretty close, but I'm still not
sure how Project is expecting this network path, server name, and database
name. So far, what I have is:

FileOpen Name:="<\\ServerName\SQLServerName\DatabaseName>\Project1",
ReadOnly:=False, UserId:="sa", DatabasePassWord:="zzzzzz",
FormatID:="MSProject.ODBC"

But this code causes an error that says "Data source not found", so I'm
assuming that's not the right path format.

Any help you could provide is most appreciated.

Dustin

:="<{SQL Server}>

 

[Rod Gill]

{SQL Server} may well be the name of a DSN entry. That would hold the Server
and db name.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Dustin Ventin]

That is possible, but it's not a DSN entry that I set up. The DSN entry I'm
using when I record the macro is "SQLEXPRESS Connection". Trouble is, when
the macro records, it puts down "SQL Server" between the '{}' brackets.

Now, I would expect that at the very least, using the recorded line of code:

FileOpen Name:="<{SQL Server}>\Project1", ReadOnly:=False, UserId:="sa",
DatabasePassWord:="xyzzy49", FormatID:="MSProject.ODBC"

Would at least WORK, but it doesn't. As I mentioned, it causes a small form
to appear asking for the server name and database name, which is what I'm
trying to do programatically. Even if I the provide this information through
the form, it comes up with another error and says: "Project cannot open
file", which does not appear when I'm actually recording the macro.

I'm sorry I'm being such a stickler about this, but I really appreciate your
help. I've been all over the Internet looking for ideas on how to fix this,
and I'm on an ever-tightening schedule, so your assistence is very highly
appreciated.

 

[Dustin Ventin]

Alright, I figured it out! The proper format is:

FileOpen Name:="<DSN Name>\Project1", ReadOnly:=False, UserId:="SQL Login
Name", DatabasePassWord:="SQL Login Password", FormatID:="MSProject.ODBC"

Also, it's important to make sure that the DSN has been created as a SYSTEM
DSN, not a file DSN. My mistake!

Thank you again for your time and effort.

Dustin

 

[DPingger]

I' ve been trying to do the same thing with VBA, Rod, without any success.
The following command still asks for the password. Is there anyway around it?

FileOpen Name:="C:\Documents and Settings\gordopi\My Documents\J-UCAS
Sched\IMS-UCAS-D\Statusing File\Jan2009Analysis\SchedTrial.mpp",
ReadOnly:=False, FormatID:="MSProject.MPP", Password:="zzzxyr"

TIA

DPingger

 

[DPingger]

Rod,

I've been trying to do the same thing using VBA but the following command
doesn't do it. It still asks for a password like it wasn't provided in the
command. Is this even possible?

FileOpen Name:="C:\Documents and Settings\gordopi\My Documents\J-UCAS
Sched\IMS-UCAS-D\Statusing File\Jan2009Analysis\SchedTrial.mpp",
ReadOnly:=False, FormatID:="MSProject.MPP", Password:="absdre"

TIA

DPingger

 

[DPingger]

Rod,

I've been trying to do the same thing using regular VBA but it still asks
for the password. is this even possible?

FileOpen Name:="C:\Documents and Settings\gordopi\My Documents\J-UCAS
Sched\IMS-UCAS-D\Statusing File\Jan2009Analysis\SchedTrial.mpp",
ReadOnly:=False, FormatID:="MSProject.MPP", Password:="asdfa"

TIA

Dpingger