use of workspace in access 2007

Posted on 2008-11-05
Last Modified: 2013-11-05
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?
    LVL 44

    Expert Comment

    by:Leigh Purvis
    Well - what exactly are you wanting to do?
    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.

    Author Comment


    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("FK", "Admin", "", dbUseJet)
    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
    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 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?

    LVL 84

    Accepted Solution

    Looks like Leigh has gone down for the evening ... here's an ADO equivalent:

    Dim con As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set con = New ADODB.Connection
    con.Open "Your Connection STring Here"

    Set rst = New ADODB.Recordset
    rst.Open "select max(jobnr) as max from ondemand_ondemand", con

    If Not (rst.EOF and rst.BOF) Then
      Con.Execute "nsert into OnDemand_OnDemand(jobnr,BNR,txt1,txt2,status,maxrc,DT_0) values (" & rst("Max") + 1 & ", '" & BNR.Value "', '%inc ODPROG(" & OD_Prog & ");', '" & UCase(PORCALC.Value) & "' , 0, 0, SYSDATE)"
    End If

    "Your Connection String" would be the fully formatted connection string to the Server. Check here for examples:


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now