no replies, might just ask once again. ODBC Direct Access2007

[SJ]

Hi all,

i've noticed that Access2007 doesn't like to use ODBCDirect workspace stuff.

We have quite a large App, about 800 forms etc.
with a SQL Server backend 2000 - 2005 tables are ODBC linked.

We use the ODBCDirect workspace connection method frequently when we want to
execute SQL code or run a Stored Proc which quite often might return a
recordset.
But that doesn't work now.

So the question i'd like to ask is this,

Given all the new Data Access Methods over the last few years, what should
we be looking at in order to upgrade our App to run under 2007.


we might use code such as

strSQL = "exec some stored procedure"
Set conMain = OpenConnection() ' this opens a connection against
an ODBCDirect Workspace
conMain.Execute strSQL
conMain.Close

OR
we have a procedure that returns a recordset from a temp querydef

SQLScript = "select * from sometable" 'usually not quite that
simple, quite often a stored proc that returns records
Set qdf = conMain.CreateQueryDef(, SQLScript)
Set rst = qdf.OpenRecordset(dbOpenForwardOnly)
Set rstObject = rst


So we have to consider those 2 scenarios...

thanks in advance...

 

[Sylvain Lafontaine]

Use sql pass-through queries and/or ADO objects instead of the ODBCDirect
workspace stuff. If you want to associate a form with a recordset returned
by a SP, see http://support.microsoft.com/?id=281998 .

You can also link a form to a SQL view; see
http://support.microsoft.com/kb/q209123/ for example.

 

[Albert D. Kallal]

Hi all,

i've noticed that Access2007 doesn't like to use ODBCDirect workspace
stuff.

We have quite a large App, about 800 forms etc.
with a SQL Server backend 2000 - 2005 tables are ODBC linked.

We use the ODBCDirect workspace connection method frequently when we want
to
execute SQL code or run a Stored Proc which quite often might return a
recordset.
But that doesn't work now.

I can confirm that ODBCdirect connections are not supported in a2007.

You have to either use ADO for your recordsets then.

However, you can simply use a jet object conneciton.

eg:

Dim rstRecords As DAO.Recordset
Dim Q As DAO.QueryDef
Dim strCon As String

strCon = "ODBC;driver=SQL Server;" _
& "SERVER=WS-FS01\SQLEXPRESS;" _
& "UID=SA;PWD=myPassword;" _
& "DATABASE=cust1"

Set Q = CurrentDb.CreateQueryDef("")
Q.Connect = strCon
Q.SQL = "select * from customers"
Set rstRecords = Q.OpenRecordset

Note that I left out the "dbo" in front of the customers table
(dbo_custoemrs in the sql server side).

In fact, you might even build ONE query def that is a pass through...

Then, you dont' even have to build the conneciton string...just go:

q.sql = "execute sp_myCoolsp"

and the reocrdset will return your stored proc results...