Relinking backend fast with A2k, very slow with A2k3

[Arno R]

Hi all,
I am deploying an A2k app to users with different versions of Access.

Using Access 2000 the relinking on startup (on deploying a new frontend or when backend has changed) is very fast.
It takes about 4-5 seconds to relink some 50 tables. (I am relinking, not refreshing)

Using Access 2003 the relinking is very slow compared with Access 2000.
Now It takes about 1,5 to 2 minutes !! to relink the same 50 tables.

I am testing this on my own machine, only changing the shortcut to point to another Access-version.
What can be the problem here ??
FYI: I am using a persistent connection while relinking. No subdatasheets, No NameAutoCorrect

Any ideas??

Thanks
Arno R

[Albert D. Kallal]

>FYI: I am using a persistent connection while relinking.

Well, you can't have tables open while re-linking.....

So, I assume the above actually means you link the first table. then open
that table to a variable that DOES NOT go out of scope. (this will give you
the persistent connection). You then continue to re-link the rest of the
tables.....right?

the above should fix the slow re-linking, and I actually not experienced a
difference in speed from a2000 to a2003 for linking speed.

If the above does not fix the speed, then I would of course turn off all off
track-autoname correct....,and work your way through the following list:

http://www.granite.ab.ca/access/performancefaq.htm

One issue that has been noted is that very long path names can cause this
also...so, perhaps the difference here is that your test for a2003 has very
long path name...try moving down to near root level...and see if that
helps...

And, double check that your code does have a per persistent connection is
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Arno R]

Hi Albert,
comments inline

Albert D. Kallal wrote:

>FYI: I am using a persistent connection while relinking.

Well, you can't have tables open while re-linking.....

I have an open database-connection like
Set db = DBEngine.OpenDatabase(PathToBackend)
'(This maintains the ldb-file)
....relink code here

So, I assume the above actually means you link the first table. then open
that table to a variable that DOES NOT go out of scope. (this will give you
the persistent connection). You then continue to re-link the rest of the
tables.....right?

Same result when I do this.
A2k is fast, A2k3 slow.
Same fe, be, workgroup... same computer, same network

the above should fix the slow re-linking, and I actually not experienced a
difference in speed from a2000 to a2003 for linking speed.

If the above does not fix the speed, then I would of course turn off all off
track-autoname correct....,and work your way through the following list:

http://www.granite.ab.ca/access/performancefaq.htm

I did all of that already...
But again I worked my way to Tony's lists... No solution.

I use the *very same* FE-BE, only difference is the shortcut that points
to C:\program Files\Microsoft Office2000\Office\Msaccess.exe or to
C:\program Files\Microsoft Office2003\Office11\Msaccess.exe

Must be something different going on here???

Thanks
Arno R

[Arno R]

"Arno R" <ar***********@planet.nlschreef in bericht news:45***********************@text.nova.planet.nl ...

Must be something different going on here???

Indeed something else, but what ??
I noticed this morning that the very same files (same setup) used on my partner's office-computer give different results.

Tested the *same* scenario: the exact same FE-BE combination as at my home-office.
So I was testing this with both Access2003 and Access2k also on the same computer there.
==On this PC relinking the backend. using Access2003 is even faster than using Access2k ???
I am stumped. Do not know where to look further...

To recap:
There seems to be a problem with my Access2003-install at my home-office.
Relinking tables is very slow

I did set macro-security to low.
Could this issue be related to a certain sandbox-mode ??
Maybe I will try a re-install of Office 2003??
Any other idea's ??

Thanks.
Arno R

[Albert D. Kallal]

I have an open database-connection like

Set db = DBEngine.OpenDatabase(PathToBackend)
'(This maintains the ldb-file)
...relink code here

Hum....interesting. I wonder if actually opening a table makes a difference
here. I never used the opendatabase make a persistent connection.
I always assumed you have to actually open a table...

>

>So, I assume the above actually means you link the first table. then open
that table to a variable that DOES NOT go out of scope. (this will give
you the persistent connection). You then continue to re-link the rest of
the tables.....right?

Same result when I do this.
A2k is fast, A2k3 slow.
Same fe, be, workgroup... same computer, same network

are you saying you changed your code to open the table after the first table
been re-linked? Or, are you saying
you kept things the same, and assumed that you open database gives you the
persistent connection?

I reasonable think that opendatabase should work, but I would re-test the
code with opening the table....
I used:

For Each mytables In CurrentDb.TableDefs
strOld = mytables.Connect
strBackPart = strGetDbTable(mytables.Connect)

strTo = strReLinkDir & strBackPart

If Len(mytables.Connect) 0 Then
If Left(mytables.Connect, 10) = ";DATABASE=" Then
mytables.Connect = ";DATABASE=" & strTo
mytables.RefreshLink
If bolFirst = False Then
Set rstFirst = CurrentDb.OpenRecordset(mytables.Name)
bolFirst = True
End If
End If
End If

