DCRAPACCESS
asked on
use of workspace in access 2007
Hi experts
i'm using this code:
Dim ws As Workspace
Set ws = CreateWorkspace("FK", "", "", dbUseODBC)
and that works with access 2003.
Now in access 2007 it tells me that i can't use that code anymore.
How do I rewrite the code, so it works?
i'm using this code:
Dim ws As Workspace
Set ws = CreateWorkspace("FK", "", "", dbUseODBC)
and that works with access 2003.
Now in access 2007 it tells me that i can't use that code anymore.
How do I rewrite the code, so it works?
ASKER
Hi
I know i have to use ADO, but i don't know how.
But I have found out that this works:
Dim ws As DAO.Workspace
Dim DB As DAO.Database
Set ws = DBEngine.CreateWorkspace("
Workspaces.Append ws
Set DB = ws.OpenDatabase("FK", dbDriverComplete, False, "ODBC;DSN=SCDRAPADM")
Set rst = DB.OpenRecordset("select max(jobnr) as max from ondemand_ondemand")
With rst
JOBNR = !Max + 1
.Close
End With
Job.Value = JOBNR
sqlJobSpec = "insert into OnDemand_OnDemand(jobnr,BN
"', '%inc ODPROG(" & OD_Prog & ");', '" & UCase(PORCALC.Value) & "' , 0, 0, SYSDATE)"
DB.Execute sqlJobSpec, dbExecDirect
But now it fails in the line that says "DB.Execute sqlJobSpec, dbExecDirect".
Before i was using "Set DB = ws.OpenConnection" but then i got a error. Now i use "Set DB = ws.OpenDatabase" is that a problem?
And instead of using "dbUseODBC" i use "dbUseJet" is that a problem?
I hope that this information is what you seek to help me?
I know i have to use ADO, but i don't know how.
But I have found out that this works:
Dim ws As DAO.Workspace
Dim DB As DAO.Database
Set ws = DBEngine.CreateWorkspace("
Workspaces.Append ws
Set DB = ws.OpenDatabase("FK", dbDriverComplete, False, "ODBC;DSN=SCDRAPADM")
Set rst = DB.OpenRecordset("select max(jobnr) as max from ondemand_ondemand")
With rst
JOBNR = !Max + 1
.Close
End With
Job.Value = JOBNR
sqlJobSpec = "insert into OnDemand_OnDemand(jobnr,BN
"', '%inc ODPROG(" & OD_Prog & ");', '" & UCase(PORCALC.Value) & "' , 0, 0, SYSDATE)"
DB.Execute sqlJobSpec, dbExecDirect
But now it fails in the line that says "DB.Execute sqlJobSpec, dbExecDirect".
Before i was using "Set DB = ws.OpenConnection" but then i got a error. Now i use "Set DB = ws.OpenDatabase" is that a problem?
And instead of using "dbUseODBC" i use "dbUseJet" is that a problem?
I hope that this information is what you seek to help me?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The error message you cite is quite correct. Access 2007 no longer supports ODBC Direct workspaces.
That you used to use one implies you have a server database which you wanted to access directly in code without involving Jet at all. It that about right? What functions are you performing on the database you access though?
Ironically, given that MS have moved towards advocating linked tables now with 2007 - the recommendation for direct data access is to use ADO instead of DAO with ODBC Direct. (I can't disagree with that suggestion :-)
So, as an answer to your question, it's tempting to just say "use ADO" - what you're attempting is relevant.