CreateWorkspace function doesn't work

RupertA
RupertA used Ask the Experts™
on
I have a VB project done in VB6 which connects to an ODBC database via DAO.

Certain clients complain that the connection to the database fails and others report success. This depends on which parts of Office they have installed. A good example is if you have Excel 97 and the Data Controls part of Excel installed it works, uninstall it and no connection.

I have found that the VB code in the application bombs out at this line.

Set wrkActivate = CreateWorkspace(ODBC_WORKSPACE_NAME, "", "", dbUseODBC)

Thats right it is this 'VB CreateWorkspace function' which is a legitimate way to access the database using DAO. So as you can see I am using the ODBCDirect version of DAO to try and access Data sources through ODBC. As I am beginning to understand it, DAO is very much linked to Office and there can be several different version of DAOs installed by various versions of Microsoft Applications. Therefore it is not much of a surprise that I am experiencing problems for example when removing Excel 97 and its Data control. The CreateWorkspace function doesn't work.

I obviously don't want this to happen and seeing as people can use say either office 97/2000 and have installed whichever components they want then a developer must have experienced this problem at some time.

There must be a VB expert who can help me out here. Otherwise you would have people using this DAO connectivity for their programs and continually being told by random clients that it doesn't work for them.

Surely someone can advise me here. Now as far as I understand DAO is a common component which is available to Office and other apps. I would like someone to explain to me what I need to package with my application to make it work regardless if a user decides to uninstall Excel 97 or Excel 2000 or whatever part of Office. Or if anyone tell me what I can do to guarantee success.

I'm pretty desperate for help as the program has not been released and a frustrated client is having meetings to discuss the problem.  I can help out with any further info.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DAO is becoming more and more unsupported.  Try distributing the DAO access DLL's with your  project.  Then if this does not work try recoding a bit... my suggestion ultimately is using the latest version of ADO, which is much more supported and much quicker.

which dao version are you using
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Try installing MDAC 2.7 and JET 6 with your application, May be it could fix the problem.

Author

Commented:
Hi westbrooknewmedia,

I was exporting it with DAO 3.5. Installshield automatically detects that it needs DAO350.dll and  DAO2535.tlb. Should I be exporting it with anything else?

Basker,

I've tried different versions of MDAC and that made no difference.
Commented:
After many fruitless/gruelling hours I solved the problem myself and I am writing it here for others with a similar problem to benefit.

For anyone having problems of a DAO connection to a database working on some computers and not on others then I would look at these three steps but my solution was in step 3 (worked out by myself). Steps 1 & 2 are merely there so you can eliminate them as the cause of the problem.

1. Make sure the DAO (Data Access Object exists) exists in the first place. Download and run dao35.exe. say http://www.castlesoft.co.nz/Downloads.htm.

2. Make sure you have MDAC. Download MDAC 2.5.

3. Now I had done points 1 & 2 and my data connection still wouldn't work on certain machines. I used InstallWatch Pro 2.5 to see what was making the difference between the connection existing when Excel is installed and it not working when getting rid of it. The answer and solution is that if your machine did not have the dll 'MSRDO20.dll' in Windows/System before Office is installed then Office will put it on. This dll is crucial for your DAO connection. If you follow the logic then when you uninstall say Excel it removes the dll and your connection won't work. For your application make sure you install MSRDO20.dll  and register it and your problems are over.

So to conclude the reason why a DAO connection doesn't work when you remove certain parts of Office is that it is getting rid of MSRDO20.dll which you need.

There must be people out there who have had the problem and not solved it, so I hope this solution will help someone out there!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial