Calling stored procedure on MS SQL Server via DAO/VB6

Posted on 2010-01-12
Medium Priority
Last Modified: 2012-05-08
I just inherited a project that is written in VB6. The application was using Sybase for it's data store.  We're converting it to use MS SQL Server 2008 so a .NET web application can interact with the data as well. the whole application uses DAO for it's data interactions. Simple ad-hoc queries work fine but when calling stored procedures that do things such as delete or update I get invalid cursor location errors when trying to use any data that is returned from the SP.

Does any one know of the limitations with DAO and MS SQL stored procedures that might cause this and potentially have a fix?

Thanks :)
Question by:used2could
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
LVL 38

Accepted Solution

PaulHews earned 100 total points
ID: 26296777
DAO works well with Access and some other file based databases, but may not be ideal for working with SQL Server.  DAO is also pretty ancient, and it is hard to find answers and support.  Personally I would migrate the code to ADO.

LVL 13

Assisted Solution

game-master earned 100 total points
ID: 26299867

DAO doesn't expose many of the capabilities that you could use if working directly with ODBC API functions, etc. For example, you can't perform asynchronous queries or connections using DAO, nor can you work with multiple result sets.

I suggest migrate it to ADO, ADO.NET in the future..


Author Comment

ID: 26323145
Oh how i would love migrating to ADO or even convert to .NET but that isn't an option at the time. The original developer didn't separate DB calls into a data access layer. It's a mess of ad-hoc + SP sql.

I did find out what was causing the problem. There is a limitation in DAO when a stored procedure runs a delete or update. DAO can't do client side cursors  with MS SQL so the expected position is invalid.

I'm just going to have to break the calls up into individual steps.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

764 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