Convert to ADO for SQL Server back end

  • Thread starter robert d via AccessMonster.com
  • Start date
R

robert d via AccessMonster.com

I've read through some posts on the ADO vs. DAO question. Most seem to be
around a year or so old.

So: I have an Access app that uses almost exclusively unbound forms and DAO
for data access. A potential client won't accept the Jet backend and demands
SQL Server.

I did some preliminary work and upsized my Jet tables to SQL server. I also
created ODBC links to the SQL Server tables from my front end. No conversion
of DAO to ADO.

Everything seemed to work pretty well. In fact, the app runs quite a bit
faster accessing data from SQL server than it does from the Jet backend on a
server.

So, since I would have to convert about 400 DAO recordsets, I'm not sure I
want to bother. Maybe the app would be faster with ADO to SQL Server, but it
is currently faster using DAO to SQL Server than DAO to Jet. So the point is,
it is not slower. Maybe that means nothing, but on the other hand, I think
it means I don't have an immediate problem. So why kill myself doing the
conversion.

Thoughts, suggestions are greatly appreciated.
 
S

Sylvain Lafontaine

By using unbound forms, all of the work required to execute a query is done
exclusively by the SQL-Server on its side and only the final results are
transferred back to the application in a single resultset for each query.
(You may have multiple resultsets for a form if it have multiple queries (or
if a query contains multiple Select statements) but each select query will
give back in a single resultet from SQL-Server.)

So, in your case, using ADO instead of DAO will achieve little or no
improvement at all. The application might even run a little slower but the
difference in speed will be so small that it will be practically impossible
to mesure.

You shouldn't make the conversion, it will be a lost of time.
 
A

aaron.kempf

your client is smart; MDB is a piece of shit technology

DAO is a piece of shit technology
hang a crash and hang and crash

I would reccomend changing them; it shouldn't take you that long.. just
be careful with find and replace.

i'm just curious; what are you doing with recordsets? update/delete or
just navigating through them like cursors?

Use Access Data Projects; it is a complete pain in the ass; but a much
better long term solution
 
M

Mary Chipman [MSFT]

The rule more or less is, "for Jet objects, use DAO, for SQL Server
objects, use ADO".

Performance-wise, use pass-through queries as much as possible (you
can manipulate them in DAO code). Also use Jet to cache static data in
local tables so you don't have to make a round trip over the network
to fill a combo box. Get into the mindset that you're creating a web
app or a .NET, except with Access as the FE instead of a browser.

--Mary
 
A

aaron.kempf

the general rule ACTUALLY is

its a lot easier to use a sproc in ADP than a Sql Passthrough.
i mean-- about 10 times more powerful.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top