Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access VBA Library (Access97/SR2)

    As far as I can see there is no close correspondence between the idea of a library of VBA code in Word/Excel/PPt and Access.

    In Word we have a standalone DOT or DOC to which we can add a reference from another Word project.
    In Excel we have a standalone XLS or XLA to which we can add a reference from another Excel project.
    In PPT we have a standalone PPA to which we can add a reference from another PPT project.

    But Access, (from a module "Tools, References"), doesn't seem to provide the same encouragement.

    I'm not talking about importing Modules of code from one Access application to another, I'm talking about the Concept of a library, a standalone collection of procedures which is NOT part of an existing application, but that has been created SOLELY to supprt development efforts in new applications.

    My Utils.dot, for example, doesn't have a single macro in it (well, that's almost the truth); Utils.dot is not meant to be run or accessed by the end user at all. It is used only by the developer.


    I can't see myself developing something in Access with the same intent - it will always be a database and hence useable by the end user.


    Or am I missing something here?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access VBA Library (Access97/SR2)

    Recommend look into Access .MDE format which is often used for creating Library files; as noted in Help file: "If your database contains Microsoft Visual Basic for Applications (VBA) code, saving your Microsoft Access database as an MDE file compiles all modules, removes all editable source code, and compacts the destination database. Your Visual Basic code will continue to run, but it cannot be viewed or edited." Many of the libraries used by Access itself for many of the so-called Access "Wizards" are compiled as .MDE files - see this MSKB article for a list of these:

    ACC2000: Wizard Names and Their Locations

    HTH

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

    Re: Access VBA Library (Access97/SR2)

    You can create a reference to another Access database. In Tools/References..., click the Browse... button. In the File Types dropdown list, select Access Databases (*.mdb). Then locate your utility database, select it and click OK.

    Now you can use public procedures and functions from the utility database in your database.

    You will notice that you can also select Add-ins (*.mda). An Access .mda is to a .mdb database more or less like an Excel .xla is to a .xls workbook. There is a bit more to it - if you want to use an add-in not just as library databae, you have to make it self-registering. The MSKB has instructions, and a demo download on how to do this - see ACC: How to Create a USysRegInfo Table for Installing Add-ins.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access VBA Library (Access97/SR2)

    Mark, thanks for the insight. I see that as a novice I can build a library of VBA vode in a regular MDB and get on with life. The MDE path is going to be a one-way street.

    Loosely speaking, I'll have development code in MDB, but would release Production code in MDEs, to protect both myself and the end-user.



    >NOTE: In the retail version of Microsoft Access, you cannot view the Visual Basic for Applications code for wizards.

    Facsinating. What other versions of MSAccess might be available to me, the humble and downtrodden consumer (grin!)

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access VBA Library (Access97/SR2)

    > An Access .mda is to a .mdb database more or less like an Excel .xla is to a .xls workbook

    Hans thanks for the reply.

    (I'm starting to hate the diversity of methods used to incorporate libraries across MSOffice appliactions, but not the learning path that comes with it).

    I have created an MDB library as a first step, that works.

    The only way (so far) I have found to create an MDA is to rename the extent outside of Access (in Windows Explorer, for example). The Access help files suggest that I purchase a book from Microsoft. Renaming an MDB to be an MDA does little for me, the developer. I can still edit the modules, and so far haven't seen the advantages of MDA, but it's early days yet. Perhaps there's soemthing good goes in if I create an MDA other than by renaming a file.

    I created an MDE from Tools, Database Utilities, and successfully accessed that (library MDE) from my application. The MDE files seems to be the equivalent of the PPT-->PPA route - the code cannot be edited; it is a one-way street.


    As you might gather, I'm running through a lot of permutations:

    I have four applications - APPLCN.DOC, APPLCN.XLS, APPLCN.MDB and APPLCN.PPT.

    I have four libraries - UTILS.DOT, UTILS.XLS, UTILS.MDB and UTILS.PPT.

    I am examining the possible routes to creating, maintaining and releasing library code for end-user applications. So far the only common stretch seems to be 'File, New". After that everyone wanders off down their own trail (grin!).

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

    Re: Access VBA Library (Access97/SR2)

    Chris,

    You can download versions of several utility/wizard databases with viewable code. See MSKB articles WzLib80, WzMain80, WzTool80 and Utility.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access VBA Library (Access97/SR2)

    > You can download versions of several utility/wizard databases with viewable code

    Hans, i do wish you wouldn't be so prompt in replying (grin!). I'm still trying to wade through a small backlog of mail, and now i have three d/l windows going simultaneously. Oops two have completed.

    From what I can see, these are not genuine MDE/MDA, but renamed MDBs with the comments stripped out.

    I'm not trying to be mean, but it seems to me that a real MDE has the property that its code will be unviewable.


    As a test, I renamed my utils.MDB to be an MDE (using Windows Explorer) and everything worked as before - the application found the reference and ran, the Utils.MDE code is viewable.

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

    Re: Access VBA Library (Access97/SR2)

    AFAIK, a .mda is just an Access database (.mdb or .mde) whose extension has been changed. This tells Access to treat it as an add-in instead of treating it as a normal database. So it's more cosmetic than anything else. If you create a wizard or a builder or a menu add-in, you can include a special table named USysRegInfo that will be used to register the add-in when you install it.

    If you have Office 97 Pro, the Microsoft book is available in HTML form on the Office Pro CD, in the ValuPackAccess folder (OpenBook.htm). Chapter 17 deals with add-ins.

    More info is obtainable from Helen Feddema's website Access Archon. Look for no. 108: WritingAccess Add-ins.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access VBA Library (Access97/SR2)

    The structure of USysRegInfo changed between 97 and 2000. The change in structure and the move to the VB IDE is why 97 add-ins don't work as add-ins in 2000.
    Charlotte

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access VBA Library (Access97/SR2)

    You're not being mean , Chris; but you are missing the point. Those files were intentionally unprotected so that people could see the structures and get an idea of how the things were put together. Parts of them are still protected, of course.

    Just renaming your file to an MDE or an MDA doesn't make it one, any more than renaming a text file to MDB makes it a database. An MDE has to be created from inside Access, and once you've run the tool to create it, it definitely is protected because there is no more accessible code.
    Charlotte

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

    Re: Access VBA Library (Access97/SR2)

    That's correct, but the change is minor - the addition of a new record in USysRegInfo to indicate whether the add-in will work in an .adp (Access 97 didn't have .adp's). See ACC2000: Converted Add-In Not Displayed in Access Project.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access VBA Library (Access97/SR2)

    <hr>I'm starting to hate the diversity of methods used to incorporate libraries across MSOffice appliactions<hr>
    The different programs use different file structures, so why would you expect them to incorporate libraries the same way? In fact, they can use the same method in the sense that you can set a reference to a code project from any of them. The difficulty is that the code project needs to be in a container readable from within the application. You're just mad because you can't create an Access template to run when the Access AutoStart occurs. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Charlotte

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access VBA Library (Access97/SR2)

    But in 97 there was only one container for add-ins, so you could create a code tool as an add-in and it worked in either environment.
    Charlotte

  14. #14
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access VBA Library (Access97/SR2)

    As noted by others, simply renaming an .MDB file to .MDE does not create an actual .MDE file. All it creates is an .MDB with the wrong file extension. You can test for whether an Access database or ADP project is an actual .MDE (or .ADE, an ADP project's equivalent of an .MDE) by testing the db/project's MDE property. As noted in MSKB article ACC2002: How to Programmatically Determine Whether the Current File Is an MDE or and ADE File:

    "Both Microsoft Access databases (MDBs) and projects (ADPs) have an MDE property that can be read to determine if the currently open file is an MDE (for an Access database) or and ADE (for an Access project). If the property does not exist, or is set to "F", the file is not an MDE or an ADE. If the property exists and is set to "T", the file is an MDE or an ADE."

    The only catch is, this property normally does not exist in a "regular" MDB or ADP, only in a project compiled as an MDE/ADE. Example (from MDE):

    ? CurrentDB.Properties("MDE")
    T

    In an MDB file, the above statement will usually result in Err 3270, Property not found. Example of function to test for this property (modified from example in MSKB article):

    Function IsMDE() As Boolean
    On Error Resume Next

    ' Access AcProjectType constants:
    ' Const acADP = 1
    ' Const acMDB = 2
    ' Const acNull = 0
    ' If current db/project is not .MDE/.ADE, there is no MDE property, _
    resulting in Err 3270, Property not found
    Dim strMDE As String

    ' MDB or MDE database:
    If CurrentProject.ProjectType = acMDB Then
    strMDE = CurrentDb.Properties("MDE").Value
    Else ' ADP or ADE project:
    strMDE = CurrentProject.Properties("MDE").Value
    End If

    IsMDE = (Err.Number = 0 And strMDE = "T")

    End Function

    HTH

  15. #15
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Access VBA Library (Access97/SR2)

    In further reply, the function provided in previous reply will work OK assuming no one has monkeyed around & added bogus MDE property to db, which is not hard to do. Example:

    Public Sub SetDBProperty(strPropName As String, strPropVal As String)
    On Error GoTo Err_Handler

    ' Use for properties of type dbText:
    Dim strMsg As String
    Dim db As DAO.Database
    Dim prop As DAO.Property
    Set db = CurrentDb
    db.Properties(strPropName) = strPropVal

    Exit_Sub:
    Set db = Nothing
    Exit Sub
    Err_Handler:
    If Err = 3270 Then 'Property not found - create and append
    Set prop = db.CreateProperty(strPropName, dbText, strPropVal)
    db.Properties.Append prop
    Resume
    Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "SET DB PROP ERROR"
    End If

    End Sub

    What's interesting, if you do set bogus MDE property, as shown in example:

    SetDBProperty "MDE", "X"
    ? CurrentDb.Properties("MDE")
    X

    Then try to create an MDE file from current db, you will get an error msg, "Property specified already exists" & Access will refuse to create new MDE (see attd error msgs). To get rid of bogus property so you can create MDE, use this:

    CurrentDb.Properties.Delete "MDE"

    For this reason created more reliable test for whether db is MDE or not, regardless of file extension (for example, an MDA add-in file can be in MDB or MDE format). New function:

    Function IsReallyMDE() As Boolean
    On Error Resume Next

    ' If you try to run this command in "real" .MDE, will result in _
    Error 2046 Command or action "NewObjectModule" isn't available:

    DoCmd.RunCommand acCmdNewObjectModule

    If Err = 0 Then
    IsReallyMDE = False
    Else
    IsReallyMDE = True
    End If

    End Function

    In an MDE, you cannot insert a module, so error results, and function returns True. Of course when you test this function in an MDB, it'll return False, plus you'll have a bunch of extra, empty modules in your database if tested repeatedly....

    HTH
    Attached Images Attached Images

Page 1 of 2 12 LastLast

Posting Permissions

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