Just curious if anyone is having issues with Acc 2007 once the number of
objects and complexity increases? I have a fairly large app, with many
linked tables, 100's of forms, queries, reports, and lots of vba code. I'm
nearly finished with re-doing my app in access 2007, and just imported an
add-in program, which has added even more forms, queries and linked tables.
Every so often now, after opening many different screens, I'll occassionally
get errors when opening a recordset for a linked table. The error will tell
me that it can't find the key value, or something to that effect. If I look
in the immediate window, I can print to the screen the table's key id, but
if I try to print any other fields in the table, I get an "Item not found in
collection" error. I finally found that if I refresh all table links, the
problem goes away. However, it does still crop up from time to time even
after refreshing the links.
I'm wondering if this has to do with having so many linked tables, and the
overall number of objects and complexity. Obviously, if this continues, I'll
be leary of going live with this app. The other thing I've wondered about is
whether it has to do with using a .accdb file as apposed to the access 2007
..mdb file. When I converted, I did choose the .accdb file, and started using
the rtf option in some memo fields.
Anyone experiencing the same in Access 2007?
Many Thanks,
Andy 2 3771
Hi Andy,
"ARC" <an**@andyc.comschrieb im Newsbeitrag
news:eY******************@newssvr13.news.prodigy.n et...
Just curious if anyone is having issues with Acc 2007 once the number of
objects and complexity increases? I have a fairly large app, with many linked
tables, 100's of forms, queries, reports, and lots of vba code. I'm nearly
finished with re-doing my app in access 2007, and just imported an add-in
program, which has added even more forms, queries and linked tables.
Every so often now, after opening many different screens, I'll occassionally
get errors when opening a recordset for a linked table. The error will tell me
that it can't find the key value, or something to that effect. If I look in
the immediate window, I can print to the screen the table's key id, but if I
try to print any other fields in the table, I get an "Item not found in
collection" error. I finally found that if I refresh all table links, the
problem goes away. However, it does still crop up from time to time even after
refreshing the links.
I'm wondering if this has to do with having so many linked tables, and the
overall number of objects and complexity. Obviously, if this continues, I'll
be leary of going live with this app. The other thing I've wondered about is
whether it has to do with using a .accdb file as apposed to the access 2007
.mdb file. When I converted, I did choose the .accdb file, and started using
the rtf option in some memo fields.
Anyone experiencing the same in Access 2007?
Many Thanks,
Andy
I have no experience with databases that large under Access 2007.
But in general overall performance is much less than under Access 2003. ACCDBs
are slow, but MDBs developed under former versions running on A2007 are
incredible slow compared to A2003. Meantime this behaviour has been confirmed by
many other users. Forms with underlying tables containing many records sometimes
take 5-10 seconds to open compared to less than 1 second under A2003.
To me it looks like if there was a problem with the new Recordset2 object. Just
do a test: Remove the ACE reference in a project, set another to older DAO3.6,
create a recordset for a table or query, set a forms recordset to the created
recordset (Set Me.Recordset = rstCreated) in runtime, and in immediate window
type: ? TypeName(Forms!frmXY.Recordset). Result will be "Recordset2"!
So Access 2007 always seems to use Recordset2 objects for its forms and reports
regardless of what you set for them. But how does this work? Obviously a DAO3.6
recordset is casted to a ACE recordset internally. I guess that this type
casting might leed to performance problems. But it's just an idea.
Have to wait for SP1 and hope such problems will be gone then.
Ciao, Sascha
Thanks for your time in replying, Sascha. So if I have a number of workspace
type databases, it sounds like I should definetely use the 2007's .mdb, and
not the .accdb?
I sure hope they release a service pack soon, as I'm nearing completion of
the 2007 version, and can't go back now.. :(
Thanks again, Andy
"Sascha Trowitzsch" <ng@moss-soft.dewrote in message
news:fa**********@online.de...
Hi Andy,
"ARC" <an**@andyc.comschrieb im Newsbeitrag
news:eY******************@newssvr13.news.prodigy.n et...
>Just curious if anyone is having issues with Acc 2007 once the number of objects and complexity increases? I have a fairly large app, with many linked tables, 100's of forms, queries, reports, and lots of vba code. I'm nearly finished with re-doing my app in access 2007, and just imported an add-in program, which has added even more forms, queries and linked tables.
Every so often now, after opening many different screens, I'll occassionally get errors when opening a recordset for a linked table. The error will tell me that it can't find the key value, or something to that effect. If I look in the immediate window, I can print to the screen the table's key id, but if I try to print any other fields in the table, I get an "Item not found in collection" error. I finally found that if I refresh all table links, the problem goes away. However, it does still crop up from time to time even after refreshing the links.
I'm wondering if this has to do with having so many linked tables, and the overall number of objects and complexity. Obviously, if this continues, I'll be leary of going live with this app. The other thing I've wondered about is whether it has to do with using a .accdb file as apposed to the access 2007 .mdb file. When I converted, I did choose the .accdb file, and started using the rtf option in some memo fields.
Anyone experiencing the same in Access 2007?
Many Thanks,
Andy
I have no experience with databases that large under Access 2007.
But in general overall performance is much less than under Access 2003.
ACCDBs are slow, but MDBs developed under former versions running on A2007
are incredible slow compared to A2003. Meantime this behaviour has been
confirmed by many other users. Forms with underlying tables containing
many records sometimes take 5-10 seconds to open compared to less than 1
second under A2003.
To me it looks like if there was a problem with the new Recordset2 object.
Just do a test: Remove the ACE reference in a project, set another to
older DAO3.6, create a recordset for a table or query, set a forms
recordset to the created recordset (Set Me.Recordset = rstCreated) in
runtime, and in immediate window type: ? TypeName(Forms!frmXY.Recordset).
Result will be "Recordset2"!
So Access 2007 always seems to use Recordset2 objects for its forms and
reports regardless of what you set for them. But how does this work?
Obviously a DAO3.6 recordset is casted to a ACE recordset internally. I
guess that this type casting might leed to performance problems. But it's
just an idea.
Have to wait for SP1 and hope such problems will be gone then.
Ciao, Sascha
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Frnak McKenney |
last post by:
Back when computer dinosaurs roamed the earth and the precursors to
today's Internet were tiny flocks of TDMs living symbiotically with
the silicon giants, tracking access to data processing...
|
by: WalterR |
last post by:
This is my first time here, so there may be earlier relevant threads of
which I am unaware. Though my experience with DB2 is not extensive,
such as it is was under OS/390 or equ. My main...
|
by: deko |
last post by:
Do I get more scalability if I split my database? The way I calculate
things now, I'll be lucky to get 100,000 records in my Access 2003 mdb.
Here some math:
Max mdb/mde size = 2000 x 1024 =...
|
by: Robin Haswell |
last post by:
Hey people
I'm an experience PHP programmer who's been writing python for a couple of
weeks now. I'm writing quite a large application which I've decided to
break down in to lots of modules...
|
by: David1129 |
last post by:
I'm working on testing a large application with Access 2007. It has been running under 2003 and XP with no obvious errors.
However with 2007 many forms seem to have lost the ability to filter....
|
by: ARC |
last post by:
Hello All,
I have a major application for quoting and invoicing that is written in
Access 97 (both front and back ends). I have a user base of over 300
customers, and currently I don't require...
|
by: mservice |
last post by:
Hi there, newbie to Access, I would normally use cursors in SQL Plus to do this type of thing, however I have been tasked with modifying an access 2000 application and am stuck on the VB code/query...
|
by: Luvin lunch |
last post by:
Hi All,
People keep saying things like "Access is a nightmare" or "Access is
hell" but the only people I hear this from are peopel who aren't that
experienced in using Access. The most I can...
|
by: prakashwadhwani |
last post by:
Hi !! I'm about to develop a new project for a client.
Should I go about it in Access 2003 or 2007 ? Purchasing it either
for me or for my client is not a major consideration here ... what I'd...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |