Unspecified error when adding a link

[Mark]

Hi - using asp and ms Access - this works ok:

set Products = Server.CreateObject("ADODB.Recordset")
Products.ActiveConnection = myConString
Products.Source = "SELECT Products.productid, Products.product,
Products.price, Products.partno FROM Products"
Products.CursorType = 0
Products.CursorLocation = 2
Products.LockType = 3
Products.Open()

...but when I try to add a connection to another table I get the dreaded
unspecified error:

(same code)
Products.Source = "SELECT Products.productid, Products.product,
Products.price, Products.partno, Sizes.ProductSize AS Size FROM Products
INNER JOIN (Sizes ON Products.SizeID = Sizes.SizeID) "
(same code)

Any ideas why this may be happening? I have latest MDAC, XP Pro (IIS
running locally), and no memo fields!

Thanks,

Mark

 

[Aaron [SQL Server MVP]]

Access is very fussy about the placement of parentheses. Try designing the
query inside of Access' GUI, and see if it looks any different. Or, you
could try the non-ANSI way, e.g.

SELECT p.productid, ..., s.ProductSize
FROM products AS p, Sizes AS s
WHERE p.SizeID = s.SizeID

 

[Mark]

Hi Aaron - thank you (I checked your site before posting - but did not
think to rewrite the query in the way you have shown) - I do not have
Access, just an Access database file - which is why I could not test
this in it's gui.

Thank you for your help,

Mark

 

[Bob Barrows [MVP]]

Products.Source = "SELECT Products.productid, Products.product,
Products.price, Products.partno, Sizes.ProductSize AS Size FROM
Products INNER JOIN (Sizes ON Products.SizeID = Sizes.SizeID) "

Why the parentheses? You only have two tables.

Products INNER JOIN Sizes ON Products.SizeID = Sizes.SizeID "


You should always build and test your queries in your database's native
query tool before trying to run them in asp. With Access, that means using
the Access Query Builder to create and test your queries.

HTH,
Bob Barrows

 

[David C. Holley]

I agree. I'm to the point where I build any complex query in Access and
then copy & past it over. Keep in mind that purchasing MSAccess is
actually to your advantage as it will allow you direct access to the
objects if you need to make a change, look up a field name or field data
type. Then, of course, there are the benefits of having Access in the
event that the database ends up corrupted.

David H

I also use the VBEditor in Access to test & debug any VBScript function
that I'm working on.

 

[Bob Barrows [MVP]]

I agree. I'm to the point where I build any complex query in Access
and then copy & past it over.

Now you're ready for the next step: make a clean break from using dynamic
sql and execute your saved queries instead.

Bob Barrows

 

[Mark]

Hi - thanks for all of your comments, all of which I take on board
(setting up queries in a designer - means getting Access, and running
queries as sort of SPs from within Access).

If you're interested, it turns out that Access didn't like the "AS
Sizes" part of the query. Don't know if it's a reserved word - but by
changing to "AS [Sizes]" it now works without any errors.

Again, thanks, Mark



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Roland Hall]

in message : Hi - thanks for all of your comments, all of which I take on board
: (setting up queries in a designer - means getting Access, and running
: queries as sort of SPs from within Access).
:
: If you're interested, it turns out that Access didn't like the "AS
: Sizes" part of the query. Don't know if it's a reserved word - but by
: changing to "AS [Sizes]" it now works without any errors.

Rather that purchasing Access, if you can, consider using SQL. You can get
MSDE for nothing.
http://www.asp.net/msde/default.aspx?tabindex=0&tabid=1

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

 

[Bob Barrows [MVP]]

Hi - thanks for all of your comments, all of which I take on board
(setting up queries in a designer - means getting Access, and running
queries as sort of SPs from within Access).

If you're interested, it turns out that Access didn't like the "AS
Sizes" part of the query. Don't know if it's a reserved word - but by
changing to "AS [Sizes]" it now works without any errors.

Ah! I missed that! "Size" is a reserved ODBC keyword
(http://www.aspfaq.com/show.asp?id=2080).

It would have been easier to spot that if you had been able to tell us that
the query ran fine in the Access Query Builder but failed when run by ADO
.... ;-)

Bob Barrows

 

[Mark]

Hi Roland - yes I know, thank you. I already have the full SQL Server
through MSDN (which didn't include Access) - trouble is with this
project, the client is insisting it uses Access. I tried to push them
down the sql route, but to no avail.

And unfortunately the way that Sql Server builds its queries isn't
always backwards compatile with what Access wants (parenthesis was
mentioned above - I know there are changes there) - so using that gui
isn't always an option either - even for testing.

Thanks for the input either way,

Mark



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!