[AccessD] Code Library, Sample Database, Etc.

ADO is very similar to DAO, here's the big differences:

Connecting.  In DAO, you have to create a workspace, then a database,
then connect with a recordset, so you have three objects you are dealing
with.  In Access, you can just use CurrentDB, which already has it's own
workspace.

In ADO, you have a connection object then a recordset object.  Just
connect the connection object and you're off and running.  The nice
thing is that ADO can connect to all sorts of stuff, text files, excel
files, word docs, .mdbs, ODBC connections, etc.

One trick when dealing with Access, use the following:

Function DBConnect(byref cnn as ADODB.Connection)
Set cnn=new ADODB.Connection
Cnn.provider="Microsoft.Jet.OLEDB.4.0"
Cnn.Open "PathToYourDatabase"
End function

Almost every one of my projects uses the above function.  Most of them
have the db path hardcoded as I show above.  When working within Access,
you don't need to create a connection object, just use
CurrentProject.Connection 

Wildcards.  ADO doesn't use * (or the other wildcard symbols).  In ADO,
you use %.  Which can be confusing within Access, because the querries
in access will use *, but running SQL against an ADO recordset will
require %.

Recordset types.  When opening a recordset, you have to set the cursor
type and the lock type.  Personally I use adOpenKeyset for ALL of my
recordsets, and then adLockOptimistic or adLockReadOnly (if you only
need to read data, it's better to only put a read lock on the
recordset).

Directly to a table.  In ADO, if you are going to just open a table,
instead of opening an SQL statement, at the end, you add
adcmdTableDirect.  Our old version of Oracle requires that even with an
SQL statement.  Go figure.

There's obviously more to learn with ADO, but these are the biggies,
IMO.  Once these are under your belt, you'll find ADO to be just as easy
as using DAO (sometimes easier).  On connecting to different
datasources, I used to have a website in my favorites that listed a ton
of connection strings and provider strings, I looked, it's not there
anymore.... sorry.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Barbara Ryan
Sent: Tuesday, February 20, 2007 6:42 AM
To: Access List
Subject: [AccessD] Code Library, Sample Database, Etc.

I have been using DAO in my Access databases but now am trying to learn
ADO.  I have found "snippets" of code on various websites, but was
hoping to find a sample database showing how to "tie it all together".

In your opinion, what is the best "Best Access Practices" list?  (e.g.,
white papers, etc.)

If anyone has a "code library" that they would like to share, I would be
more than happy to see it!

I have learned SO much from all of you --- even if many of the
discussions have been "over my head" ---- Thanks!

Thanks!
Barb Ryan


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com