Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking for a creative solution... (A2K SP3)

    Hi everyone!

    To open my database users click on a shortcut that runs a batch file which copy's the latest version of my database front-end if it's needed before the actual application opens. This works great for about 90% of the users in our company.

    There's one report in my database that the other 10% use and that's where my problem is. It's not a standard 8.5" x 11" report. In fact it's a 4" x 8" report that is sent to Zebra or Datamax thermal printers. Before these users can use my database I have to manually set the report to print specifically to their thermal printer. Each printer usually has its own margin requirements too (set from the printer driver not in Access). To make this "easier" on me I've created a database on each of these local computers that only contains this one report in it with the proper printer settings. Then I manually import this report into the updated database, as needed. It does the job but it's very cumbersome to handle it this way any time there's an update.

    So....I'm looking for a way to automate this! I imagine this is a common problem but I haven't been able to find similar posts. Does anyone know how to delete the existing report and then import the report from the second database via VBA? I should also mention that the main application is password protected.

    Or is there a way to programatically change the printer settings (specific printer, printer driver margins, etc.) the first time the new database is opened?

    Are there other options?

    Thanks for the help!!!!
    Sam

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

    Re: Looking for a creative solution... (A2K SP3)

    This would be much easier in Access 2002 or 2003 - there, reports have a Printer property that can be set in code, and the Printer has properties such as PaperSize, LeftMargin etc. In Access 2000, it's rather messy, I believe, involving the primitive PrtDevMode and PrtMip structures <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a creative solution... (A2K SP3)

    Hans, thanks for responding. I wish our company would pay to upgrade Office so that I would be able to use the Printer property. I've used PrtDevMode in the past. It's so clunky. I was hoping I wouldn't have to resort to that.

  4. #4
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a creative solution... (A2K SP3)

    Well, here's a solution I came up:

    Each computer that uses this unique report has a separate database that just includes this one report. Users on these computers can open up this database to set the specific printer and any page formats.

    After an update to my main database occurs users can import the report with their unique settings by clicking a button (or I may automate this even further). Really, I just need this code:

    DoCmd.DeleteObject acReport, "MyReport"
    DoCmd.TransferDatabase acImport, "Microsoft Access", "PathToDatabaseContainingSingleReport", acReport, "ReportNameToImport", "ImportAsReportName"

    And this works with one big hiccup:

    Since my main database is password protected (VBA - Tools - Properties - Protection) objects can't be deleted or imported into!!

    So, my new question: is there a way to temporarily supply the password through VBA so that I can delete/import objects? Or does anyone know a different way to delete/import objects that would get around the password?

    I thought that if maybe I referenced this other small database that it might work, but I didn't have any luck there either.

    Thanks for any help!
    Sam

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

    Re: Looking for a creative solution... (A2K SP3)

    I don't know, and I cannot find anything relevant about it either - the question has been asked more than once in the newsgroups, but without a satisfactory answer as far as I can see.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a creative solution... (A2K SP3)

    I don't know how to bypass the database password, but here is an alternative that *should* work. Remove the database password and apply user-level security to the database. Remove all permissions (including the Database permission) for the Admin user and the Users group. This will prevent anybody from opening the database. Apply all permissions to a single, password protected user account (or group). Distribute the password to your users, thus providing the functional equivalent of the database password.

    Create a new account with administration permissions. Use this account to delete/import your specialized report. You can use this specialized account "inside" your regular Access session by creating a new workspace in VBA, creating a database object within the workspace, and running the VBA code against the new "priviledged" database object.

    Haven't done this, but I think it should work...
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  7. #7
    Star Lounger
    Join Date
    Jan 2002
    Location
    Washington State
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for a creative solution... (A2K SP3)

    Well, that's pretty cool! It does work! Pretty slick. Sure appreciate the help!

    The only problem is that I'd rather not use Access Workgroup Security. I've written my own security based on a custom user login screen.

    So to implement your idea each user would have to enter the mdw password and then login again with my custom login screen. I can hear the complaining already. [img]/forums/images/smilies/smile.gif[/img]

    The idea's great though! Thanks!

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

    Re: Looking for a creative solution... (A2K SP3)

    I think the general concensus is that database passwords are a less reliable protection scheme than User Security. For one thing, they are easily hacked. And if you are working with the password and store it in code, since the VBA project is not secured in 2000 and up, it is pretty easy for someone with basic knowledge of VBA to get at the password. So why did you choose to write a custom security procedure as opposed to using Access User Security? The latter is not without it's faults, but it actually works pretty well in my experience.

    All that aside, <!mskb=235422>Microsoft Knowledge Base Article 235422<!/mskb> "How to open a password-protected database through Automation in Access 2000" may help you solve your problem. It does unfortunately embed the password in code.
    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
  •