Connecting to a SQL Server 2005 using VBA and ADO

[Brian]

I am very familiar with connecting to an Access database and manipulating
data with DAO. Something like:

dim db as database
dim rst as recordset

set db=currentdb()
set rst=db.openrecordset("Contacts", dbOpenDynaset)

with rst
.findfirst "[ContactID]=12345"

and so on...you get the point.

I am now writing a database with an Access 2007 front end and SQL Server
2005 backend. The tables are linked into Access using a .dsn file through
the ODBC connector. So my question is...how do I duplicate the functionality
that is shown above using ADO?

Thanks

 

[Albert D. Kallal]

Any reason you don't leave the code as you have?

The code example you have should work just fine....

dao should work just fine....

if that table is linked, then your code should work as is.

I would not use find first in this case, but perhaps you have reason for
doing so.....

I would have used:

dim rst as dao.RecordSet

set rst = currentdb.openrecordset("select * from contacts where contactid =
12345")

if rst.RecordCount > 0 then
'....do whatever....

end if

the above will work if you using linked tables to a back end mdb, or if your
tables are linked to sql server...the code does not need to be changed in
the above example when using sql server, or a plan Jane un-split database,
or a split database.

The above code will work fine for all 3 cases..and should not need to be
modified.....

I would not use findfirst even when not using sql server unless the data set
was small, and you had to repeat use findfirst a number of times. It is
generally better to just fetch the one record as a above then doing a table
scan with findfirst...

Also, I used dao.RecordSet, but if you don't have a reference to ado, then
your use of "RecordSet" should work just fine also...

 

[Brian]

Any reason you don't leave the code as you have?

The code example you have should work just fine....

dao should work just fine....

if that table is linked, then your code should work as is.

I would not use find first in this case, but perhaps you have reason for
doing so.....

I would have used:

dim rst as dao.RecordSet

set rst = currentdb.openrecordset("select * from contacts where contactid =
12345")

if rst.RecordCount > 0 then
'....do whatever....

end if

the above will work if you using linked tables to a back end mdb, or if your
tables are linked to sql server...the code does not need to be changed in
the above example when using sql server, or a plan Jane un-split database,
or a split database.

The above code will work fine for all 3 cases..and should not need to be
modified.....

I would not use findfirst even when not using sql server unless the data set
was small, and you had to repeat use findfirst a number of times. It is
generally better to just fetch the one record as a above then doing a table
scan with findfirst...

Also, I used dao.RecordSet, but if you don't have a reference to ado, then
your use of "RecordSet" should work just fine also...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)


Albert,

Thank you for your reply. I guess my "problem" (if you can call it that)
with DAO is that my understanding is that it is going to be unsupported my
Microsoft since they now seem to be favoring ADO. If this is true, and since
I am writing the front-end DB from scratch, I would prefer to use something
that will not be obsoleted in the next release of Access (or whenever).

So my question to you would be: Is there any specific reason not to use
ADO? Are there any technical differences between ADO vs. DAO that would lead
you to recommend one over the other? Just so you know, this database was
written in Access 97 and continually upgraded over the years until Access
2003. We have now decided to place the back end in a SQL Server and totally
rewrite the front end in Access 2007. So based on past history, and within
the confines of SQL Server 2005, I would expect that this database will also,
over the years, be upgraded to whatever new version of Access comes about.
Which is why I was leaning towards ADO.

Any thoughts you have would be most appreciated.