Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2008
    Location
    Fort Knox
    Posts
    32

    Red face Unanswered: VBScript and Access 2003

    Here's my software config:

    WinXP Pro SP2
    Access 2003 SP2

    I'm not too sure where to start explaining since my project so far has taken so many different directions in an attempt to break through (or work around) the limitations of Access 2003.

    I recently learned that the CurrentUser() function does not return the %USERNAME% environment variable. One of the core functions I was staking my project on was to use the windows username for filtering not only the data, but the allowable functions per user. It's not just a database, it will be a front-end for almost every function in my office. Consequently, I can't have every user access all the functions, and I can't have every user remember yet ANOTHER user/pass combination just to filter permissions. We have too many systems that require a user/pass combo and creating another one would be taking a step backwards no matter how excellent my project turns out to be.

    I've found a few bits of VBScript on the internet that have helped. For example I found a script that captures and displays the windows username in a MsgBox, so I know I have the capability to capture that info. And I found another bit of code that disabled the "hold SHIFT" workaround for bypassing the startup settings. I actually intend to run the database in "Admin" mode for all users, but I'll use the Forms and the interface to filter permissions of who can do what and who can read what. I'm almost done with that part of the database... the only part holding me back now is not having access to the current windows username.

    I feel like I know what to do, but I just don't know the language well enough to do it.

    Basically I'm looking to not only learn how to do all this, but to actually do it without too much copy paste. I know that every program that VBScript has the ability to modify/control has it's own unique Component Object Model. I can't seem to find that model for Access 2003. Every time I get close I think I'm looking at the "internal" COM for Access 2003 functions within the database, but not stuff that has any function or usefulness outside of the Access 2003 application.

    I've pretty much given up on the idea that there is a way to extract the current windows username through Access 2003. If there is a way, and if someone could point me to some documentation on that, that would be a tremendous help. Other than that, I'm just looking for the COM for Access 2003 so I can dig around in VBScript sites to figure out how to pass the current windows username into Access 2003 so I can use it.

    Any comments or references would be appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you don't need vbscript for that
    you can perfectly easily get the deatils of the current network user in Access

    there is an example of how to use the API calls (from PKStormy) to get the netwrok logon in the code bank.. or do a search for Dev Ashish API Access. in fact the URLl has plenty of great examples for other seemingly non related things...well worth a browse

  3. #3
    Join Date
    Feb 2008
    Location
    Fort Knox
    Posts
    32

    Isn't that VBScript? :-P (sure looks like it to me)

    Thanks healdem for your input. I'm afraid I really don't know how to use API stuff as far as how it relates to Access 2003. That link you gave me looked a LOT like VBScript though. Any chance you could give me more insight on where that would be put in to Access 2003 so that I could use it? (Doesn't API stuff deal with DLL's and/or making DLL's?)
    Last edited by TheArkive; 04-16-08 at 17:02.

  4. #4
    Join Date
    Feb 2008
    Location
    Fort Knox
    Posts
    32

    Getting Access 2003 to interact with XP and read environment variables

    I finally found a way to achieve my goal and I just wanted to post the results for everyone else here.

    My main problem was actually getting Access 2003 to interact with the Operating System (Windows XP), but the main focus for functionality sake was simply to get the current logon name (username) from windows.

    For all the experienced dudes out there please stifle your laughter, this is a big moment for me!

    As I was trying to Google solutions with VBScript, I came across several instances of CreateObject("WScript.NetWork"). After a while I figured that would have something to do with it. So through Access 2003 I would open up the Microsoft Visiual Basic Editor. I also found the button that opened up the Object Browser within the VB Editor, but to my dismay there was no instance of the Windows Scripting Host Component Object Model to select from the list of available libraries (...it's a combo box that lists the available libraries in the top left corner of the Object Browser).

    That was what gave me the idea that so-called "Access 2003 VB" was isolated and could not interact with the Operating System. It turns out you actually need to select (or load) the Windows Scripting Host COM libraries into the Microsoft VB Editor in order to access the WSH functions and variables via Access 2003.

    Here's how you do that:

    Within the Microsoft Visiual Basic Editor...

    1. Click Tools
    2. Click References...
    3. Click checkbox for Windows Script Host Object Model
    4. Press OK.
    -- NOTE: If you go back to the References... all the checked libraries will be grouped at the top.

    This way you can see everything that WSH has to offer and therefore extract the current UserName as well as a ton of other OS information.

    As far as copying the Access *.mdb file to another comptuer (or another network), I'm not sure how that would affect the functionality of the database. If someone can comment wether or not my actions of selecting the WSH Object Model in the VB Editor are unique only to the workstation I did this on, or if I should be in the clear for any computer I run this on, I'd really appreciate the input. So far the database is working fine off a network drive. I did a filter test with 2 users using the database simultaneously and both users were only able to see their data. So far so good... but if anyone is aware of something I missed please be so kind as to let me know.

    To get the UserName (after loading the appropriate library), I created a new Module. I named the module Current Windows User

    EDIT:
    NOTE: The module name cannot be the same as the function name!
    Here is the code for that:

    Code:
    Function CurrentWindowsUser()
      Dim objNet
      Set objNet = CreateObject("WScript.NetWork")
    
      Dim strInfo
      strInfo = objNet.UserName
    
      CurrentWindowsUser = strInfo
    End Function
    The function name is CurrentWindowsUser(). Just set a control's Control Source to =CurrentWindowsUser(), or use it in any function, event, or macro to get access to the output (which is the current username as reported by windows).

    With a little more VB knowledge I would assume it is possible to use the rest of the WSH COM to get access to a bunch of other OS variables and functions.

    If anyone has any critique they can give me on my ideas/methods I'd really appreciate knowing other ways to do this that are better/faster, or just ways to improve what I have already done. One of my main goals is to not have too many seperate files in order for things to work (ie. *.vbs files). So keeping everything within the *.mdb file is a rather high priority for me. And healdem, I'd like to know more about your API idea. Eventually I'd like to save this as an MDE file to get it working off of compiled code instead of the upper level VBScript. If API methods are the way to go then please help me understand that if you would :-)
    Last edited by TheArkive; 04-16-08 at 17:06.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    daft way to do it

    daft why?
    one there is an easy way of dxoing it within Access.. place the code in a public code module, and call the function.. if you only need the funcxtion in one place you coudl have it within a form

    some AV's report vb script as possibel virus activity

    the api call is common accross W98, NT, XP 2000, and Im preety cetain under Vista.. so there are no problems or portability, no problems of references. in short its an easy well proven solution. it requires no vbs files, iots all within the access mdb/mde

    I doubt very much the vsh model is giving you anything not available thruough the APi calls.. after all Im pretty certain the code you are suign is another wrapper for the same API calls.

    as said before the refercne quoted has a whole ruck of functions

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    partially daft:
    the VBA in #4 late-binds to WSH so there is no need to set the reference.
    either set the reference and early-bind, or forget the reference and late-bind.

    also:
    Dim strInfo
    strInfo = objNet.UserName
    CurrentWindowsUser = strInfo

    is simpler and faster as:
    CurrentWindowsUser = objNet.UserName

    i have used late-binding WSH in all my apps for ten years without any protest from AVG or Norton.
    healdem's API suggestion is at least equally good and may be better long-term with Gates killing off COM.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I used WSH in early 2002/3 and it tripped Norton big style.. it may be differences in what I was trying to do at the time but it scared the heck out of users.....

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To retrieve the current windows loginID into MSAccess, check out this example (the latest code) http://www.dbforums.com/showpost.php...9&postcount=68

    I added some neat features to the example above (including how to implement security levels in a table and "simulate" logging in as another user.) The sample mdb is very easy to follow. I've used this technique for years without any problems.

    Older versions are here: http://www.dbforums.com/showpost.php...0&postcount=20

    The function in all examples above is getuser(). You can use getuser() anywhere (in a query as an expression, as the default value for field (ie. EnteredBy field), in a report, in another module, etc...) and it will always return the current windows loginID.

    Just import the Getuser() function and the SystemInfo Class module into your mdb and you're ready to go but look at the example mdbs for some ideas on ways you can use it first. Also read the comments in the SystemInfo Class Module as there is a LOT more you can do with that module. The FormInfo Class Module included in the examples also offers a wealth of stuff you can do with it.

    Also, to get the current windows loginID in a vbscript, here's the code for the vbscript:

    Set WshNetwork = CreateObject("WScript.Network")
    GetUser = WshNetwork.UserName
    Set WshNetwork = Nothing
    msgval = msgbox("LoginID = " & GetUser)
    Last edited by pkstormy; 04-17-08 at 04:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Feb 2008
    Location
    Fort Knox
    Posts
    32
    healdem... I did create a public module, and put the function in that. So I can use the function within Access and no external files. Is that really daft if I did it that way?

    As far as using API, does that mean writing a DLL? I really have no idea how to use API with Access. I'd love to use a long term solution instead of something destined to fail. How do I use API, what does that even mean to use API, and how can I package it with my database?

    EDIT: Are you meaning to use a different library instead of the WSH? Use the "advapi.dll" instead? So it's the same thing but a different library?

    EDIT2: What is the difference between advapi.dll and wuapi.dll??? You got me interested now :-D
    Last edited by TheArkive; 04-17-08 at 23:42.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look at the example

    it tells you
    place the code in a public modules
    copy and paste from the example and put it into your public module

    the copy and paste brings across the API declaration..
    you need to tell Access what API call you are using, (the name), what its parameters are and where it can be found.. all of whiohc is done for you
    and the function itself

    you then call the function from any form, report or whatever within that db as if its a normnal function.

    it doesn't shell out to anything, it doesn't invoke WSH.. it does means that it runs effectively as an in line function within access. that's why in my books going out to WSH is daft there are no libraires or references to set, as these functions are critical to windows, and all windows apps have access to them. mind you its exactly the same way as linking to any other DLL.. but you may need to check that DLL exists before use.

    the only bugbear in my books is that you cannot build (AFIAK) say a common library of functions which you add to over time, and any changes in the source library are automatically picked up by Access MDB's using that library (fair enough MDE's can't even thought it would be 'nice'). you have to remember to import the new version of your common library.. mind you I suppose they way its currently designed you do have a sort of effective versioning code library

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi healdem,

    you can indeed build a common library, update the library, and applications using that library get automagic access to the new library functionality - no re-reference, no import, just a little care to maintain backwards compatibility with old applications.

    my .MDE library includes generic functions and also provides late-bind classes. the library is included in my applications' references. saves a lot of typing. there is a simple example in the codebank (and, just for you, it uses WSH )

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Feb 2008
    Location
    Fort Knox
    Posts
    32
    Thanks a bunch guys ... now I understand what all the stuff is and how to do it . I'll have to see if the company I work with will have an issue dealing out the "advapi.dll". This "database" will run in seperate remote locations (unlinked). What I mean is, I have to talk to the admins in the remote locations and see if that will be a major issue. I don't forsee that it will be, but just going and doing stuff without asking on my company's network can have some bad results (even if it is for the good of part of the company).

    This is part of the process to going to a "global" link kind of system, until then I don't have much choice but to start small :P ... This technically isn't even my job, but since I can do it, and since we duplicate so much data in word documents and excel spreadsheets, I feel like I gotta do something to try and move my company into the 21st century!

Posting Permissions

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