Next mytables

If bolFirst = True Then
rstFirst.Close
Set rstFirst = Nothing
End If

I just not 100% sure opening the database does force the connection
open....it *should*..but, you might want as a test to check the above.

The other issue (and I sure you checked) is to disable any virus protection
software running on the machine.........

There is obviously some issue on that machine...just don't know what....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

>

>the above should fix the slow re-linking, and I actually not experienced
a difference in speed from a2000 to a2003 for linking speed.

If the above does not fix the speed, then I would of course turn off all
off track-autoname correct....,and work your way through the following
list:

http://www.granite.ab.ca/access/performancefaq.htm

I did all of that already...
But again I worked my way to Tony's lists... No solution.

I use the *very same* FE-BE, only difference is the shortcut that points
to C:\program Files\Microsoft Office2000\Office\Msaccess.exe or to
C:\program Files\Microsoft Office2003\Office11\Msaccess.exe

Must be something different going on here???

Thanks
Arno R

[David W. Fenton]

"Albert D. Kallal" <Pl*******************@msn.comwrote in
news:siPxh.906027$R63.106391@pd7urf1no:

>I have an open database-connection like
Set db = DBEngine.OpenDatabase(PathToBackend)
'(This maintains the ldb-file)
...relink code here

Hum....interesting. I wonder if actually opening a table makes a
difference here. I never used the opendatabase make a persistent
connection. I always assumed you have to actually open a table...

If the slowdown is caused by the creation of the LDB file, then
opening the database connection is sufficient.

>>So, I assume the above actually means you link the first table.
then open that table to a variable that DOES NOT go out of
scope. (this will give you the persistent connection). You then
continue to re-link the rest of the tables.....right?

Same result when I do this.
A2k is fast, A2k3 slow.
Same fe, be, workgroup... same computer, same network

are you saying you changed your code to open the table after the
first table been re-linked? Or, are you saying
you kept things the same, and assumed that you open database gives
you the persistent connection?

I reasonable think that opendatabase should work, but I would
re-test the code with opening the table....
I used:

For Each mytables In CurrentDb.TableDefs
strOld = mytables.Connect
strBackPart = strGetDbTable(mytables.Connect)

strTo = strReLinkDir & strBackPart

If Len(mytables.Connect) 0 Then
If Left(mytables.Connect, 10) = ";DATABASE=" Then
mytables.Connect = ";DATABASE=" & strTo
mytables.RefreshLink
If bolFirst = False Then
Set rstFirst =
CurrentDb.OpenRecordset(mytables.Name)
bolFirst = True
End If
End If
End If

Next mytables

If bolFirst = True Then
rstFirst.Close
Set rstFirst = Nothing
End If

I just not 100% sure opening the database does force the
connection open....it *should*..but, you might want as a test to
check the above.

It *does* create the LDB file. If the creation of the LDB file is
what causes the performance drain, then it should work.

The other issue (and I sure you checked) is to disable any virus
protection software running on the machine.........

There is obviously some issue on that machine...just don't know
what....

I would try completely deleting the links and recreating them from
scratch in A2K3 on the theory that there's some cached data in the
links that is causing a problem in A2K3.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Arno R]

"David W. Fenton" <XX*******@dfenton.com.invalidschreef in bericht news:Xn**********************************@127.0.0. 1...

I would try completely deleting the links and recreating them from
scratch in A2K3 on the theory that there's some cached data in the
links that is causing a problem in A2K3.

Thanks David,

Recreating the links, that's *exactly* what I am doing with this matter.
I stopped refreshing links long ago. I noticed sometimes a difference with a complete relink.
So this is not the problem here on my machine.

All my tests do *exactly* the same thing, only with different Access-versions and/or different machines.
The code I use first deletes all the linked tables (deletes the links) and recreate the links for all the needed tables again.

Here is the code: (error handling, declarations and such neglected here)

Sub Unlink()
Set db = CurrentDb()
For i = 0 To db.TableDefs.Count - 1
If Len(db.TableDefs(i).SourceTableName) <0 Then
DoCmd.DeleteObject acTable, db.TableDefs(i).Name
End If
Next i
End sub
-----------------

Sub LinkTable (Tablename)
DoCmd.TransferDatabase acLink, "Microsoft Access", strBEPath, acTable, Tablename, Tablename, False
End sub
------------------

Link the tables again:
Sub LinkAgain()
'Detect strBEPath first ... (path to backend)

Dim dbLink As Database
Call Unlink
Set dbLink = DBEngine.OpenDatabase(strBEPath)

LinkTable "TableFirst"
LinkTable "TableSecond"

