[AccessD] Fastest Way

Rocky,

<<So you're really not using Seek on a linked
table, then. :)>>

  Yes, in the strictest sense, that is correct.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Friday, January 15, 2010 4:39 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Fastest Way

Thanks, Jim.  Didn't know that.  So you're really not using Seek on a linked
table, then. :)

R 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Friday, January 15, 2010 8:32 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Fastest Way


 Yes, but you would need to open the remote db.  i.e.

 Dim wrk As Workspace
  Dim dbCurrent As Database
  Dim dbRemote As Database
  Dim tdfAttached As TableDef
  Dim strPath As String
  Dim rst1 As Recordset
  Dim rst1Field As Field
  Dim rst2 As Recordset
  Dim rst2Field As Field
  Dim intHaveMatch As Integer

'
' First, get the path to the MDB for the attached table.
'
  Set wrk = DBEngine.Workspaces(0)
  Set dbCurrent = wrk.Databases(0)
  Set tdfAttached = dbCurrent.TableDefs("tblGeoAllData")
  
  strPath = tdfAttached.Connect
  strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
  
  Set dbRemote = wrk.OpenDatabase(strPath, False, True)
  
'
'  First, find the postal code.  Need to know the country.
'
  intHaveMatch = True
  
  Set rst1 = dbRemote.OpenRecordset("tblGeoAllData", DB_OPEN_TABLE)
  rst1.index = "PrimaryKey"
  rst1.Seek "=", CountryCode, PostalCode

  If rst1.NoMatch Then
    Set rst2 = dbRemote.OpenRecordset("tblGeoCountries", DB_OPEN_TABLE)
    rst2.index = "PrimaryKey"


  That's a bit of the long way around because it was an example and I wanted
to show how to do it step by step.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Friday, January 15, 2010 11:21 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Fastest Way

Can I use .Seek on a linked table though?
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Friday, January 15, 2010 5:35 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Fastest Way

Rocky,

  If findfirst is too slow, open it as a table and use .Seek

  That is the fastest way to search a JET table.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Thursday, January 14, 2010 6:56 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Fastest Way

That was my first thought - brute force - time is not that critical.  And it
would only be a couple of lines of code additional in the current import
routine - just a .FindFirst.  If later it turns out that the time is
unacceptable, I could bail to the other approach.  Still thinking.... But
with the brute force method I could quickly set up a test case.  I already
put start time, end time, and elapsed time test boxes on the form.  

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Thursday, January 14, 2010 1:36 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Fastest Way

It's a done deal.  If you want speed and efficiency,  then go  my way.

Unique index on relevant fields.  Let access handle the dupes.  One read-One
input-Finito.

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: 14 January 2010 21:22
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Fastest Way

I think we'd all be interested to know what method you eventually use, and
how the performance is.

Lambert 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Thursday, January 14, 2010 1:33 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Fastest Way

Actually don't want to delete and import - just bypass the incoming records
that are already in the table.

R 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com