UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Converting Hyperlink Data To Text, Access 2007    
 
   
trab
post Aug 24 2017, 11:39 AM
Post#1



Posts: 39
Joined: 11-April 13



I have a lookup form based on a table with around 800K records - the table currently has a number of fields table including 1 hyperlink field, that links
to pdf files.

When I open the form it looks for all records that match an ID in my main table.

The problem is that the lookup form takes ages to open, not just a few seconds. Is this because of the hyperlink data, or simply the number of records in the table?

Would it help if I converted the hyperlink data to text, and then after clicking a button, ran some code which converted the text in the text field
to a hyperlink? How would I do this, and would it be likely to speed the whole process up anyway?
Go to the top of the page
 
DanielPineault
post Aug 24 2017, 12:06 PM
Post#2


UtterAccess VIP
Posts: 5,453
Joined: 30-June 11



QUOTE
When I open the form it looks for all records that match an ID in my main table.


How is it looking exactly? Can you show us your code.
Have you applied the proper indexes?
Have you minimized the number of fields being returned by your query?
Do you establish a persistent connection between your BE and FE at the startup of your database?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
trab
post Aug 25 2017, 03:53 AM
Post#3



Posts: 39
Joined: 11-April 13



Hello Daniel,

  • How is it looking exactly? Can you show us your code.
    This isn't code, but it's the SQL that shows in the row source for the lookup form that opens. I haven't used any code yet, as I wasn't sure what to do, other than use some hyperlink function like hyperlinkpart(?) :

    CODE
    PARAMETERS [Forms]![frmtransactions]![UnitNo] Text ( 255 );
    SELECT tblLetters.LinktoPDF, tblLetters.FileDate, tblLetters.FileTime, tblLetters.UnitNo
    FROM tblLetters
    WHERE (((tblLetters.UnitNo)=[Forms]![frmtransactions]![UnitNo]))
    ORDER BY tblLetters.FileDate DESC , tblLetters.FileTime DESC;

    Not sure why the PARAMETERS [Forms]![frmtransactions]![UnitNo] Text ( 255 ) line is there - I didn't type it in;

  • Have you applied the proper indexes?
    Currently, the primary index in tblLetters, [ID}, is autonumbered. The "lookup" field, a hyperlink field, is [LinktoPDF] is indexed, but I notice it's not unique - I think it should be.

  • Have you minimized the number of fields being returned by your query?
    Just the four fields as per SQL above.

  • Do you establish a persistent connection between your BE and FE at the startup of your database?
    I don't have front and back-ends with this database. I inherited it, and have never got around to rejigging it. Not sure, what the pitfalls might be on the way, either.


TIA.

trab
Go to the top of the page
 
RAZMaddaz
post Aug 25 2017, 08:15 AM
Post#4


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


PMFJI....

QUOTE
Currently, the primary index in tblLetters, [ID}, is autonumbered. The "lookup" field, a hyperlink field, is [LinktoPDF] is indexed, but I notice it's not unique - I think it should be.


Are you saying that hyperlink Field is a "Lookup" Field.

If so, that MIGHT be the reason why everything is taking so long.

Please read the following, regarding Lookup Fields and how they can affect one's Database, Queries, etc.

The Evils of Lookup Fields


RAZMaddaz
Go to the top of the page
 
trab
post Aug 25 2017, 08:37 AM
Post#5



Posts: 39
Joined: 11-April 13



Hello Raz,

I knew that would confuse matters. No, it's not a lookup field - that's why I put it in quotes. Apologies. I meant that's the field that's being "looked at" - sorry, I don't know the correct terminology for it.

KR
trab
Go to the top of the page
 
trab
post Aug 25 2017, 08:37 AM
Post#6



Posts: 39
Joined: 11-April 13



Hello Raz,

I knew that would confuse matters. No, it's not a lookup field - that's why I put it in quotes. Apologies. I meant that's the field that's being "looked at" - sorry, I don't know the correct terminology for it.

KR
trab
Go to the top of the page
 
RAZMaddaz
post Aug 25 2017, 09:23 AM
Post#7


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


trab,


I am trying to understand why you have "Parameters" on the first line of the SQL, because this query does not look like Crosstab query.

If you make a backup of this Query, then delete the first line of this Query, does the Query still run correctly and/or faster?


RAZMaddaz
Go to the top of the page
 
RAZMaddaz
post Aug 25 2017, 09:41 AM
Post#8


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


Nevermind, regarding the Parameters.


If you view the Query and look at the Parameters, you will see that the Text Box on the Form and the Data Type has been entered here. And "Short Text", has been entered as the Data Type.


Is that correct?




RAZMaddaz
Go to the top of the page
 
trab
post Aug 29 2017, 10:01 AM
Post#9



Posts: 39
Joined: 11-April 13



Hello,

Not sure that I understand you - if I look at the underlying query, the data type for [Forms]![frmtransactions]![UnitNo] is simply "Text".

KR

trab
Go to the top of the page
 
RAZMaddaz
post Aug 29 2017, 10:45 AM
Post#10


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


Sorry!!!

If you look at the Query, in Design View.

Then go to Parameters and there you have the [Forms]![frmtransactions]![UnitNo] in the Parameters Column and probably "ShortText" in the Data Type Column. Correct?

Now if you view the Table from where this Field "UnitNo" is located, is this a Text Field or a Number Field.

Whether this explains the reason for you Query for taking forever, I really don't know.


RAZMaddaz
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:29 PM