Changing VC++6 app database from Access to SQL Server - can I use linked tables?

Question

We have a Visual C++ 6 app that stores data in an Access database using DAO. The database classes have been made using the ClassWizard, basing them on CDaoRecordset.

We need to move from Access to SQL Server because some clients have huge (1.5Gb+) databases that are really slow to run reports on (using Crystal Reports and a different app).

We're not too worried about performance on this VC++ app - it is downloading data from data recorders and putting it in the database.

I used the "Microsoft SQL Server Migration Assistant 2008 for Access" to migrate my database from Access into SQL Server - it then linked the tables in the original Access database. If I open the Access database then I can browse the data in the SQL Server database.

I've then tried to use that database with my app and keep running into problems.

I've changed all my recordsets to be dbOpenDynaset instead of dbOpenTable. I also changed the myrecordsetptr->open() calls to be myrecordsetptr->open(dbOpenDynaset, NULL, dbSeeChanges) so that I don't get an exception.

But... I'm now stuck getting an exception 3251 - 'Operation is not supported for this type of object' for one of my tables when I try to set the current index using myrecordsetptr->->SetCurrentIndex(_T("PrimaryKey"));

Are there any tricks to getting the linked tables to work without rewriting all the database access code?

[UPDATE 17/7/09 - thanks for the tips - I'll change all the Seek() references to FindFirst() / FindNext() and update this based on how I go]

Answer 1

Yes, but I don't think you can set/change the index of a linked table in the recordset, so you'll have to change the code accordingly.

For instance: If your code is expecting to set an index & call seek, you'll basically have to rewrite it use the Find method instead.

Answer 2

Why are you using SetCurrentIndex when you have moved your table from Access to SQL Server?
I mean - you are using Access only for linked table.

Also, as per this page - it says that SetCurrentIndex can be used for table type recordset.

Answer 3

In what context are you using the command SetCurrentIndex? If it's a subroutine that uses SEEK you can't use it with linked tables.

Also, it's Jet-only and isn't going to be of any value with a different back end.

I advise against the use of SEEK (even in Access with Jet tables) except for the most unusual situations where you need to jump around a single table thousands of times in a loop. In all other DAO circumstances, you should either be retrieving a limited number of records by using a restrictive WHERE clause (if you're using SEEK to get to a single record), or you should be using .FindFirst/FindNext. Yes, the latter two are proportionally much slower than SEEK, but they are much more portable, and also the absolute performance difference is only going to be relevant if you're doing thousands of them.

Also, if your SEEK is on an ordered field, you can optimize your navigation by checking whether the sought value is greater or lesser than the value of the current record, and choosing .FindPrevious or .FindNext, accordingly (because the DAO recordset Find operations work sequentially through the index).