Problem With MSAccess And Executing A DTS Package Via VBA - Returns Column Headings Only

I wonder if anyone can help with this. I have searched around and not found much on the topic which can resolve my problem.

History on the reason for using VBA and not C# or VB or VB.net etc. Whn I arrived at this company, there were no development tools so had to resort to Access and VBA. Of course, the routines I wrote are now set in production and I haven't had much time to convert them to c# and ASP.NET (which I have learnt over the past couple of years).

Anyway the problem is that I found on the SQLDTS.com site that I could in fact create and execute DTS packages within VB and followed the examples and got this working in VBA. Of course I created the DTS first and then saved it as VB, then used the resulting code with the VBA project etc etc. This worked well and we were happy.

The DTS packages I create and execute are to export data from SQL2000 tables to tabs in Excel spreadsheets.

Now I find that on my machine, when the objPkg.execute command runs, the Excel file is created but only the query column headings are populated into the excel file. No data is returned.

If I run this on a different machine it works (although having said that, the other machine is now starting to display similar behaviour on some routines).

we created a version of the Access 2000 MDB in Access 2003 some time ago and the Access 2000 still behaves correctly. The strange thing is that my machine has only started doing this incorrectly a little while ago (not sure how long now but could be as little as a couple of months). The routines are monthly processes so it's only going to show up as a problem once a month so the fact that it used to work on my machine and has only failed the last couple of times means that something must have changed on my setup. I am runningXP-Pro SP2 - fully patched, Office Pro 2003 and talking to SQL 2000 SP3a

is it possible that some windows updates or office updates are clobbering something which used to work?

If I allow the VBA to create the DTS but not execute it, I can execute the created DTS manually from SQLEM and it outputs data correctly.

No security access to the SQL databases/table has changed, nor have there been any changes to security on the file system where the files are written.

It's weird.

Any help would be gratefully accepted.

Annabel



I'm really lost and not sure whether I should look at driver versions, ODBC versions blah blah