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

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

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

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.

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.

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

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

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

crapo,

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

~Jillyn

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

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!

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:

https://www.experts-exchange.com/questions/21310743/Hide-Unhide-Access-Objects.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

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

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

Just wanted to be sure :)