Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 2nd, 2005, 05:12 AM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADOX/MDAC Problem

Hi folks

My client has a “split” architecture Access database – the back end on a server and the front end on users desktops. All was well until the client changed to Windows server 2003, from 2000.
Since that change, the ADOX.catalog object takes so long to “load” that it is not possible (in practical terms) to access a procedure to use its command as a command object and execute it.
In order to show what I mean, please see the 2 vba code samples below, which merely iterate through the stored procedures.
The old DAO version works in about 2 seconds, the ADO version takes up to 10 minutes. I also notice that the time taken by the ADO routine is dependant upon the amount of data in the tables, whereas the DAO routine seems to be unconcerned whether the tables contain 2 rows or 2000 rows.
Either version works quickly if both front and back ends are on an XP or Windows 2000 PC, and worked with Server 2000.
The Windows Server 2003 is using MDAC 2.80, whereas XP(SP2) pcs use 2.81, and I think Windows 2000 server used MDAC 2.5. Is there something about the 2.80 version that is causing this behaviour? Or is there another explanation that anyone can suggest?
Thanks
Arthur

Dim db as DAO.Database
Dim qdf as DAO.QueryDef

Set db = CurrentDb()
For Each qdf in db.QueryDefs
    Debug.Print qdf.Name
Next qdf

Set qdf = nothing
Set db = nothing

Dim cat as ADOX.Catalog
Dim prc as ADOX.Procedure

Set cat = New ADOX.Catalog
For Each prc in cat.Procedures
    Debug.Print prc.Name
Next prc

Set prc = nothing
Set cat = Nothing
 
Old December 2nd, 2005, 09:10 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Try the MDAC downloads page: http://msdn.microsoft.com/data/mdac/...s/default.aspx

And the installation page: http://msdn.microsoft.com/library/de...dacinstall.asp

There is an SP1 for 2.8 that might help.

HTH

mmcdonal
 
Old December 2nd, 2005, 10:39 AM
Registered User
 
Join Date: Dec 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

as far as I know, the SP1 for 2.8 is not compatible with server 2003, the updates needing to come from a server 2003 service pack.
Quote:
quote:Originally posted by mmcdonal
 Try the MDAC downloads page: http://msdn.microsoft.com/data/mdac/...s/default.aspx

And the installation page: http://msdn.microsoft.com/library/de...dacinstall.asp

There is an SP1 for 2.8 that might help.

HTH

mmcdonal




Similar Threads
Thread Thread Starter Forum Replies Last Post
installing MDAC ubsacc2004 ASP.NET 1.0 and 1.1 Basics 3 April 26th, 2006 10:47 AM
A problem with running MDAC file amouzeshgah BOOK: Beginning ASP.NET 1.0 4 March 10th, 2006 12:52 PM
ADOX problem toshesh VB Databases Basics 1 December 23rd, 2005 10:34 AM
MDAC johanyu ASP.NET 1.0 and 1.1 Basics 1 October 6th, 2004 03:22 PM
Detect MDAC dunnie VB How-To 8 July 17th, 2003 03:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.