Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using docmd in another database (2000)

    Hi.

    I am trying to take my code out of my backend database, as each time I want to modify it, I am having to ask all the users to logoff. The code currently works fine in the back end. I have therefore imported the modules and classes into an empty database. However, I am unsure how I need to refence the backend database. I was using
    Dim ws As DAO.WorkSpace
    Set ws = DBEngine.WorkSpaces(0)
    Set db = ws.opendatabase _
    ("s:mis_be", False, False, "MS Access;PWD=1245Gv1")
    with db
    .docmd beep

    etc.etc

    Obviously that didn'[t work. I think I'm way off the mark. Any help would be much appreciated.

    Many thanks

    Amanda Battye

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using docmd in another database (2000)

    Opening a database in DAO doesn't open it in the Access interface. You can manipulate tables and queries through the Database object, but you can't open forms etc.

    What exactly are you trying to do?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using docmd in another database (2000)

    Hi Hans,

    I use the docmd object quite a lot.

    DoCmd.DeleteObject acTable, "wname_"
    DoCmd.TransferDatabase
    DoCmd.OpenQuery "qryRemoveErrors"
    DoCmd.OpenQuery "qryOperaII_no_nino_app", acNormal, acEdit
    DoCmd.OpenQuery "qryOperaII_no_nino_del", acNormal, acEdit
    DoCmd.OpenQuery "qryOperaII_Add_New"
    DoCmd.OpenQuery "qryOperaII_ExistingCurrent"
    DoCmd.OpenQuery "qryOperaII_ExistingLeavers"
    DoCmd.OpenQuery "qryTrimSurname"
    DoCmd.OpenQuery "qryKnownAs"
    DoCmd.OpenQuery "qrycost3codes"

    I know with the queries, I could just use the sql, but it is nice to have stored procedures.

    Thanks for your help

    Amanda

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using docmd in another database (2000)

    If you link the tables from the backend in your frontend, you can run the queries in the frontend. They will manipulate the data in the backend. There is no need to have stored queries in the backend database.

    If you want to create or delete tables in the backend, you can use the TableDefs collection of the Database object. Look up terms such as CreateTableDef, CreateField, Append etc. in the online help.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using docmd in another database (2000)

    Maybe I should elaborate a little.

    I am a Excel VBA programmer and am doing an Access VBA project.
    In Excel I'd do something like
    set objWkbk = application.workbooks.open("book1.xls")
    with objWkbk

    etc. etc.

    Is there anything that I can do like this in Access?

    Thanks

    Amanda

  6. #6
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using docmd in another database (2000)

    Why didn't I think of that?

    thanks

    Amanda

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using docmd in another database (2000)

    You need to distinguish between Access and the Jet Engine.

    The Jet Engine is the underlying mechanism for storing and manipulating data. The Jet Engine "knows" about tables and queries, but not about forms and reports. The Jet Engine does not have a user interface, but you can use DAO or ADO to program it. It is possible to "open" several databases at once in DAO or ADO, but this is only for manipulation through code.

    Access is a user interface built on top of the Jet Engine. It uses forms and reports to display the data. You can open only one database at a time in Access. If you have database A open, then use File | Open to open database B, database A will be closed before B is opened. So although the equivalent of Workbooks.Open exists, you wouldn't normally use it.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Using docmd in another database (2000)

    I'm with Hans on this - splitting a database shouldn't require you to change any code - all the queries, forms, reports, macros and VBA end up in the front-end and the tables are in the back-end. You can use DAO to manipulate the data in a table whether it is linked or local. And you can use queries to create tables, delete tables, etc. While the use of temporary tables can get a little tedious, we don't find you need them very often unless you are creating some kinds of reports that summarize things. Hope this helps clear up the idea of a split database a bit. If not, post back.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •