Re: linked tables

From: Mark J. McGinty (mmcginty_at_spamfromyou.com)
Date: 12/23/04 

Date: Thu, 23 Dec 2004 01:59:14 -0800

"Jon Rizzo" <jrizzo at langan dot com> wrote in message
news:OuGAJYG6EHA.824@TK2MSFTNGP11.phx.gbl...
> Yes, when I try to access the linked table, I get this error message:
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'MyDatabase'
> failed.
>
>
> If I import the same table rather than linking it, the code works fine.
> What do I need to do in order to access this linked table?
>
> Jon

Personally I would create the link in the opposite direction, define a
linked server in SQL Server, then access the Jet table with a 4-part name
(actually two of the parts will be blank because Jet has neither object
ownership nor catalog constructs) e.g.,

SELECT * FROM [mydatabase].[owner].[sql_table] s INNER JOIN
[linked_server_to_jet]..[jet_table] j ON (s.jet_id = j.id)

Place-holders:
    [mydatabase] -- SQL Server database in which [sql_table] resides
    [owner] -- SQL account that owns the table
    [sql_table] -- Do we really need this definition?

    [linked_server_to_jet] -- Name assigned to linked server object in SQL
Server
    [jet_table] -- Table that resides in MS Jet (.MDB) database file

I know that way will work, plus you'll then be working with the SQLOLEDB
provider, instead of the one for Jet.

You might be able to work it the other way by creating a querydef based on a
pass-through, but it's been too long since I cared about Jet to remember for
sure.

-Mark

> "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
> news:9e7js0l09k0nai78jd0gphuve5amocej9c@4ax.com...
>> On Tue, 21 Dec 2004 11:08:24 -0500, "Jon Rizzo" <jrizzo at langan dot
>> com>
> wrote:
>>
>> ¤
>> ¤ Hello,
>> ¤
>> ¤ I am having a problem, and I was hoping that someone here could help
>> me.
> I
>> ¤ have an Access database define that contains two tables. There is a
> third
>> ¤ table defined in the Access database which is linked to a SQL Server
> table.
>> ¤ While I am in the Access environment, I can query between the linked
>> and
>> ¤ non-linked tables, but when I try to run a query that joins the linked
>> &
>> ¤ non-linked tables through ADO, it can't seem to access the linked table
>> ¤ properly.
>> ¤
>> ¤ Can somebody explain to me how this is done in ADO?
>> ¤
>>
>> What do you mean by "can't seem to access the linked table properly"? Are
> you getting an error? A
>> prompt for a user name and password?
>>
>>
>> Paul ~~~ pclement@ameritech.net
>> Microsoft MVP (Visual Basic)
>
>

Relevant Pages

  • Error 80040e14
    ... I have a simple Stored Procedure and all it does is it adds 4 ... Microsoft OLE DB Provider for SQL Server ...
    (microsoft.public.sqlserver.programming)
  • VSS Errors using NTBackup
    ... Microsoft OLE DB Provider for SQL Server ... Error message: ...
    (microsoft.public.windows.server.sbs)
  • Re: Linking a SQL database to an Access database.
    ... @srvproduct = 'Microsoft OLE DB Provider for Jet', ...
    (microsoft.public.access.queries)
  • Re: SQL-Server nach dBase
    ... The syntax that you are using is supported only by the Microsoft Jet OLE DB ... Jet Provider, the easiest solution would be to create an Access database ... SQL Server, then use SQL syntax over a SQLClient connection to SELECT INTO ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)
  • Re: OLE DB Error 999 "Object Was in Use"
    ... Steven Bras wrote: ... Microsoft OLE DB Provider for SQL Server ...
    (microsoft.public.data.ado)