Visual Studio 2010 & SQL Server 2000? Yes, You Can!!!

I think I earn this week's paycheck on this one. I've been writing a project in Visual Studio 2010 for several weeks, only to discover that Microsoft stopped supporting SQL Server 2000 in April of 2008. Huh?

All research I've done on the web comes back to the same answer: Either redo the app in Visual Studion 2005/2008 or upgrade the database. Now, Visual Studio is not going to import a project from a newer version, I sure as heck do not want to have rewrite the app, and the company isn't spirited to upgrade the database.

So, here is the solution (just the overview. If you're hurting bad enough from this situation, you'll figure out the rest):

Open up a new MS Access database (I used 2007).
Link the tables you want from the SQL Server database to MS Access, what this does is allows Access to use the data as sort of a pass-through, so it can manipulate data in SQL Server 2000.

Close the database (save it where you can find it, probably with your project).

Use Visual Studio's ODBC connectivity to hook up the Access database to your project. And there, sitting pretty, are your SQL Server tables.

Awesome, huh?

I don't know if Access will create bottle necks, but as it's a pass-though probably not. Will be interesting to follow the thread to see how it's working

:)

If I recall some objects in the SQL DB don't work well with the ODBC connection - but I'm not 100% which ones.

I might remember someone hitting brickwalls everytime they encountered a field name with a space in it.

I think Access provides for that.

[ ] will allow the use of spaces in table names.

No - that is not what I meant.

Bugs in the provider meant those fields were useless - could not pass through the underlying layer of the ODBC provider (I'm sure I've got a thread on the forum here from 5 years ago about this).

Didn't matter if you used []'s or not - the provider choked.

That's what you will encounter when you start using older unsupported technologies to handle your current needs.

[edit] this thread needs to be moved to the .net section of the forum - it's not a DB post ;) [/edit]

I guess when it's all told. This fix is not anywhere near perfect. After all it's a half-assed work around.

But desparate time require desparate measures. At least until the company is ready to plunk down the cash and endure the joys of an upgrade.

:)