Ace or Jet

[gemma-the-husky]

Is one more "efficient" that the other?

We have a database. An important form is designed as a container, with two continuous subforms. The current event of the left subform, causes the right subform to refresh, by changing a global variable, and requerying the right subform which uses this global variable.

With fe/be in accdb format, refresh of the right form appears slow. The "calculating" legend comes up, and takes a while to clear.

converted both fe and be to mdb format, and it's working fine.


So it seems that the mdb (JET) is clever enough to retrieve just the data matching the function, but the accdb (ACE) is possibly bringing the whole dataset across for the local machine to then filter.

Does this sound feasible?

 

[Banana]

I would not expect this to be case - theoretically, ACE should be basically an upgraded version of JET.

Think you can make a small repo file that show this problem? I'd love to see this.

 

[gemma-the-husky]

I would not expect this to be case - theoretically, ACE should be basically an upgraded version of JET.

Think you can make a small repo file that show this problem? I'd love to see this.

not really.

it's a big app at a client's. I wasn't sure about this analysis tbh.

They reported very slow running on one/maybe two terminals only.

We checked the spec of the computer (1Gb memory), closed all other apps down to see. (the machine was supporting 2 screens). Finally we tested the performance by changing everything from accdb to mdb, and at this point the delay went away, and the refresh was virtually instant.




now, the current event for the left sub form does a complicated series of dlookups and other stuff to set some reference data in global variables, and on the parent form, and finally requeries the right sub form.

tbh, I didn't realise I had put quite so much code in the current event, until I rechecked to answer your enquiry. It's about 6 months since I've done anything to this app.

I just assumed from the "calculating" message that it was the requery of the right subform producing the delay, but I suppose it may not be. I could put some timers on the lookups to see if its one of those, but unless it reappears, I won't bother.

 

[LPurvis]

Can I just confirm - when you say "changing everything from accdb to mdb" did you also then either switch to a PC running Access 2003 or earlier (or install 2003 on that previously slow PC)?
If not - then you're still using ACE. The format of the application won't change the engine that's running as part of your Access installation.
I can believe the format of the application makes a difference (we're all XML based files now we're living in the post 2003 future lol ;-)
I wouldn't necessarily expect it to be slower - but I have seen as much.

Cheers.

 

[the_net_2.0]

(we're all XML based files now we're living in the post 2003 future lol ;-).

LP,

Do you know what impact XML will have o
n the future of web-based technology and work? I read an article just the other day that XML is more compatible with all the browsers, and that there are actually full websites out there built with XML files. Do you have anything to say on that?

 

[LPurvis]

I'd say that you can't really view XML in that way.
It's essentially only a standard. Fairly revolutionary in data terms - but just as html was in internet formats.
Using it as an underlying file format (still somewhat obfuscated by binary data) doesn't ultimately mean that much in the grand scheme of things. Desktop users' experiences won't be affected. It won't change interaction with browsers.
It's just a standard - and not even a new one. It became the buzzword of the early naughties - but it's inception is firmly routed in the mid 90's.

Web interaction will always be based on functionality implemented by clients/browsers/servers. the underlying data formats aren't desperately relevant. Not so far anyway.

 

[gemma-the-husky]

Can I just confirm - when you say "changing everything from accdb to mdb" did you also then either switch to a PC running Access 2003 or earlier (or install 2003 on that previously slow PC)?
If not - then you're still using ACE. The format of the application won't change the engine that's running as part of your Access installation.
I can believe the format of the application makes a difference (we're all XML based files now we're living in the post 2003 future lol ;-)
I wouldn't necessarily expect it to be slower - but I have seen as much.

Cheers.

no it's still running on A2007.

we just changed the format of the front end and back end from accdb to mdb. ie - by selecting the save as 2003 option.

 

[LPurvis]

Yeah fair enough.
Then the topic of the thread is really Access 2007 Vs Acccess 2003 formats.
(Or more precisely ACCDB Vs MDB.)
Data wise it's ACE Vs ACE. ;-)

 

[Galaxiom]

I design in Access 2007 but I have yet to see anything in accdb that makes me even consider changing. Dave's observation is yet another reason to stick with mdb.

I have also heard of bloating problems in accdb. The inability to directly sign accdb and forcing the extra steps of packaging and extracting is also a negative.

Just what is in accdb that would make anyone use it?

 

[Banana]

RE: XML

I want to point out that it's not the only standard. JSON is another popular standard and argued to be easier to use than XML. As Leigh says, it's going to be always something new coming out to serve a purpose that the old standard didn't do a good job. Personally, I'm inclined to think there's a tendency to over-XML everything.

RE: Why use ACCDB?

Well, for 2007, I can honestly say there's very little benefits. Things only get really interesting when we get into 2010. Few reasons:

1) Ability to embed a report in a form (aka a subreport in form)
2) New Navigation Control (think of as combination of tab control and subform)
3) Trusted Documents which at least being some sanity to the silly Trusted Policies
4) Application Parts to help modularize and make it easy to quickly add templates below the file level
5) New WebBrowser control.

There's surely more and IIRC, those would require an ACCDB even though 2010 continue to work with MDB just as natively. I also should point out a common source of confusion: With 2010, there's no such thing as "2010 ACCDB format"; it still uses 2007 ACCDB file format but adds new features that 2007 cannot use so trying to share an ACCDB between 2007 and 2010 may be problematic but in this case, it's probably just better to buy single license of 2010, and distribute 2010 runtime engine because 2007 is basically Access 95.

 

[the_net_2.0]

With 2010, there's no such thing as "2010 ACCDB format"; it still uses 2007 ACCDB file format but adds new features that 2007 cannot use

then why do my 2007 files become immediately corrupted after I open them in 2010? I don't use 2010 because:

• accdb opened in 10
• changes or no changes made
• file closed
• file opened in 07
• message box "database in unrecognized format"
• db corrupted. has to be scrapped

anyone have tips on that?

 

[Banana]

First, it's not really corruption per se. Bad behavior, perhaps, but it's still usable. FMS Inc. has a write-up on this issue[/url].

Second, it illustrates my earlier point - if you want to use 2010, you have to do it exclusively (e.g. distributing the 2010 runtime instead of developing on 2010 and giving to 2007 clients). It's just not worth the headaches maintaining 2007 and 2010 side by side - it makes far more sense to have 2003 and 2010 because at least there's some sanity WRT file format (since MDB can't have 2010-only feature that would cause same problems in 2003 as it has for 2007/ACCDB). 2007 is just best forgotten just like Access 95, TBQH.

 

[the_net_2.0]

I'll try this as soon as I can! Thanks much Banana!

 

[Galaxiom]

Is there any way to force 2010 to avoid using features that 2007 does not support? Or does the developer need to know what not to use? Other than using an mdb of course.

I think Banana is on the money. Either stay with mdb or go the whole way with 2010 if you want to use accdb. It sounds like 2007 was the unfinished version of what 2010 eventually became.

 

[Banana]

AFAIK, no. In theory, Microsoft was supposed to design the ACCDB so that if you added 2010-only features, you could still open it in 2007 but you couldn't use anything that used those 2010-only features or maybe view it as read-only. This way, you could have several 2007-compatible forms with maybe one special 2010-only form and it'd be usable to anyone using either 2007 or 2010.

Unfortunately, it was one of those ideas that sounds cool in abstract but once implemented, it's a mess. Even if we did not have this problem that the_net_2.0 was seeing with his ACCDBs, it's trouble enough to keep track of what is 2010 features and thus is unusable in 2007. Hence, we're better off either using 2007 to deploy 2007 clients or buying a single license of 2010 and using runtime instead of opening it in 2007.