Re: Access 2007 - Anyone having trouble with really large conversions?

Hi Andy,

"ARC" <andy@xxxxxxxxx> schrieb im Newsbeitrag
news:eYnxi.14760$eY.13067@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Datareader, dataset, else?
    ... A DataReader is most closely analogous to a Read-Only/Forward-Only Recordset ... > I'm writing my first real asp.net app at my job, ... > seems like I have to choose between using a SQldatareader or a dataset. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Accessing External data
    ... I need to save data from an Access APP with no TABLES ... The values of the subform's controls TAG need to be saved / loaded. ... The MASTER Form and the rest of the APP looks at CONstants - which I'd like ... I'm struggling to create a recordset here ...
    (microsoft.public.access.modulesdaovba)
  • Re: Run-time Error 3704 in VB6
    ... > At this point for certain queries it reports that "the operation is not ... > I assure you that the recordset is still open. ... > fine I think until we upgraded to SQL 2000 a few months back ...
    (microsoft.public.data.ado)
  • Re: Application crashes with "On Error Resume Next"
    ... The code is intended to move data from one recordset to another - ... however the "ChangedCarveOuts" recordset is built using a stored proc ... Next is then to allow the app to skip any potential 3065 (Item cannot ... ran the app on a machine at the client site that uses MDAC ...
    (microsoft.public.vb.bugs)
  • Re: Passing an ADO recordset using remoting
    ... > I have inherited an existing Windows application written in VB6 with all ... DL embedded in the one app. ... > I have created a server component to return a disconnected recordset (not ... > dataset) to the client using TCP remoting. ...
    (microsoft.public.dotnet.framework.remoting)