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

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"DBG" <(E-Mail Removed)> wrote in message
news:BA3863DC-3184-4FF3-AB5E-(E-Mail Removed)...
> 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

> 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

"DBG" <(E-Mail Removed)> wrote in message
news:886B5263-1B08-4105-A0C6-(E-Mail Removed)...
> > 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?

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.

> >
> > 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?

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

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

--
Brendan Reynolds
Access MVP


"DBG" <(E-Mail Removed)> wrote in message
news:886B5263-1B08-4105-A0C6-(E-Mail Removed)...
>> 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
>

> 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

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

-David

"Brendan Reynolds" wrote:

>
> 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
>
> --
> Brendan Reynolds
> Access MVP
>
>
> "DBG" <(E-Mail Removed)> wrote in message
> news:886B5263-1B08-4105-A0C6-(E-Mail Removed)...
> >> 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
> >
>
>
>

"DBG" <(E-Mail Removed)> wrote in message
news:CF45C002-8B8F-4834-A1B7-(E-Mail Removed)...
> > 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.

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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> 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