Access JET vs ADO - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Access JET vs ADO

Author

Topic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-11 : 17:29:48

slbmis writes:

"What would the syntax be for connecting to a sql databse using access 2000 as opposed to access 95 using this syntax?"

Actually, slbmis, the difference is between using DAO (Data Access Objects) or ADO (ActiveX Data Objects). You can actually use either method with either version (in theory at least) YOU CAN EVEN USE BOTH IN THE SAME ACCESS PROJECT! (admittedly I've only tried this with Acc 97 not Acc 95 and wouldn't recommend it).

Open a code module in Access (ALT-F11) and then go to Tools - References, to check out what you're using at the moment.

In Access 95, for example, Microsoft DAO 3.X is included by default, but you can also add a reference to Microsoft ActiveX Data Objects version 2.X and then use it!

The intention appeared to be DAO for JET databases and ADO for others (eg MSDE/SQL Server etc) - but you can use ADO to connect to a JET database (if you want to!). And of course, you have always been able to access SQL Server using DAO code, by using "linked" tables.

Finally, to answer your question:
DAO -
Dim db as Database
Dim rst as Recordset

Set db = CurrentDb
Set rst = db.openRecordset("select * from linked_table")

--etc
rst.Close
db.Close

Set rst = nothing
Set db = nothing

ADO example (and there are MANY ways of doing this)
Dim cnxn as New ADODB.Connection
Dim rst as New ADODB.Recordset

cnxn.Open "Provider=sqloledb;Server=SERVER_NAME;Database=DATABASE_NAME;...
rst.Open "Select * from Table",cnxn

rst.Close
cnxn.Close

Set rst = nothing
Set cnxn = nothing

For more information, check out....

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovropeningaccessdatabasebyusingado.asp[/url]

PS Just out of interest, Microsoft don't want you to use JET any more - see [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dao360/html/dadefmsjetdatabaseengine.asp[/url]

Hope that (as they say) helps....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 03/11/2002 17:33:36

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources