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