ADO, DAO, or ADO.NET

[Guest]

I'm setting up my application to work with a SQL Server 2005 backend.
I plan to keep the app as an .mdb, which I assume means I can keep linked
tables to the SQL Server. However, what's the most efficient way to access
the data?

Using DAO with the linked tables or some other method which does not use
linked tables? ADO.NET or the like.

-David

 

[Douglas J Steele]

Depending on what you're doing, the most efficient way is probably to use
pass-through queries (which must use ODBC), since they run on the server.
The downside, though, is that pass-through queries aren't updatable.

With linked tables, DAO should be fine: your code is going to go against the
linked table in your MDB.

If you want to go directly against SQL Server, without linked tables, ADO is
probably better.

ADO.Net isn't an option.

 

[Guest]

Depending on what you're doing, the most efficient way is probably to use

pass-through queries (which must use ODBC), since they run on the server.
The downside, though, is that pass-through queries aren't updatable.

Is this using ODBCDirect?

With linked tables, DAO should be fine: your code is going to go against the
linked table in your MDB.

I'd like to avoid linked tables, as it seems like the direct route would be
more efficient.

If you want to go directly against SQL Server, without linked tables, ADO is
probably better.

Allen Browne has said in a related discussion that ADO is essentially dead
as a generic library, but I'm not sure of what relevance that might have.
Can I use ADO with the relative assurance that it will work with SQL Server
2005?

Thanks,

-David

 

[Douglas J Steele]

Is this using ODBCDirect?

I simply meant that when you create the Connect property for the
pass-through query, you must select an ODBC connection. You can use a DSN,
or (my preference) an ODBC DSN-less connection string.

I'd like to avoid linked tables, as it seems like the direct route would be
more efficient.


Allen Browne has said in a related discussion that ADO is essentially dead
as a generic library, but I'm not sure of what relevance that might have.
Can I use ADO with the relative assurance that it will work with SQL Server
2005?

Development of ADO is dead, but the existing ADO library should be able to
connect to SQL Server 2005, provided you have the appropriate provider.
Afraid I haven't played with SQL Server 2005, so I can't comment whether the
existing providers work with it.

 

[Brendan Reynolds]

I haven't had an opportunity to play with SQLS 2005 yet, but the following
quick test successfully retrieved data from the SQLS 2005 Express pubs
database, so, yes, ADO 'classic' works with SQLS 2005. There may or may not
be some new features of SQLS 2005 that might not be available via ADO
'classic'. If you haven't done so already, you might want to ask in a SQL
Server or ADO forum about that. Anyhow, here's the test code ...

Public Sub TestSqlExpress()

Dim strConnect1 As String
Dim strConnect2 As String

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

strConnect1 = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=pubs;" & _
"Data Source=DBWGQZ0J\SQLEXPRESS"

strConnect2 = "Provider=SQLNCLI.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=pubs;" & _
"Data Source=DBWGQZ0J\SQLEXPRESS"

Debug.Print "Using OLEDB Provider for SQL Server"
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = strConnect1
.Open
End With
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cnn
.Source = "SELECT * FROM Authors"
.Open
Do Until .EOF
Debug.Print .Fields(0)
.MoveNext
Loop
.Close
End With
cnn.Close

Debug.Print
Debug.Print "Using SQL Native Client"
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = strConnect2
.Open
End With
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cnn
.Source = "SELECT * FROM Authors"
.Open
Do Until .EOF
Debug.Print .Fields(0)
.MoveNext
Loop
.Close
End With
cnn.Close

End Sub

 

[Guest]

I simply meant that when you create the Connect property for the

pass-through query, you must select an ODBC connection. You can use a DSN,
or (my preference) an ODBC DSN-less connection string.

Thanks, any tips or can you point me to a tutorial about pass-through
queries using ODBC DSN-less connections? This seems like the best way to go
about connecting, short of someone on the SQL Server lists telling me I'm
being dumb.

I tried to post on the VB forums but the not nice forum admin removed my
post (go figure)

Am I correct in assuming that with pass-through queries I continue to use DAO?

Thanks again,

-David

 

[Guest]

Cheers. Thanks for the code I'll have a stab at it later.

-David

 

[Douglas J Steele]

Thanks, any tips or can you point me to a tutorial about pass-through
queries using ODBC DSN-less connections? This seems like the best way to go
about connecting, short of someone on the SQL Server lists telling me I'm
being dumb.

You shouldn't really require a tutorial. With any pass-through query, you
need to assign a value to its Connect property. You simply need to know what
the Connection string is. With SQL Server 2000, using Trusted Connection, I
use:

"ODBC;DRIVER={sql
server};DATABASE=DatabaseName;SERVER=ServerName;Trusted_Connection=Yes;"

(replacing DatabaseName and ServerName with the appropriate values)

For other possibilities, check Carl Prothman's site
http://www.carlprothman.net/Default.aspx?tabid=90

I tried to post on the VB forums but the not nice forum admin removed my
post (go figure)

What forum? Couldn't have been one of the Microsoft ones, as there are no
forum admins.

Am I correct in assuming that with pass-through queries I continue to use

DAO?

The query is a query. You can run it using DAO or ADO, although I suspect
DAO would be more efficient. (If using ADO, remember that you're running a
query in the MDB, so that's what your connection string needs to be)

 

[Guest]

The downside, though, is that pass-through queries aren't updatable.
Ok, its been 7 days and I'm still not decided on which path to take, but
regardless, can you explain about the pass-through queries not being
updatable? I think I understand but I want to be absolutely sure.

Thanks,

-David