TIP - How to perform Seek on Linked Tables 2

[WP]

How to perform Seek on Linked Tables<br>
(Q) Can I perform Seek and Index on linked tables?<br>
(A) Directly, No. But you can use the following function which will allow you to do this.<br>
(watch out for line wraps. It's just a single line of code.)<br>
'************ Code Start ***************<br>
'This code was originally written by Michel Walsh.<br>
'It is not to be altered or distributed,<br>
'except as part of an application.<br>
'You are free to use it in any application,<br>
'provided the copyright notice is left unchanged.<br>
'<br>
'Code Courtesy of<br>
'Michel Walsh<br>
<br>
Public Function OpenForSeek(TableName As String) As Recordset<br>
' Assume MS-ACCESS table<br>
Set OpenForSeek = DBEngine.Workspaces(0).OpenDatabase _<br>
(Mid(CurrentDb().TableDefs(TableName).Connect, _<br>
11), False, False, &quot;&quot.OpenRecordset(TableName, _<br>
dbOpenTable)<br>
End Function<br>
'************ Code End ***************<br>
Just use:<br>
Dim rst as Recordset<br>
set rst=OpenFormSeek(&quot;TableName&quot<br>
and you can use rst.Seek and rst.Index on linked table TableName<br>
<br>
<p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[Brack]

Yes, you can Seek on attached/linked tables. Simply use OpenDatabase() instead of CurrentDb().<br>
<br>
- CSV<br>
Author, related Access Help topics<br>
Free Downloads at <A HREF="

http://www.csvTools.com"

TARGET="_new">

http://www.csvTools.com</A>

 

[JimHorton]

Hello CSV,<br>
I remember you from the Cserve Access forum, it's good to see you here!<br>
--Jim

 

[Dumboy]

Question.... How legal is the copyrights you are talking about....?<br>
<br>
does it really holds...? <br>
<br>
is it really copyrighted...?<br>
<br>
Sorry... but it doesn't make much sense to me. If that were possible wouldn't in a really short period of time all the code be copyrighted, after all there are only a few different ways to write the code.<br>
<br>
I agree that as a courtesy to the creator of the code you should make a note as far as who is the author of the code... talk about ethics... but going as far as using words as copyright for VBCode... <br>
<br>
I don't know.... like I said I'm just maybe too stupid to make sense of what I'm saying...<br>
<br>
Later... <p>Dumboy.! Ouch.! .my brain.....<br><a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a><br><a href= > </a><br>It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!

 

[WP]

Brack,<br><br>I know this !!!<br><br>But tell me how would you get the path of the attached table then ?&nbsp;&nbsp;Or would you hardcode it.&nbsp;&nbsp;Why not ... the database will never move.&nbsp;&nbsp;Hah.<br><br>The OpenforSeek function provides a much cleaner way of seeking on attached tables.&nbsp;&nbsp;I like it.&nbsp;&nbsp;I wish I came accross it years ago.&nbsp;&nbsp;It save me having extract the path from the attached table and then do an external open of the database.<br><br>WP<br><br> <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[pac1exl]

WP,

I try to copy your function Openforseek in my MS Access module. I'm using a MS SQL 7.0 as a back-end. I encountered a problem in executing the function OpenForseek. It keeps on prompting &quot;Can't find the file&quot;. Appreciate your help.

Thanks.

 

[WP]

This function only applies to tables attached from another Access database.

If you need to seek on a SQL table then you'd need to write and call a strored procedure.

T/SQL is fairly simple to write and the calling of the SP using ADO is too. Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out

http://www.deepturtle.ch

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmmmmmmmmmmm!

Actually -NOT-.

The &quot;Seek&quot; is NOT on the linked table, but on a seperate instantation of the table as an open table type recordset in any MS Access (including the current OR Foregin) database. The distinction can be important is some applications.

Since any find or seek operation may be replicated with standard SQL, it may just be easier to use the standard methods (SQL) than techniques which are specific to one program. It MAY take a little extra effort at first, but the reward of being able to use the same approach across multiple database implementations is (IMHO) well worth it.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WP]

Mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm!

Really. So what you are saying is that you would encourage local SQL against a linked SQL Server table. Fine when you have 200 records but stick 1000000 records in there and you'll see your app. crawl along as it tries to lookup the record.

The reason most people use Seek is for the performance it offers over FindFirst.

Who really cares about a generic solution? All most people want is performance and stability (i.e. no Access hangs because it takes so long to return a SQL result).

In My Humble Opinion too.
Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out

http://www.deepturtle.ch

 

[MichaelRed]

I don't recall specifying ACCESS SQL. I also did not mention &quot;FIND&quot; as the recommended method. What I did say was &quot; ... find or seek operation may be replicated with standard SQL ... &quot;. So you can avoid the instantiation of the copy of the recordset by simply 'constructing' the recordset from an SQL statement (aginst the existing linked instantation of the foregin recordset).

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over