Access query of linked tables returns EOF

[Bob Barrows]

It works fine for me. I created a database called db14.mdb containing one
local and two linked tables (each from a different database). I then created
a saved query called testquery that links them via this SQL:
SELECT linkedA.SomeData, linkedA.fldDay, linkedB.Description, local.Field1
FROM (tblContacts AS linkedA INNER JOIN PartList1 AS linkedB ON
linkedA.SomeData = linkedB.PartNumber) INNER JOIN Table1 AS [local] ON
linkedA.SomeData = local.FK;

I then used this code to get results from ASP:
Dim rs, cn
Set cn = server.createobject("ADODB.Connection")
cn.Open "provider=microsoft.jet.oledb.4.0;" & _
data source=" & server.mappath("db14.mdb")
Set rs = server.createobject("ADODB.Recordset")
cn.testquery rs
response.write rs.eof
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

EOF was false.
Are you sure you have all the databases in a folder(s) to which the IUSR
account has permissions?

RJM wrote:

> I am using an Access query in database A that references linked
> tables in databases B and C to return related records in B and C.
>
> The query runs fine in Access, but when referenced via ASP, it
> returns EOF. I am assuming my code is correct, since queries in A
> that only reference the same linked tables in B only, or C only, do
> return the appropriate records via the same ASP code.
>
> Is there some limitation of ASP when retrieving information via Access
> queries? Does the ASP code need to be modified in some way?
>
> Here's a bit of my method:
> -------------------------------------------------
> Set oCmd = Server.CreateObject ("ADODB.Command")
> oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath
> oCmd.ActiveConnection = oConn
> oCmd.CommandText = "testquery"
> oCmd.CommandType = &H0004
> Set oRs = Server.CreateObject ("ADODB.Recordset")
> Set oRs = oCmd.Execute
> -------------------------------------------------
> Again, this works if "testquery" references data in database A, B, or
> C, but not the combination of B and C I'm looking for.
>
> Any help would be appreciated.
>
> Thanks

[RJM]

Nevermind. Found it.

A LIKE criteria in the database A query contained a * wildcard, while ASP /
SQL uses %. The Access query creates a SQL statement that when run from ASP
returned no values (LIKE "foo*" returns nothing, LIKE "foo%" returns
foobar). I changed LIKE to = and spelled out the each available result
explicitly, and all is well.

Thank you for your response though.


"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:ObKsZIYQDHA.3016@TK2MSFTNGP10.phx.gbl...

> It works fine for me. I created a database called db14.mdb containing one
> local and two linked tables (each from a different database). I then

created

> a saved query called testquery that links them via this SQL:
> SELECT linkedA.SomeData, linkedA.fldDay, linkedB.Description, local.Field1
> FROM (tblContacts AS linkedA INNER JOIN PartList1 AS linkedB ON
> linkedA.SomeData = linkedB.PartNumber) INNER JOIN Table1 AS [local] ON
> linkedA.SomeData = local.FK;
>
> I then used this code to get results from ASP:
> Dim rs, cn
> Set cn = server.createobject("ADODB.Connection")
> cn.Open "provider=microsoft.jet.oledb.4.0;" & _
> data source=" & server.mappath("db14.mdb")
> Set rs = server.createobject("ADODB.Recordset")
> cn.testquery rs
> response.write rs.eof
> rs.Close
> Set rs = Nothing
> cn.Close
> Set cn = Nothing
>
> EOF was false.
> Are you sure you have all the databases in a folder(s) to which the IUSR
> account has permissions?
>
> RJM wrote:

> > I am using an Access query in database A that references linked
> > tables in databases B and C to return related records in B and C.
> >
> > The query runs fine in Access, but when referenced via ASP, it
> > returns EOF. I am assuming my code is correct, since queries in A
> > that only reference the same linked tables in B only, or C only, do
> > return the appropriate records via the same ASP code.
> >
> > Is there some limitation of ASP when retrieving information via Access
> > queries? Does the ASP code need to be modified in some way?
> >
> > Here's a bit of my method:
> > -------------------------------------------------
> > Set oCmd = Server.CreateObject ("ADODB.Command")
> > oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath
> > oCmd.ActiveConnection = oConn
> > oCmd.CommandText = "testquery"
> > oCmd.CommandType = &H0004
> > Set oRs = Server.CreateObject ("ADODB.Recordset")
> > Set oRs = oCmd.Execute
> > -------------------------------------------------
> > Again, this works if "testquery" references data in database A, B, or
> > C, but not the combination of B and C I'm looking for.
> >
> > Any help would be appreciated.
> >
> > Thanks

>
>