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

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 <m...@xxxxxxxxxxx> wrote:

-----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 <at> earthlink <decimal-point> net
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-----

.

Relevant Pages

  • Re: Parameters Question
    ... OleDB the syntax for calling a stored procedure is the same as the ODBC ... The .NET Framework Data Provider for SQL Server does not support the ... In this case, you must use named parameters, as in the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Parameters Question
    ... Also as I said in my post, the reason I sometimes use OLEDB for both Access ... and SQL Server is so I can upsize the Access database to SQL Server and use ... Do you have an example of using named parameters with OLEDB for both Access ... It will work with question mark placeholders. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: oledbconnection v sqlconnection
    ... The OleDb provider is a one-size-fits-all data access interface that uses ... No two database engines have the same SQL so you'll have to ... actual low-level data access layer and exposes a common object layer that ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Really want a answer, is there a mature way to access different data providers without performan
    ... I dll can support both SQL server and mysql, ... > You can use OleDB and ODBC also. ... > direct adapter as SqlClient. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Using SEEK on a TADODataSet
    ... MS SQL Server OLEDB provider does not support indeces (they are used when ... Only one MS OLEDB ... http://www.oledbdirect.com - The fastest way to access MS SQL Server, ... > SELECT * FROM CAPACITY ...
    (borland.public.delphi.database.ado)