Connecting to linked SQL tables through access

[Bonnie]

Hello,

I have searched the web and can't find a solution, but
this must (maybe) be common. I am creating an asp page
that connects to an access database which contains linked
SQL tables.

I can query and manipulate data in tables that aren't
linked, but when I try to run a query (qry Invoice) that
accesses data in un-linked tables and linked tables I get
the following error.

Even if I change the IIS server to use integrated
authentication I get the same error.

Error Type:
Microsoft JET Database Engine (0x80004005)
ODBC--connection to 'fin_live' failed.
/rcweb/asp/invoices.asp, line 55


Here's the code:

Dim connStr
Dim rsSelect
Dim vbInvoice
Dim sql1
vbInvoice = Request.Form("SelInvoice") + 0
connStr = "PROVIDER = MICROSOFT.JET.OLEDB.4.0;Data Source
= " & Server.MapPath("ReportsWeb.mdb")
Dim con
set con = Server.CreateObject("ADODB.Connection")
con.Open connStr

con.Execute "exec [qry Invoice]"

Thanks for any ideas,
Bonnie

[Bob Barrows]

Bonnie wrote:

> Hello,
>
> Microsoft JET Database Engine (0x80004005)
> ODBC--connection to 'fin_live' failed.
> /rcweb/asp/invoices.asp, line 55
>
>

[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;245587&Product=ado[/url]

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bonnie]

Hello,

I read that article and added the following code to create
a dummy recordset, but on this line I get the same error
message. I even tried the administrator username and
password. fin_live is a system dsn, I replaced username
and password with actuals ( I did not use quotes

Dim rs
Set rs = con.execute("SELECT * FROM
[ODBC;DSN=fin_live;uid=Username;pwd=Password;databa se=pubs]
..Invoice_Headers WHERE Invoice_Number = 102248")
rs.close

Thanks for any additional ideas,
Bonnie

>-----Original Message-----
>Bonnie wrote:

>> Hello,
>>
>> Microsoft JET Database Engine (0x80004005)
>> ODBC--connection to 'fin_live' failed.
>> /rcweb/asp/invoices.asp, line 55
>>
>>

>[url]http://support.microsoft.com/default.aspx?scid=kb;en-[/url]

us;245587&Product=ado

>
>HTH,
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed

in my From

>header is my spam trap, so I don't check it very often.

You will get a

>quicker response by posting to the newsgroup.
>
>
>.
>

[Bob Barrows]

Bonnie wrote:

> Hello,
>
> I read that article and added the following code to create
> a dummy recordset, but on this line I get the same error
> message. I even tried the administrator username and
> password. fin_live is a system dsn, I replaced username
> and password with actuals ( I did not use quotes
>
> Dim rs
> Set rs = con.execute("SELECT * FROM
> [ODBC;DSN=fin_live;uid=Username;pwd=Password;databa se=pubs]
> .Invoice_Headers WHERE Invoice_Number = 102248")
> rs.close
>
> Thanks for any additional ideas,

Sorry, but I've never used Access this way so I can't offer any more
assistance.
(I would have said "WHERE 1=2" to prevent any records from being returned).

Have you tried the other suggestion of persisting the login info in the
table link?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bonnie]

Thanks anyway.

Yes, the login info is already stored in the table in
access. If I run documenter on the table you can see that
the username and password are stored.

I thought it was common to use linked tables from an sql
database and to use access as the front end. I am trying
to use access queries in the asp pages instead of going
directly to the sql table, but even when I try that it
still doesn't work...

Thanks again,
Bonnie

>-----Original Message-----
>Bonnie wrote:

>> Hello,
>>
>> I read that article and added the following code to

create

>> a dummy recordset, but on this line I get the same error
>> message. I even tried the administrator username and
>> password. fin_live is a system dsn, I replaced username
>> and password with actuals ( I did not use quotes
>>
>> Dim rs
>> Set rs = con.execute("SELECT * FROM
>>

[ODBC;DSN=fin_live;uid=Username;pwd=Password;databa se=pubs]

>> .Invoice_Headers WHERE Invoice_Number = 102248")
>> rs.close
>>
>> Thanks for any additional ideas,

>
>Sorry, but I've never used Access this way so I can't

offer any more

>assistance.
>(I would have said "WHERE 1=2" to prevent any records

from being returned).

>
>Have you tried the other suggestion of persisting the

login info in the

>table link?
>
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed

in my From

>header is my spam trap, so I don't check it very often.

You will get a

>quicker response by posting to the newsgroup.
>
>
>.
>