Access 2007 - Anyone having trouble with really large conversions?

[ARC]

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

[Sascha Trowitzsch]

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

[ARC]

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" <[e-mail] 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