ADODB reference?

[Corta]

I am working wtih Access 2007 and writing my VBA code that connects to
my backed database wtih ADODB (ADOX). I have found lots of examples,
many of them work, but I am looking for a reference that explains all
of my options for connection properties, data source syntax, etc.
There are so many ways to accomplish the same thing, so I am looking
for the definitive reference when using ADODB in Access 2007 so I can
learn about all of the methods, classes, etc.

Thanks,

Corta

 

[Allen Browne]

In Access 2007, open the code window, click the Help button on the toolbar,
and go to:
HV01227787

Here's a few more of those examples you talked about:
http://allenbrowne.com/func-ADO.html

Seriously, though, you may be better learning the native Access library DAO.
ADO is obsolete, replaced by the very different ADO.NET which is well suited
to a variety of data sources but not designed specifically for Access.

 

[Corta]

Allen, thanks very much for your tips. I am fairly new to Access
programming, but have other programming experience in Visual Studio
2005 with VB and ADO.Net on SQL Sever Express 2005. I was under the
impression that DAO was from Access 2003 and ADO was its replacement
in 2007. I had read a couple articles about Microsoft phasing out DAO
in the future and all the latest books on Access 2007 seem to be
covering ADO and not DAO. Not sure I have all those fact correct, so
any corrections are much appreciated. When I am going to just be
using Access 2007 what are my choices for database connectivity? I
assume ADO and DAO, ODBC, etc.? I thought I would take the plunge and
learn all the various options in ADO for Access 2007 hoping there
would be some overlap in syntax with ADO.net, but that hasnt turned
out quite the way I had hoped. I will always typically use unbound
forms and SQL statements wtih a disconnected recordset, if that
matters. Your thoughts and input are appreciated.

Corta

 

[Allen Browne]

Okay, your comments make sense, given where you are coming from. If you are
already familiar with the way ADO works, then of course you want to leverage
that knowledge.

DAO was designed specifically for Access. The "A" in DAO *is* Access, and
it's been there since version 1.

When MS released Access 2000, they made ADO the default instead of DAO, and
released articles implying that DAO was obsolete and ADO was the future.
While ADO was certainly more generic, there were few real advantages over
DAO, and you still had to have DAO to do lots of stuff. Consequently most of
us stayed with DAO, and Microsoft put the DAO reference back in by default
in A2003.

As you found ADO.NET is quite a different beast, so it's fair to day ADO is
now obsolete. Access 2007 contains an enhanced version of JET and of DAO ,
so the new DAO (acedao.dll) is the preferred approach in A2007. Microsoft
and the experienced developers are finally singing from the same song sheet
again. (That's assuming we are talking JET tables here.)

Unbound forms have a place in very limited scenarios, but if you have never
worked with bound forms, I would strongly encourage you to do so. Much of
the rapid development power in Access comes from the way bound forms work.
Text boxes automatically accept only the right type of data, without having
to write validation (e.g. is it a valid date?) The form's BeforeUpdate event
perform's record-level validation (something I want on every form I create.)
The controls have an OldValue so you can compare to what was there before.
The events are just surpurb. And that's before we start talking about
subforms that automatically (codelessly) display the rows of related data,
filters, finding in the RecordsetClone, etc, etc. You may not know what you
are missing out on.

 

[Corta]

Allen, all excellent points. Thank you so much for taking your time
to help. I am sure others will find your input useful as well. I
will play around with DAO in A2007 after I finish my current test
project and see if I can get my skills up to speed on a new sample
project.

I had one other question about ADO connection string syntax in case
you know. In my sample development project I have a front end
A2007 .accde with all my forms, etc in it and a back end A2007
database with the tables, etc located on my file server on the f:
drive.

In my front end UI on the form load event I have the following
connection string setting up my ADO recordset.

Private Sub Form_Load()
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;"
strConnection = strConnection & "Data source=F:\FS1\Dev\BE.accdb"
.... the rest of my code...

If I set a password for my backed A2007 database, do you know the
syntax for passing the password in my connection string?

Thanks,

Corta

 

[RoyVidar]

Allen, all excellent points. Thank you so much for taking your time
to help. I am sure others will find your input useful as well. I
will play around with DAO in A2007 after I finish my current test
project and see if I can get my skills up to speed on a new sample
project.

I had one other question about ADO connection string syntax in case
you know. In my sample development project I have a front end
A2007 .accde with all my forms, etc in it and a back end A2007
database with the tables, etc located on my file server on the f:
drive.

In my front end UI on the form load event I have the following
connection string setting up my ADO recordset.

Private Sub Form_Load()
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;"
strConnection = strConnection & "Data source=F:\FS1\Dev\BE.accdb"
.... the rest of my code...

If I set a password for my backed A2007 database, do you know the
syntax for passing the password in my connection string?

Thanks,

Check out
http://www.connectionstrings.com/?carrier=access2007

 

[Albert D. Kallal]

Private Sub Form_Load()
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;"
strConnection = strConnection & "Data source=F:\FS1\Dev\BE.accdb"
... the rest of my code...

Do you have linked tables already?

if yes, then just go:

Dim rstRec As ADODB.Recordset
dim strSql As string

strSql = "select * from tableName"

Set rstRec = New ADODB.Recordset
rstRec.Open strSql, CurrentProject.Connection, adOpenDynamic

If the tables are linked already, then you not need a password, and as the
above shows, you have a built in connection object that you can, and should
use. Don't create your connection objects from scratch when you have linked
tables....

 

[Corta]

Check outhttp://www.connectionstrings.com/?carrier=access2007

Ahh... the connectionstrings.com web site was the answer... thank you!

 

[David W. Fenton]

I had read a couple articles about Microsoft phasing out DAO
in the future and all the latest books on Access 2007 seem to be
covering ADO and not DAO.

That means that all those books are not new books that reflect
current Microsoft policy, but old books that reflect a policy that
MS has abandoned with regard to Access. ADO was mistakenly pushed
onto Access developers by MS with the release of A2K and all the
book authors had to cover it in their books. But now MS has realized
how stupid it was for them to do that and now deprecates ADO with
Jet data sources. That those books you have are not updated to
reflect this policy change (which has been in place since at least
the release of A2K3) demonstrates that you really oughtn't trust
what those books say about what is newest and best.