use of workspace and openconnection in access 2007

[dragnor]

Hi experts

At work we have to cange from access 2003 to 2007 and the following code used to work in 2003:
Dim ws As Workspace
Dim DB As Connection
Dim rst As Recordset, rst2 As Recordset, rst3 As Recordset, rst4 As Recordset

Set ws = CreateWorkspace("FK", "", "", dbUseODBC)
Workspaces.Append ws
Set DB = ws.OpenConnection("FK", dbDriverNoPrompt, False, "ODBC;DSN=SCDRAPADM;UID=SASADMIN;PWD=abc123")
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,BNR,txt1,txt2,status,maxrc,DT_0) values (" & JOBNR & ", '" & BNR.Value & _
"', '%inc ODPROG(" & OD_Prog & ");', '" & UCase(PORCALC.Value) & "' , 0, 0, SYSDATE)"

DB.Execute sqlJobSpec, dbExecDirect

but in access 2007 it does not work anymore :-(

Can someone please help me get this to work in 2007?

 

[Rabbie]

First of all

change

Dim DB As Connection
Dim rst As Recordset, rst2 As Recordset, rst3 As Recordset, rst4 As Recordset

to

Dim DB As Connection
Dim rst As ado.Recordset, rst2 As ado.Recordset, rst3 As ado.Recordset, rst4 As ado.Recordset

I think that DAO is the default in A2007 so it is best to remove any ambiguity from your code.

 

[dragnor]

Thanx! That worked!

 

[Rabbie]

Thanx! That worked!

Glad I was able to help