Error 2147217900 Invalid Table Name "Tasks" when opening ADOrecordset.

[Andy Thomas]

I have some VBA run from MS Excel that will loop through sub projects
on a MS Project 2000 .mpp file, and opens a ADO recordset for the
tasks on a sub project.

The process will run OK until, at a random sub plan, the run time
error (ref as above) 'Invalid Table Name "Tasks"' is generated when
the the code tries to open the recordset. Previous posts have
indicated this could be to do with the SQL statement being used to
open the recordset, but I know this can't be the case as a recordset
can be opened up to the point the error occurs.

There doesn't seem to be any pattern to when the error is thrown
either - on occasions the process can run through succesfully, then at
other times the error will be generated.

Any help in understanding the cause would be appreciated.

Extracts of code are below;
======================================================================
Sub ProcessRecordset(prname as String)

Dim rsTasks As Object
Dim strLocalPlanName As String
Dim strSQL As String

'Set SQL statement for recordset containing tasks
strSQL = "SELECT
Project,TaskName,TaskOutlineNumber,TaskFinish,TaskText1,TaskNotes,"
strSQL = strSQL &
"TaskPercentComplete,TaskUniqueID,TaskText5,TaskText7,TaskFlag1,TaskFlag2,TaskFlag3,"
strSQL = strSQL &
"TaskFlag4,TaskText9,TaskText25,TaskText26,TaskText27,TaskText28,TaskText29,TaskSummary"
strSQL = strSQL & ",TaskOutlineLevel,
TaskStart,TaskOutlineLevel ,TaskOutlineLevel,TaskOutlineLevel,TaskDuration
FROM Tasks where TaskOutlineNumber <> '0'"

Set cnsubplan = cnplan(prjname)
Set rsTasks = CreateObject("adodb.recordset")
Call rsTasks.Open(strSQL, cnsubplan)

'Process recordset

rsTasks.Close
cnsubplan.Close

Set rsTasks = Nothing
Set cnsubplan = Nothing

End Sub


==========================================================
Public Function cnplan(strPlanname As String) As Object
'Sets up a connection to a plan

strConn = "Provider=Microsoft.Project.OLEDB." & strVersion & ";PROJECT
NAME=" & strPlanname

Set cnplan = CreateObject("adodb.connection")
cnplan.Open (strConn)

End Function

==========================================================

 

[Bob Barrows [MVP]]

Good luck, but this is the very first post I have seen anywhere involving
the use of ADO with Project. Hopefully someone with that experience will
read your post, but I would not get your hopes up.

I did a google search and came up with these results:
http://www.google.com/search?source...e=utf8&q=ADO+Project+VBA+"Invalid+Table+Name"

 

[Rod Gill]

HI,

This group is closing soon, so please post in the developer group in future.

To answer your question, if the code works for some files and not others,
then the file may have a corruption. With 2000 I've forgotten what save as
formats you have. If you can save to html then open from html, use it
otherwise you may have to save to mpx which will lose some accuracy of
detail.

keep your original file of course and remember to delete the sub-project
from the master first then insert the new version.

You may also get better results using the oledb driver directly:
Tools, References in the VBE and select Microsoft Data Objects 2.8 (or the
highest version you have)

Now use the following:
dim conn as ADODB.Connection
rs As ADODB.Recordset
conn.Open "Provider=Microsoft.Project.OLEDB.9.0;Project
Name=FilePath.mpp;"
rs.Open strSQL, conn

Note project 2003 finally got the OLEDB driver right (all time phased data
read accurately) so of course in 2007 the oledb driver is no more!@#$#$@!
--

Rod Gill
Microsoft MVP for Project

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

 

[Bob Barrows [MVP]]

HI,

This group is closing soon, so please post in the developer group in
future.

Just to clarify, since this OP was cross-posted to .data.ado, it's the
project.vba group that's closing, not the .data.ado group, correct?

 

[Rod Gill]

Hi Bob,

Correct, project.vba is closing soon, one day, maybe after the 10th request
from project MVPs, maybe when oil hits $140/ barrel, no wait it did that
once already, sigh....!

--

Rod Gill
Microsoft MVP for Project

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

 

[Andy Thomas]

Thanks for your responses.

I wanted to avoid referencing the oledb driver directly as I wanted
this to be portable and not need users to have to ensure they have the
references in VBA.

Anyhow, just to see if this made a difference I tried it ; it didn't
resolve the issue.

It's not a file corruption as sometimes the process works for a file,
then at another time it the exact same file generates the error. I've
even restarted my machine twice in identical fashion with absolutley
the same applications running, run the script and still get
inconsistent results. All very strange!

 

[Rod Gill]

Does it work on project 2002 or later? It may be a bug in the 2000 oledb
driver.

If Project is installed on a PC, then the Project oledb driver is also
installed (except for 2007).

--

Rod Gill
Microsoft MVP for Project

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

 

[Gary L. Chefetz [MVP]]

Andy:

The error code looks like SQL is rejecting the connection.

--

Gary L. Chefetz, MVP
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com