Re: Cross Database Join, C++ program set up confusion

Don't understand you. Whatever fields you request in the SELECT are the
returned columns regardless as to whether it is a JOIN, cascaded SELECT
or
simple SELECT. Do you mean updating columns?

I meant for returning columns, where is the data returned to if there
is no recordset?

Well what kind of data are you returning if it is not in Recordset?
I am still not entirely with you.
Several points on this:

(i) If you are doing a Connection Execute or Command Execute and the SQL is
an action statement (like INSERT, UPDATE, DELETE or similar and no Rowsets
are returned), you can give a parameter to ADO which indicates that no
Recordset is returned (it is fractionally faster as ADO does not waste time
constructing a Recordset). The number of records affected can also be
obtained for an action query.
(ii) If you wish to return parameters from a Stored Procedure or a RETURN
value, you can do that via a Command Object without returning a Recordset.
Note, the Jet Provider for Access does not support Output parameters. The
SQLServer Provider does support Output parameters.
(iii) If you have a singleton SELECT that returns at most 1 row of data, no
more, you can open a Record object. This came in for MDAC 2.6. It avoids the
overhead of constructing a Recordset object. I believe all fields are
fetched back as Output parameters. I have done this. It is 15% faster than
using a singleton SELECT Recordset.

Does that answer what you mean?

I have to be able to join access and SQL Server tables, and
I can't qualify tables that haven't already been
linked or qualified, and most aren't.

I don't understand this. If the default database is db1 on the
Connection
and there is a db2, db3 on the server I can issue the SQL command via
Connection Execute

"insert into db2.dbo.table1 select * from db3.dbo.table2"

No recordset needed and it is fast.
It is not a function of ADO.
It is a function of SQL Server.

With SQL Server, the only time you need external links is if the
database is
located on a _different_ server. There is no problem working with
multiple
databases on the _same_ server.
With Access, it is more primitive and you need a link every time to any
other database.

Yeah, that's my problem. I have four or five servers with SQL Server
installed that I need to run joins on.

You can do this with mutiple Recordsets in ADO but it such a slow painful
solution I would not think about it.

All the big databases make this relatively painless. For SQL Server what you
want to make use of is the system stored procedure : sp_addlinkedserver. For
a particular SQL Server, you register all the other external database
servers (and they dont have to be SQL Server they can be Access, Oracle)
with itself. Having done that you can issue SQL commands and your local SQL
Server will go and fetch the data for you do whatever JOINs you want to do
etc. An ADO Recordset will just see the rowset results. Far better and
painless. Almost management free.

And I think all the major databases have the equivalent of
sp_addlinkedserver.
Note, I would rather have SQL Server fetch data from an Access database than
have Access fetch data from SQL Server.

Cheers

Stephen Howe


.

Relevant Pages

  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Re: Knowledge Base Help Not working
    ... Database is a DAO object. ... both Access 2000 and 2002 use ADO. ... Microsoft DAO 3.6 Object Library, ... For example, to ensure that you get a DAO recordset, you'll need to ...
    (microsoft.public.access.formscoding)
Loading