End sub

As I said I also tested with a connection to the first table (thanks Albert) but in all my tests I see no difference at all.
As soon as I find out what is the case here I will post back to the group.
Also disabled any viruscatchers... no difference

Arno R

[Lyle Fairfield]

On Feb 6, 11:29 am, "Arno R" <[e-mail]:

"David W. Fenton" <[e-mail] in berichtnews:Xn**********************************@1 27.0.0.1...

I would try completely deleting the links and recreating them from
scratch in A2K3 on the theory that there's some cached data in the
links that is causing a problem in A2K3.

Thanks David,

Recreating the links, that's *exactly* what I am doing with this matter.
I stopped refreshing links long ago. I noticed sometimes a difference with a complete relink.
So this is not the problem here on my machine.

All my tests do *exactly* the same thing, only with different Access-versions and/or different machines.
The code I use first deletes all the linked tables (deletes the links) and recreate the links for all the needed tables again.

Here is the code: (error handling, declarations and such neglected here)

Sub Unlink()
Set db = CurrentDb()
For i = 0 To db.TableDefs.Count - 1
If Len(db.TableDefs(i).SourceTableName) <0 Then
DoCmd.DeleteObject acTable, db.TableDefs(i).Name
End If
Next i
End sub
-----------------

Sub LinkTable (Tablename)
DoCmd.TransferDatabase acLink, "Microsoft Access", strBEPath, acTable, Tablename, Tablename, False
End sub
------------------

Link the tables again:
Sub LinkAgain()
'Detect strBEPath first ... (path to backend)

Dim dbLink As Database
Call Unlink
Set dbLink = DBEngine.OpenDatabase(strBEPath)

LinkTable "TableFirst"
LinkTable "TableSecond"

End sub

As I said I also tested with a connection to the first table (thanks Albert) but in all my tests I see no difference at all.
As soon as I find out what is the case here I will post back to the group.
Also disabled any viruscatchers... no difference

Arno R

Is there a reason for
DoCmd.TransferDatabase acLink, "Microsoft Access", strBEPath, acTable,
Tablename, Tablename, False?
I suppose MS might recommend it. That would likely mean that it's very
inefficient.
I try not to ask Access to do JET jobs.

If you can't find the problem perhaps you should consider a different
approach, perhaps some new code? Here's some airy stuff:

Public Sub Link(ByVal NewLocation$)
Dim aTables$()
Dim TableList$
Dim tdf As DAO.TableDef
Dim z&
Unlink TableList
aTables = Split(TableList, ",")
For z = 0 To UBound(aTables)
Set tdf = CurrentDb.CreateTableDef(aTables(z))
With tdf
.SourceTableName = aTables(z)
.Connect = ";DATABASE=" & NewLocation
End With
CurrentDb.TableDefs.Append tdf
Next z
CurrentDb.TableDefs.Refresh
'Debug.Print CurrentDb.TableDefs.Count
End Sub

Private Sub Unlink(ByRef TableList$)
Dim r As DAO.Recordset
Dim Name$
'Debug.Print CurrentDb.TableDefs.Count
TableList = ""
Set r = CurrentDb.OpenRecordset("SELECT Name FROM mSysObjects
WHERE Type = 6")
With r
While Not .EOF
Name = .Fields(0).Value
CurrentDb.Execute "DROP Table [" & Name & "]"
TableList = TableList & Name
.MoveNext
If Not .EOF Then TableList = TableList & ","
Wend
End With
Set r = Nothing
CurrentDb.TableDefs.Refresh
'Debug.Print CurrentDb.TableDefs.Count
End Sub

Public Sub test()
Link "C:\Documents and Settings\Lyle Fairfield\My Documents\Access
\northwind.mdb"
End Sub

[Arno R]

"Lyle Fairfield" <ly***********@aim.comschreef in bericht news:11**********************@k78g2000cwa.googlegr oups.com...

Is there a reason for
DoCmd.TransferDatabase acLink, "Microsoft Access", strBEPath, acTable,
Tablename, Tablename, False?
I suppose MS might recommend it. That would likely mean that it's very
inefficient.
I try not to ask Access to do JET jobs.

If you can't find the problem perhaps you should consider a different
approach, perhaps some new code? Here's some airy stuff:

Hi Lyle,
Thanks for the 'airy stuff'. I am sure with a little adaption this approach will be faster indeed.
It does make sense to handle all the needed tables in a loop (like the unlink-code) when relinking the backend.

But I am using the code "DoCmd.TransferDatabase" as from Access 2.0 and it always worked fine.
I also use this code when I only need to link one or two tables.
This code workes fine and fast enough for me, except on my machine with Access2003.
Remember (or re-read) my problem or question: with Access2000 on my machine the *same* code is blazing fast.
Also on another machine with Access2003 the same code is blazing fast.

So, faster code or a different approach does *not* explain my problem....
I guess there is something the matter with my machine and I am determined to figure this out.

Nevertheless I will try your 'airy code', thanks again.

Arno R

[Tim Marshall]

Arno R wrote:

Nevertheless I will try your 'airy code', thanks again.

I think an established version of that code would be here:

http://www.mvps.org/access/tables/tbl0009.htm

When I do develop in Jet and include the capability to switch between
back ends, I've always used the above in A97 and A2003 and it just
blazes away.

I'd try it first and if it works quickly, I wouldn't worry about machine
differences - 8) you will go insane and start signing your posts as
"pcd". 8) 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[David W. Fenton]

