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
|
