hyperlinkpart issue when pulling hyperlink field from Access 2003

[cdck]

I have an ASP page on our company intranet that pulls information from a database used to manage controlled Quality Documents. The database is Access 2003; updated from an earlier 2000 db. When the update happened I began getting issues with hyperlinks stored in the database coming through to the ASP page doubled because it returned both the display text and the address.

Other projects with higher priorities arose after I posted that original issue (see thread701-1228509 and thread333-1375415) which delayed my being able to complete the troubleshoot.

Today I returned to the issue, following the lead that PHV and Melagan gave me at the time regarding the HyperlinkPart method. The ASP page is accessing a query in Access with the information needed, and so I adjusted the query to pull only the hyperlink part for the address rather than pulling all of the hyperlink field data. In Access, this looks and works fine; however, when the ASP page tries to pull information from that query, I get the following error:

[tt]Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'hyperlinkpart' in expression. [/tt]

I don't understand why the ASP page is even seeing the hyperlinkpart function, since that should be processed by Access and the data only should be served to the page. I suspect that it has something to do with the ODBC driver, but I don't know what it could be or how to find out. Can anyone suggest a path for troubleshooting this?


Cheryl dc Kern

 

[johnwm]

Sounds as if your query string has a problem - can you post it here?

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[cdck]

The query string is:

[tt]SELECT drDoc, drName, drDocType, drRev, drFile, drFileType FROM qryDocRev WHERE NewDoc = Yes ORDER BY drName;[/tt]

When I remove the hyperlinkpart method from the query that it is accessing in Access, it works fine. When I put it back in, it gets the error. If I open the query in Access with the hyperlinkpart method in use, it works fine.

I did find that Microsoft apparently recognizes an issue with this and data access pages, and doesn't care. I can only assume that they don't mention the issue with ASP pages because they don't think anyone might use another method to access the data... Can anyone suggest anything other than an actual re-design of the database?

Cheryl dc Kern

 

[johnwm]

I don't see a hyperlinkpart in the query. You are also missing quotemarks round the Yes in <WHERE NewDoc = Yes>

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[cdck]

You are correct, there is no HyperlinkPart in the query - this is the query from the ASP page. There is no HyperlinkPart in this query. THe HyperlinkPart method is in the query in Access which this query is pulling data from. The ASP page should never see the HyperlinkPart method at all, only the results of that method having been used.

Also, the query provided is taken out of code - here's the code that sets the query:

[tt]listSQL= "SELECT drDoc, drName, drDocType, drRev, drFile, drFileType FROM qryDocRev WHERE NewDoc = Yes ORDER BY drName;"[/tt]

In case you need it, here's the specific line in the ASP page that throws the error - but again, only when HyperLinkPart is used in the query being pulled from in Access, which is named qryDocRev:

[tt]set rstemp= Con.Execute(listSql)[/tt]

which is the line that uses the string created above to run a query.

In Access, qryDocRev is as follows:

[tt]SELECT Documents.Doc AS drDoc, Documents.Name AS drName, Revisions.Rev AS drRev, Revisions.revDate AS drRevDate, Revisions.revPage AS drRevPage, Revisions.revWhy AS drReason, Documents.FileType AS drFileType, Documents.DocType AS drDocType, Documents.NewDoc, Revisions.revInProcess, HyperlinkPart(Documents.File, 2) AS drFile
FROM Documents INNER JOIN (Revisions INNER JOIN qryLastRev ON (Revisions.revDoc = qryLastRev.revDoc) AND (Revisions.Rev = qryLastRev.MaxRev)) ON Documents.Doc = Revisions.revDoc
WHERE (((Documents.Disable)=No))
ORDER BY Documents.Doc;[/tt]

As I've said, this query runs fine and without error when opened directly in Access, but throws the problem error when opened by the ASP page through a query. If I remove the HyperlinkPart section of the code from this query, it stops throwing the error on the ASP page, but it returns Access's display text immediately followed by the address text in the result. The HyperlinkPart method is a built-in Access function, it is not a part of the ASP page. I suppose that if someone knew how to write the code for that, it would negate the entire problem...

Cheryl dc Kern

 

[johnwm]

The first query is also being run by the ODBC driver (which is made clear by your first mentioned error message), and that driver doesn't recognise internal Access functions. Rewrite the query in standard SQL and avoid using the internal function.

Sorry I don't use Access, so I'm not familiar with that function.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[cdck]

The function strips off the display text portion of the field and leaves only the address portion of the field. Does anyone know of standard SQL that can do this, since the display text is included specifically by Access? This is the problem I'm having - the new version of Access treats a hyperlink field as a multiple-part field when outputting, the old version did not. But I am not aware of any standard SQL coding that would know what to look for.

Does anyone else?

Cheryl dc Kern

 

[johnwm]

This looks much more like an Access problem than an ASP problem - you will probably do better in one of the Access forums

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[cdck]

Thanks anyway for the effort, johnwm.

For those of you who find this thread later when trying to deal with the same issue, from my experience there is no real answer. Microsoft appears to recognize that it is an issue (at least they have an article about it happening on data access pages) but they offer no solution. When you ask an Access group what the trouble is, they can't help you because nothing in Access is malfunctioning. ASP experts can't help you because it appears the tha problem is in the output from Access.

The solution that I've come up with is this:

1. Add another field to the table where the hyperlink is stored - make it a text field large enough for the longest address you might put in (if you're not sure, you might have to use a memo field).
2. In Access, run a query to update that field with the address section of the hyperlink form the original field. (set NewField = HyperlinkPart(oldField, 2)).
3. Wherever in Access you normally input the hyperlink data, set the "after update" event to also update the new field with only the address.
4. From your external page, pull the new field instead of the old one.

Hope this helps someone else come to a conclusion faster.

Cheryl dc Kern