Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing code module (Access 2003 SP2)

    Hi All

    I'm trying to run some code which will create a temporary mdb, and then import a bas file as a code module into the temp mdb.

    I keep getting error 91 in the "Set vbc = vbp.VBComponents" line in the following code :

    Dim objAccess As Access.Application
    Dim wks As DAO.Workspace
    Dim dbs As DAO.Database
    Dim vbe As VBIDE.vbe
    Dim vbp As VBIDE.VBProject
    Dim vbc As VBIDE.VBComponents
    Dim mdl As CodeModule
    '************************************************* ************************
    'End of variable declaration
    '************************************************* ************************
    On Error GoTo CreateMDB_Error
    '
    Kill "C:tempnew.mdb"
    Set objAccess = New Access.Application
    'objAccess.Visible = False
    Set wks = objAccess.DBEngine.Workspaces(0)
    Set dbs = wks.CreateDatabase("C:tempnew.mdb", dbLangGeneral)
    Set vbp = objAccess.vbe.ActiveVBProject
    Set vbc = vbp.VBComponents
    vbc.Add (vbext_ct_StdModule)
    vbe.SelectedVBComponent.CodeModule.AddFromFile "c:basMenuFunctions.bas"

    Has anybody come across this one before and got any suggestions as to how to get round it ?

    Usual thanks

    Nick

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing code module (Access 2003 SP2)

    Hans

    I take your point about the template database, but I did think that VBE Extensibility applied to all Office applications.

    Nick

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

    Re: Importing code module (Access 2003 SP2)

    You're correct. It does work.

    But you have only created a database, you haven't opened it in your instance of Access. Add the following line after creating the database, before trying to do something with it:

    objAccess.OpenCurrentDatabase "C:Tempnew.mdb"

    You can close it later with

    objAccess.CloseCurrentDatabase

    Another way to do it, without requiring a reference to the Microsoft Visual Basic for Application Extensibility library, is:

    ...
    Dim mdl As Module ' instead of As CodeModule
    ...
    objAccess.OpenCurrentDatabase "C:Tempnew.mdb"
    objAccess.RunCommand acCmdNewObjectModule
    Set mdl = objAccess.Modules(0)
    mdl.AddFromFile "c:basMenuFunctions.bas"
    ...

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

    Re: Importing code module (Access 2003 SP2)

    Edited by HansV

    The Microsoft Visual Basic for Applications Extensibility library is not intended for Access, it is for Word, Excel etc.
    As far as I know, you cannot create a new Access module in code. I think you'll have to create a template database and insert the module into the template manually.

    Added: my remarks about VBA Extensibility are incorrect - see my next reply in this thread.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing code module (Access 2003 SP2)

    Hans

    That was very helpful, but I have one more problem. The module is created with the correct code, but when I try to close the database I get the Save As dialog box popping up. I have been searching around, but I cannot see how to do it in code.

    Can you suggest a way please

    Thanks again

    Nick

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing code module (Access 2003 SP2)

    Answering my own question, the line that was missing was :

    objAccess.DoCmd.Close acModule, "basMenuFunctions", acSaveYes

Posting Permissions

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