Solved

Is there a way to pass an Access Database password through VBA code?

Posted on 2006-11-27
15
254 Views
Last Modified: 2008-02-01
I have a specific need to lock down an Access front-end so that users cannot use another (secondary) access database to "Import" the tables and queries.

While a database password will satisfy that need, it also creates another issue: I do not want users to have to enter (or know) the database password.   Is there a way to pass the password internally, hardcoded in the VBA when the front-end opens (a command-line parameter call when opening the db will not work in this situation either)?


Thanks,

Scott
0
Comment
Question by:crapo
  • 8
  • 6
15 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 18021870
Hi crapo,

Can you clarify, please.
You want to lock your front-end with a password but you don't want users to know it?
Sounds a bit odd.

Pete
0
 
LVL 8

Expert Comment

by:Jillyn_D
ID: 18021958
Hi crapo,

What about linking the non-passworded front-end to the passworded back-end once a user is logged in to the front-end and deleting the links when the user logs out?  That way the user doesn't have to know the password or even where the tables are kept and can only access them via the front-end.

Good luck!
~Jillyn
0
 

Author Comment

by:crapo
ID: 18022026
Hi Pete,

Yes it is odd... I run a modular main menu on the app that provides three levels of interface-based security based on the user's enterprise logon... so I do not need them to login again.  The problem is that they can use another mdb to 'import' the linked tables from the closed .mde.
0
 

Author Comment

by:crapo
ID: 18022060
Hi Jillyn,

That is a great idea, and I tried it.  I run a SQL Express backend and to relink all the tables everytime the front-end opens added a ton of latency.

FYI: I use vba to build the connections on the dev version (one time only, or as needed if the tables change).  This way there are no external ODBC connections are available on the user's PC.
0
 
LVL 8

Expert Comment

by:Jillyn_D
ID: 18022247
crapo,

Sorry it dodn't work!  I haven't worked with SQL Express backends, so I can't be of much help there :(

What about a loader of some sort that programmatically directs them to the mde  If the users don't know where the front end is, they can't grab the linked tables.  If all else fails...Hide stuff!

~Jillyn
0
 
LVL 8

Expert Comment

by:Jillyn_D
ID: 18022255
Oh, and sorry for the delayed response!  I didn't get my little notification thing that a comment had been added.
0
 

Author Comment

by:crapo
ID: 18022360
Hi Jillyn,

>>What about a loader of some sort that programmatically directs them to the mde  If the users don't know where the front end is, they can't grab the linked tables.  If all else fails...Hide stuff!<<

Users actually open a VB version control app that I wrote which opens the mde (it also checks for new versions and automatically downloads them to the client pc, etc), the user never actually opens the .mde file itself (the version control does).

I could pass the password parameter in the version-control software's mde command-line call.  The only issue with this is that I'll then have to use the version-control software's batch file call to update itself... not the easiest thing to coordinate.

That's why I was wondering if there is a way to send the database password internally from the mde itself.

I appreciate your help though!

Scott
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Expert Comment

by:Jillyn_D
ID: 18022386
crapo,

*sighs* sorry :(  You could always use the Access Security thing...It scares me though.

~Jillyn
0
 
LVL 8

Expert Comment

by:Jillyn_D
ID: 18022412
Scott,

One last try...What about prefacing the linked tables in the mde with USys?  This will make them User System tables, and will only show up for import if the user has System objects viewable in their Options settings.  My experience is that most people don't...

~Jillyn
0
 

Author Comment

by:crapo
ID: 18022486
I love those types of tricks!

I'll have to put that in my toolbox... but due to the size and complexity of this DB, it is not possible to change the table names.


Thanks again though.

If you think of anything else, let me know!

My day is over (Thank God), I'll check back in the am.

Have a good one!
0
 
LVL 8

Accepted Solution

by:
Jillyn_D earned 250 total points
ID: 18022606
I'm totally jealous!  I still have an hour and a half :(  Hope you have a great night!

I love that kind of thing as well!  There's a dbHiddenTable setting in table.properties too, but the documentation on that states that tables with this setting are sometimes deleted on compact...Generally not good.  However, if your vb loader produces a new copy of the mde on the client PC each load, that might not be such a terrible thing.  In fact, you could set it to compact on close and it might automatically delete the tables for you...In any case, when a table is set to dbHiddenTable it is totally invisible, even with hidden objects toggled on.  You can only import the tables through code if you know the exact name.

Here's the function I have for this...

Public Function HideAllTables(Optional strDB As String, Optional blHide As Boolean = True)
Dim db As Database, tbl As TableDef
    'Get current db name if strDB is null
    If Nz(Len(strDB), 0) = 0 Then
    strDB = CurrentDb.Name
    End If
   
    Set db = DBEngine.Workspaces(0).OpenDatabase(strDB)
    For Each tbl In db.TableDefs
        Debug.Print tbl.Name
        'Hide or Unhide tables
        If blHide = False Then
            tbl.Attributes = 0
        Else
            tbl.Attributes = 1
        End If
    Next
   
    Set tbl = Nothing
    db.Close
    Set db = Nothing

End Function
0
 

Author Comment

by:crapo
ID: 18027881
Thanks Jillyn,

I took that concept and code and ran with it!

I am not just hiding the tables, but all the objects using this archived code I found on Google:

http://www.experts-exchange.com/Databases/MS_Access/Q_21310743.html


Public Sub HideUnhide(ByVal bHideUnhide As Boolean)

    'Modules

    Dim obj As Object
    Dim i As Integer
   

    'Tables
    For i = 0 To CurrentDb.TableDefs.Count - 1
        If Left$(CurrentDb.TableDefs(i).Name, 4) <> "MSys" Then Application.SetHiddenAttribute acTable, CurrentDb.TableDefs(i).Name, bHideUnhide
    Next i

    'Queries
    For i = 0 To CurrentData.AllQueries.Count - 1
        Application.SetHiddenAttribute acQuery, CurrentData.AllQueries(i).Name, bHideUnhide
    Next i

    'Forms
    For Each obj In CurrentProject.AllForms
        Application.SetHiddenAttribute acForm, obj.Name, bHideUnhide
    Next obj

    'Modules
    For Each obj In CurrentProject.AllModules
        Application.SetHiddenAttribute acModule, obj.Name, bHideUnhide
    Next obj

    'Reports
    For Each obj In CurrentProject.AllReports
        Application.SetHiddenAttribute acReport, obj.Name, bHideUnhide
    Next obj

End Sub

You get all the points.... thanks a lot.

Scott
0
 
LVL 8

Expert Comment

by:Jillyn_D
ID: 18028012
Scott,

Thank you!  Glad I finally came up with something helpful!

Just a note on the code included, it is safer, but doesn't hide database objects as thoroughly as dbHiddenTable.  It should have the same effect as toggling the Hidden box on the object's Properties screen, so the items will be visible but shaded if a user has Show Hidden objects toggled on in their Access Options.  And if your users are somewhat knowledgeable, they'll realize that the database can't be empty and still be useable...In my experience, making everything hidden causes the curious ones to dig deeper.  Something to consider and be prepared for :-/

Hope all goes well!
~Jillyn
0
 

Author Comment

by:crapo
ID: 18028301
Understood...

But until I create a new version of the version control software that will open the database with a db password, this will allow me to stay one step ahead!


s
0
 
LVL 8

Expert Comment

by:Jillyn_D
ID: 18028381
Just wanted to be sure :)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now