Re: Jet >SQL conversion

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Albert D. Kallal (PleaseNOOOsPAMMkallal_at_msn.com)
Date: 07/31/04 

Date: Sat, 31 Jul 2004 00:18:46 -0600

"Kevin" <BudStJohn@hotpop.com> wrote in message
news:410AFE9D.6070505@hotpop.com...

> Oh, thank you Albert! I was reacting to Rick Dobson's statement on page
> 1 paragraph 1 of Programming Access 2003: "While DAO can still perform a
> few selected tasks for the Jet engine that are impossible or very
> difficult to do with ADO, accessing data from sources other than Jet
> using DAO can be very awkward or impossible depending on the context."
> Other statements reinforce this.

The above is actually true to a point. You can for example build a dao
connect object (this is called jet direct), but ado is more suited to this.
The same applies to ado, and building, or executing stored procedures on the
server side (ado is again somewhat better).

However, for existing stuff, and most stuff, dao is ok. I mean, sure...if
you start a new application, then I certainly would suggest using ado when
working with sql server, but really, linked dao tables work just fine.

> But the tip about combo boxes is interesting:
> many of my forms have a dozen or so combo boxes on them, and some load
> pretty slowly, even with a one-record recordset. I wonder if populating
> the rowsource OnEnter into the combobox control and dropping it OnExit
> would speed things up for those that don't use dataless keys?

You certanly can consier the above. As metnoend, after just a few combo
boxes, the form will start to load slow. However, if you read carauflay, not
only do I mention to create a vew, but ALSO to make sure you do NOT use sql
in the combo box souce, but JUST the view you created on the server side.
This approach speeds things up quite a bit (saves the sql load and
translation). So, building a view and useing that for the combo souce does
make things more snappy. This means you can get away with more combo boxes
and not having to write a lot of code. The fastest way to solve the combo
box load stuff is to use a call back function, but then we are talking more
work.

> With the exception of the few forms which load slowly, as above,
> performance hasn't been an issue, and this app can have several hundred
> thousand records in a number of related tables. It's the occasional
> compact and repair which pops up that's more of a reason to move to SQL.
>
> I'm afraid Mr. Dobson gave me quite a scare... thanks for ameliorating it!

You are correct that upsizing to sql is some work, but the secret is to get
things working, and the attach each performance bottle neck one by one. And,
of course from a reliability and security point of view...sql server is
certainly a good path to go. 

-- 
Albert D. Kallal   (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

Relevant Pages

  • Re: Use DAO or ADO?
    ... SQL Server is more popular than Jet. ... real world of development, Dot Net", ADO has been superceded by ADO.NET ...
    (comp.databases.ms-access)
  • Re: Can AlbertCos tables be accessed?
    ... SQL to FoxPro really did the trick, ... I don't really see your point on ADO, ... because it's not designed around Jet. ... much because VB needed a database layer as it was the needs of ...
    (comp.databases.ms-access)
  • Re: Use DAO or ADO?
    ... tables to a jet backend to open a ado recordset. ... will ALSO work for mysql, oracle and sql server if I take that sql ... engine that they don't understand who want to avoid Jet at all ...
    (comp.databases.ms-access)
  • Re: Index implementation design
    ... I'm not a big fan of ADO, but it works, so this is just a "FYI". ... It's possible to tell ADO(or rather Jet) to access your file ... Dim sql As String ... This allows you to export your data to MDB file, ...
    (comp.lang.basic.visual.misc)
  • Re: WITH COMPRESSION
    ... People who used Jet before ... ADO were perfectly fine with DAO, and got the benefit of all the ... I would think the number of developers who knew real SQL 92 syntax ...
    (comp.databases.ms-access)