"Lyle Fairfield" <ly***********@aim.comwrote in
news:11**********************@k78g2000cwa.googlegr oups.com:

Is there a reason for
DoCmd.TransferDatabase acLink, "Microsoft Access", strBEPath,
acTable, Tablename, Tablename, False?
I suppose MS might recommend it. That would likely mean that it's
very inefficient.
I try not to ask Access to do JET jobs.

Why not? I see no reason why you'd get any significant performance
increase by using DAO to do what TransferDatabase does. Surely
TransferDatabase is a direct interface to the same Jet interface
that DAO is, and it is much, much less code.

If there were a 50% performance improvement using DAO, well, that
might be worth it. But less than that, I think the more efficient
code is worth it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Arno R]

"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumschreef in bericht news:eq**********@coranto.ucs.mun.ca...

Arno R wrote:

>Nevertheless I will try your 'airy code', thanks again.

I think an established version of that code would be here:

http://www.mvps.org/access/tables/tbl0009.htm

When I do develop in Jet and include the capability to switch between
back ends, I've always used the above in A97 and A2003 and it just
blazes away.

Hi Tim,
Thanks, but that code is very different from Lyle's airy-code.
Lyle is *relinking* the BE, while your example is code to *refresh* links. Two different animals that is !!
What I am doing is relinking, *not* refreshing.
At one time (Access 2.0 time) I experienced problems with refreshing (that is long ago and maybe solved, but I doubt the latter....)
So I changed my code to delete links and link again.
Since that moment I *always* do so (and I have read more posts about problems while only refreshing links since).

I'd try it first and if it works quickly, I wouldn't worry about machine
differences - 8) you will go insane and start signing your posts as
"pcd". 8) 8)

I am still testing here. This morning I uninstalled Microsoft Office 2003 and installed again.
Result: No difference... Still the weird differences between Access versions. (and machines).
BTW: Uninstalling the Office suite immediately resulted in errors like 'default mail cliënt not found' and such...
What a drag ! This MS-install uninstall thing still does not work properly...

To solve my problem/questions I might even go back to a previous image (ghost) of my c-drive (which is a few month's old), but I am a bit reluctant to do so.
I am busy at the moment (need a few jobs done) and I can't lose too much time installing software again.
Must find out what I installed after making the 'ghost' ...(should keep a log of this !)

BTW: Don't understand your 'signing as "pcd" remark' ???
But I'm glad the lunatic is gone...

Arno R

[Tim Marshall]

Arno R wrote:

Hi Tim,
Thanks, but that code is very different from Lyle's airy-code.
Lyle is *relinking* the BE, while your example is code to *refresh* links. Two different animals that is !!
What I am doing is relinking, *not* refreshing.

For my own knowledge (as I said, I run PTQs against oracle most of the
time and do relatively little Jet development), what is the difference?

For example, the only Jet application I have in development (and have
been working on it for nearly 3 years - it's a hobby related thing) I'm
doing right now allows me to switch from one back end file to another.
So when I specify a new back end, is it not relinking to the tables in
that back end? Or is the proper term "refreshing"?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Arno R]

"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumschreef in bericht news:eq**********@coranto.ucs.mun.ca...

Arno R wrote:

>Hi Tim,
Thanks, but that code is very different from Lyle's airy-code.
Lyle is *relinking* the BE, while your example is code to *refresh* links. Two different animals that is !!
What I am doing is relinking, *not* refreshing.

For my own knowledge (as I said, I run PTQs against oracle most of the
time and do relatively little Jet development), what is the difference?

Access internally saves *more* than only the path (connection string) to the table.
Also field information and such is saved so it seems.
While refreshing the connection string it seems that Access is *not* always updating the 'other' data properly.

I am sure that refreshing works allright when the tabledesign has *not* changed.
I am *not* sure that refreshing works allright when the tabledesign *has* changed.
I have seen some very strange things happening, but only M$ knows what is really happening here...

So I delete the links (delete the attached tables) and make totally new links.
Now I am sure the links are good.

Regards,
Arno R