Access linked tables to ODBC in C++/ADO application

[Bret Kuhns]

I recently started a co-op/internship at a company and they are
looking to migrate a large legacy supported application from OLEDB to
SQL Server. I'm doing prelim work in experimenting with the options
available. The application is huge (1+ million lines of C++ code), so
it'd be quite a bit of man hours to adapt all the database code for
SQL Server. I stumbled upon linked tables in an OLEDB file to an ODBC
source (such as SQL Server) and it appears promising as an initial
transition as it requires no code change (ideally, anyway).

I've got the table data moved to a locally running SQL Server and
linked the tables in the Access database. All the queries and table
data are working properly in Access. I launched our application with
this modified database file and the application immediately crashes
with a 0xC0000005 memory access violation. I traced the debug
information down to an assert in low-level Microsoft Foundation
Classes (MFC) code. We're using ADO (/not/ .net) in C++ as our DB
layer.

Is what I'm attempting to do possible? I've seen examples online of VB
handling linked tables in an Access database just fine, so I'm not
sure why I'm getting errors instantly. Any help would be more than
appreciated, thanks!

[MGFoster]

Bret Kuhns wrote:

I recently started a co-op/internship at a company and they are
looking to migrate a large legacy supported application from OLEDB to
SQL Server. I'm doing prelim work in experimenting with the options
available. The application is huge (1+ million lines of C++ code), so
it'd be quite a bit of man hours to adapt all the database code for
SQL Server. I stumbled upon linked tables in an OLEDB file to an ODBC
source (such as SQL Server) and it appears promising as an initial
transition as it requires no code change (ideally, anyway).

I've got the table data moved to a locally running SQL Server and
linked the tables in the Access database. All the queries and table
data are working properly in Access. I launched our application with
this modified database file and the application immediately crashes
with a 0xC0000005 memory access violation. I traced the debug
information down to an assert in low-level Microsoft Foundation
Classes (MFC) code. We're using ADO (/not/ .net) in C++ as our DB
layer.

Is what I'm attempting to do possible? I've seen examples online of VB
handling linked tables in an Access database just fine, so I'm not
sure why I'm getting errors instantly. Any help would be more than
appreciated, thanks!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

OLEDB is just a protocol to run DBs (like ODBC). IOW, OLEDB could be
used to link to SQL Server (so I don't understand your problem). The
OLEDB server provider name for SQL Server is SQLOLEDB.

If you want you can link Access (really JET db engine) tables directly
to SQL Server using the SQL Server's Linked Servers (under the Security
menu in the SQL Enterprise Manager). Then treat the Jet tables like SQL
Server tables. The reference to the tables is:

linked_server_name.catalog.schema.object_name

Ex:

AccessDB...table_name

Where "AccessDB" is a name I applied when I linked the access DB to SQL
Server; and the table_name should be whatever the real table name is.

Since Access doesn't have a named catalog, nor schema name, just use the
periods (dots) without the names.

I'm suprised that they original designers of the C++ code didn't make a
class for connecting to the data source. That way all you'd have to do
is replace that class with a class that connects to SQL Server. I
believe I saw something like this on Microsoft's web site - it was
pretty cool how they compacted everything into a few classes. I believe
there is some code that you can download for these classes. Try the MSN
network.

Good luck
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSMBn3oechKqOuFEgEQL14wCgg9D11Th6Odmi626A0+R/4oeRwp4AoL8O
XhEp/r8C4CtIqlUYQT34v8Eg
=PCOo
-----END PGP SIGNATURE-----

[Bret Kuhns]

Thanks for the advice. For now, I would like to see how viable it is
to stay inside an actual Access OLEDB file. The reason is that the
application uses around 200 stored queries (some parametrized) in the
database for a lot of operations instead of writing SQL statements in
the code itself. I suppose this probably looked like a good idea when
they initially designed it, but now it's difficult to migrate to SQL
Server as the migration tools I've used can't convert about 150 of the
queries to SQL Server stored procedures and/or views. So the idea
behind staying with a .MDB file is that the queries remain in
the .MDB, but are run against linked tables to the SQL Server ODBC
source. I know that OLEDB is just the standard protocol, and that SQL
Server supports an OLEDB layer that should be easy to transition to.
However, with the query issue I mentioned, it's not going to be easy
to move to SQL Server without rewriting a lot of code and if it's
avoidable, the company would love to take an alternative route.

