Access2007 doesn't like to use ODBCDirect workspace

[SJ]

sorry if this has turned up more than twice. I thought i reposted but it
appears i might not of


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

 

[Albert D. Kallal]

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.

There was LITTLE need to use ODBCDirect. Note that ONLY JET direct ODBC has
been removed.

JET + odbc still works just fine. It is ONLY the JET direct that been
removed. You CAN STILL USE JET+ODBC....

I am not sure if you realize, but jet direct ONLY was invoked when you use

Set wrk1 = DBEngine.CreateWorkspace("TestWorkSpace", "", "", dbUseODBC)

The dbUseODBC keyword would actually prevent JET from EVER being loaded.

So, you can still use JET + odbc...

so:

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

Change the above to:

Set qryconMain = CurrentDb.QueryDefs("MyConnection")
qryConMain.Sql "exec MyTestProcedcue"
qryConMain.execute
qryConMain.Close

If you want, you can create a global query that is set as passthough
query....and use that...

eg:

qryConMain.Sql = strSql
qryConMain.Execute


And, for this example:

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

the above becomes:

qryConMain.Sql = strSql
set rst = qryConMain.OpenRecordSet

(assuming we created that qry called qeryConMain to user everywhere).

If you want to create the query from scratch, then use:

Setqdf = CurrentDb.CreateQueryDef("")
Q.Connect = strCon ' my connection string
Q.SQL = "exec MyTestUser"
Set rstRecords = Q.OpenRecordset

Debug.Print rstRecords(0)

So we have to consider those 2 scenarios...

Like I said, it not that JET + odbc been removed, but only odbc direct. So,
just create one query (or perhaps two) that are a pass-through query, and
use that in place of what you have....

And, if you must, I bet you could actually put in a reference to the DAO 3.6
object, and actually use a dao reference here. That would likely let you run
your code un-modify.

For the most part, few of us used odbc Direct. We use a combination of
linked tables, and for connection, we use a existing query that was saved as
pass-through...so, we tend to use something like:
eg:
Set Q = CurrentDb.QueryDefs("qryPass")
Q.SQL = "exec MyTestUser"
Set rstRecords = Q.OpenRecordset

It is the about the same, or is actually somewhat less code then what you
have.....

Further, you always resort to using ADO if you really do need to create the
connection object....

However, the above examples do support the use of a connection string..it
just that we are not creating a connection object.

However, you can use/create a connection string:
Thus:

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

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 tblcustomers"
Set rstRecords = Q.OpenRecordset

So, we can freely use connection strings, we just don't wind up creating
that connection object...

As I said, we don't really much need to create the connection object.
However, if MUST...then you need to use ADO....
(however, I not tried setting a dao reference...and that *might* allow you
to create your dao connection object anyway -- I just not tried setting a
reference to DAO in access 07...but, it should work. I would try setting the
reference, as then it would allow your code to run mostly un-modified).

 

[aaron.kempf]

Dude you are a mother ****ing idiot for still using JET / DAO / ODBC

Access Data Projects made all your crap obsolete

bastard; you're stuck in the 90s