473,883 Members | 2,025 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Relinking backend fast with A2k, very slow with A2k3

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
Feb 4 '07 #1
13 4528
>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.....righ t?

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
Feb 4 '07 #2
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.OpenDa tabase(PathToBa ckend)
'(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.....righ t?
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\Offi ce\Msaccess.exe or to
C:\program Files\Microsoft Office2003\Offi ce11\Msaccess.e xe

Must be something different going on here???

Thanks
Arno R
Feb 5 '07 #3

"Arno R" <ar***********@ planet.nlschree f in bericht news:45******** *************** @text.nova.plan et.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
Feb 5 '07 #4
I have an open database-connection like
Set db = DBEngine.OpenDa tabase(PathToBa ckend)
'(This maintains the ldb-file)
...relink code here
Hum....interest ing. 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.....righ t?

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.Table Defs
strOld = mytables.Connec t
strBackPart = strGetDbTable(m ytables.Connect )

strTo = strReLinkDir & strBackPart

If Len(mytables.Co nnect) 0 Then
If Left(mytables.C onnect, 10) = ";DATABASE= " Then
mytables.Connec t = ";DATABASE= " & strTo
mytables.Refres hLink
If bolFirst = False Then
Set rstFirst = CurrentDb.OpenR ecordset(mytabl es.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\Offi ce\Msaccess.exe or to
C:\program Files\Microsoft Office2003\Offi ce11\Msaccess.e xe

Must be something different going on here???

Thanks
Arno R

Feb 5 '07 #5
"Albert D. Kallal" <Pl************ *******@msn.com wrote in
news:siPxh.9060 27$R63.106391@p d7urf1no:
>I have an open database-connection like
Set db = DBEngine.OpenDa tabase(PathToBa ckend)
'(This maintains the ldb-file)
...relink code here

Hum....interest ing. 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.....righ t?

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.Table Defs
strOld = mytables.Connec t
strBackPart = strGetDbTable(m ytables.Connect )

strTo = strReLinkDir & strBackPart

If Len(mytables.Co nnect) 0 Then
If Left(mytables.C onnect, 10) = ";DATABASE= " Then
mytables.Connec t = ";DATABASE= " & strTo
mytables.Refres hLink
If bolFirst = False Then
Set rstFirst =
CurrentDb.OpenR ecordset(mytabl es.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/
Feb 6 '07 #6

"David W. Fenton" <XX*******@dfen ton.com.invalid schreef 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.Co unt - 1
If Len(db.TableDef s(i).SourceTabl eName) <0 Then
DoCmd.DeleteObj ect acTable, db.TableDefs(i) .Name
End If
Next i
End sub
-----------------

Sub LinkTable (Tablename)
DoCmd.TransferD atabase 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.OpenDa tabase(strBEPat h)

LinkTable "TableFirst "
LinkTable "TableSecon d"

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
Feb 6 '07 #7
On Feb 6, 11:29 am, "Arno R" <arraNOcomS...@ tiscali.nlwrote :
"David W. Fenton" <XXXuse...@dfen ton.com.invalid schreef in berichtnews: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.Co unt - 1
If Len(db.TableDef s(i).SourceTabl eName) <0 Then
DoCmd.DeleteObj ect acTable, db.TableDefs(i) .Name
End If
Next i
End sub
-----------------

Sub LinkTable (Tablename)
DoCmd.TransferD atabase 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.OpenDa tabase(strBEPat h)

LinkTable "TableFirst "
LinkTable "TableSecon d"

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.TransferD atabase 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.Creat eTableDef(aTabl es(z))
With tdf
.SourceTableNam e = aTables(z)
.Connect = ";DATABASE= " & NewLocation
End With
CurrentDb.Table Defs.Append tdf
Next z
CurrentDb.Table Defs.Refresh
'Debug.Print CurrentDb.Table Defs.Count
End Sub

Private Sub Unlink(ByRef TableList$)
Dim r As DAO.Recordset
Dim Name$
'Debug.Print CurrentDb.Table Defs.Count
TableList = ""
Set r = CurrentDb.OpenR ecordset("SELEC T Name FROM mSysObjects
WHERE Type = 6")
With r
While Not .EOF
Name = .Fields(0).Valu e
CurrentDb.Execu te "DROP Table [" & Name & "]"
TableList = TableList & Name
.MoveNext
If Not .EOF Then TableList = TableList & ","
Wend
End With
Set r = Nothing
CurrentDb.Table Defs.Refresh
'Debug.Print CurrentDb.Table Defs.Count
End Sub

Public Sub test()
Link "C:\Documen ts and Settings\Lyle Fairfield\My Documents\Acces s
\northwind.mdb"
End Sub
Feb 6 '07 #8

"Lyle Fairfield" <ly***********@ aim.comschreef in bericht news:11******** **************@ k78g2000cwa.goo glegroups.com.. .
Is there a reason for
DoCmd.TransferD atabase 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.Transfer Database" 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
Feb 6 '07 #9
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
Feb 7 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
2900
by: Neil | last post by:
I have a very puzzling situation with a database. It's an Access 2000 mdb with a SQL 7 back end, with forms bound using ODBC linked tables. At our remote location (accessed via a T1 line) the time it took to go to a record was very slow. The go to mechanism was a box that the user typed the index value into a combo box, with very simple code attached: with me.RecordsetClone .FindFirst " = " & me.cboGoTo If Not .NoMatch Then Me.Bookmark...
16
2693
by: John | last post by:
Hi All, I have two backend databases that area link to a frontend database where information is entered, retrieved and deleted. The information accessed from the frontend database is coming from both databases. Both backend databases are at least 225MB each, therefore accessing data is slow and running queries are slow and frustrating, this causes the databases to crash at times. At the beginning I though it was a network connection, so...
6
2542
by: Don Leverton | last post by:
Hi All, I've got a situation where I am developing an Access 97 app for a client, and am in the "beta testing" stage. I have split the app up, using the DB splitter, into front-end /back-end for the usual reason ... so I can mess with form/report revisions. Since splitting, I've had issues with re-linking the tables ... as it relates to the completetely different "My Documents" data paths on several different Win98 / Win XP computers.
0
1903
by: Rob | last post by:
I need to allow a user with the default "Full Data User" permissions on the front-end database to relink to a table in a backend to which he has the same permissions. (I'd like to do read, update, insert, delete, which are the DEFAULTS for a "Full data user", but just so you know I have also added "Administrator" for this group in front and backends, just to try and figure out the problem and it didn't help). Anyway I cycle through the...
19
2468
by: David W. Fenton | last post by:
I'm setting up a project for a client on a new Windows Terminal Server. The application is currently in A2K, but the sysadmin does not want to install that, he wants to install A2K3, because Office 2K3 is the organization's new standard (they want to be fully converted within the new calendar year). What issues will I have, if any, with A2K3 on WTS? Anything different from A2K? Anything not specific to WTS that I need to know about...
19
4355
by: Mark 123 | last post by:
I just read at http://groups.google.com/group/microsoft.public.access.forms/browse_frm/thread/766ba7b493eacb63/8a7d5504ee848c21?lnk=st&q=migrate+access+97+to+access+2007&rnum=1#8a7d5504ee848c21 that if you have a spilt db using SQL server rather than jet, the front-end's can connect to the backend SQL server over the internet. Has anyone had experience with this? We are thinking of migrating an commercially sold Access97 runtime app to...
1
1654
by: shumaker | last post by:
I just need to know gnerally what commands/api I would need to look up documentation for, if this can be done at all. I have some users who take copies of an Access database home with them to do analysis. I am going to split the database however, and that means that they will have to copy the backend and then relink to that local copy in their frontend when they are not on the network. In other words, manually: Copy backend to local...
6
1935
by: bg_ie | last post by:
Hi, My company's backend is located at a location with a long address, something like - //our_servers/server_number_one/our_department/our_devision/ our_results/our_databases/backend.be I have tested the very same backend on another server with a sort path location at it works fine, but at the location above it is extremely
9
2151
by: Salad | last post by:
I have access, for testing at my client's site, a Win2000 computer running A2003 retail. He recently upgraded all of his other machines to DualCore Pentiums with 2 gig ram and run A2003 runtime. I believe all current SPs for Windows and Office are installed on the fast machines. I have 1 process/subroutine that has worked for a couple of years without a problem. It works fine on the testing (slow) machine. The process checks a folder...
0
9786
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11126
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10836
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9568
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7964
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5794
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4607
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4211
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.