I suppose my question is focused mainly on how to programmatically
access linked tables in a .MDB OLEDB file. Is it identical to
accessing local tables in the file itself, or would code need to be
changed? If it still requires a change, we might as well just do a
full transition to SQL Server and ignore the linked table idea.

Thanks again for your help!
On Sep 4, 6:57*pm, MGFoster <[e-mail]:

>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

OLEDB is just a protocol to run DBs (like ODBC). *IOW, OLEDB could be
used to link to SQL Server (so I don't understand your problem). *The
OLEDB server provider name for SQL Server is SQLOLEDB.

If you want you can link Access (really JET db engine) tables directly
to SQL Server using the SQL Server's Linked Servers (under the Security
menu in the SQL Enterprise Manager). *Then treat the Jet tables like SQL
Server tables. *The reference to the tables is:

* *linked_server_name.catalog.schema.object_name

Ex:

* *AccessDB...table_name

Where "AccessDB" is a name I applied when I linked the access DB to SQL
Server; and the table_name should be whatever the real table name is.

Since Access doesn't have a named catalog, nor schema name, just use the
periods (dots) without the names.

I'm suprised that they original designers of the C++ code didn't make a
class for connecting to the data source. *That way all you'd have to do
is replace that class with a class that connects to SQL Server. *I
believe I saw something like this on Microsoft's web site - it was
pretty cool how they compacted everything into a few classes. *I believe
there is some code that you can download for these classes. *Try the MSN
network.

Good luck
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. *I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSMBn3oechKqOuFEgEQL14wCgg9D11Th6Odmi626A0+R/4oeRwp4AoL8O
XhEp/r8C4CtIqlUYQT34v8Eg
=PCOo
-----END PGP SIGNATURE-----

[MGFoster]

Bret Kuhns wrote:

Thanks for the advice. For now, I would like to see how viable it is
to stay inside an actual Access OLEDB file. The reason is that the
application uses around 200 stored queries (some parametrized) in the
database for a lot of operations instead of writing SQL statements in
the code itself. I suppose this probably looked like a good idea when
they initially designed it, but now it's difficult to migrate to SQL
Server as the migration tools I've used can't convert about 150 of the
queries to SQL Server stored procedures and/or views. So the idea
behind staying with a .MDB file is that the queries remain in
the .MDB, but are run against linked tables to the SQL Server ODBC
source. I know that OLEDB is just the standard protocol, and that SQL
Server supports an OLEDB layer that should be easy to transition to.
However, with the query issue I mentioned, it's not going to be easy
to move to SQL Server without rewriting a lot of code and if it's
avoidable, the company would love to take an alternative route.

I suppose my question is focused mainly on how to programmatically
access linked tables in a .MDB OLEDB file. Is it identical to
accessing local tables in the file itself, or would code need to be
changed? If it still requires a change, we might as well just do a
full transition to SQL Server and ignore the linked table idea.

Thanks again for your help!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Let me see if I got this correct: You have all your data in an Access
..mdb file and your going to put that data into an SQL Server DB. Then
you're going to run the C++ application using the queries in the current
Access DB. You are using ADO's OLEDB provider to connect from the C++
program to the Access DB. Is this correct?

If correct: You can link SQL Server tables to an Access .mdb file using
ODBC and run Access queries against those tables. If your current C++
code runs the Access queries I don't believe you'll have to change
anything.

If you decide to convert the Access queries to SQL Server stored
procedures/views you'll have to change the C++ code. The SQL Server OLE
DB provider name is spelled SQLOLEDB. You'll also have to change the
whole ADO set up for the SQL Server db.

--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSMGmGoechKqOuFEgEQJ2iQCfZSbf3UfTnzc5Q0wPHo5KwB 8MBeUAmwXv
3WI2deaMAcfwrFw+R8m6gdcn
=rK21
-----END PGP SIGNATURE-----