[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

Linking Tables to Password-Protected Access 2007 Back End

How can I programatically link tables to a password protected back-end in access 2007 without any user prompts asking for the back end password? This is designed for access 2007 runtime environment.

In this project, I am planning on linking all tables to the password-protected back end once the user has entered their login credentials on the login form. The login form briefly connects to the user table on the back end to check the user password then deletes the link through a macro. This is a problem because each time I need to link to the back end, I have to enter the password to the back end.

I would like the password entered programatically each time it tries to connect so it is a seamless process.

I am willing to admit that I may be going at this the wrong way, but if there's a better way to keep the tables un-linked until the user enters their password, that would be great. Thank you for your help and your time.

Regards,
Charlie
0
charlesguzman
Asked:
charlesguzman
  • 8
  • 8
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok. Here is the de-facto standard for relinking your front end master to the common backend:

http://www.mvps.org/access/tables/tbl0009.htm

And in this part of the code you can hard code the password.
               Dim sPwd as String                       'Add these two lines
                sPwd = "YourSecretPassword"


                Set tdfLocal = dbCurr.TableDefs(strTbl)
                With tdfLocal
                    .Connect = ";Database=" & strDBPath & ";Password=" & sPwd
                    .RefreshLink
                    collTbls.Remove (.Name)
                End With
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I'm not positive about this password part syntax

  .Connect = ";Database=" & strDBPath & ";Password=" & sPwd

OK ... I found it ... this:

.Connect = ";Database=" & strDBPath & ";PWD=" & sPwd
0
 
charlesguzmanAuthor Commented:
Thanks so much for your response,

I'm going to try this out here, and I'll let you know if I'm able to get it to work. Thank you for you help mx.

Charlie
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
btw ... as you will also see noted in the KB's I posted in the other question, be absolutely sure that ... each user has a copy of the front end master on their workstation.  Do not try to share a common front end.  Yes, it can be done ... but the time tested best practice is a copy for each user.

There a automated ways to help with deployment, if that is an issue.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome ...

mx
0
 
charlesguzmanAuthor Commented:
I tried the code you gave me and here is what mine looks like now:

When I click the button, it tells me: "Runtime error 424, Object Required". It then highlights "Set tdfLocal = dbCurr.TableDefs(strTbl)" and I'm not sure why? I Declared the variable tdfLocal as a TableDef. Is that wrong?


Private Sub Command0_Click()

Dim sPwd As String
Dim strDBPath As String
Dim strTbl As String
Dim tdfLocal As TableDef

strDBPath = "C:\Users\Charlie\Documents\Database\Rytech\Rytech.accdb"
sPwd = "ax4bmk3"
strTbl = "tblUsers"

Set tdfLocal = dbCurr.TableDefs(strTbl)
With tdfLocal
.Connect = ";Database=" & strDBPath & ";Password=" & sPwd
.RefreshLink
collTbls.Remove (.Name)
End With

End Sub

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, first ... you want create a new VBA module, that paste all that code (the Function) into that module.

Then, you would typically call that code as part of a app startup routine.  Keep in mind the intent of this code is to 're-link' an FE to a BE if the BE has moved.  I should have made that clear, sorry.
Now, you should be able to initially ... manually link the FE to the BE tables, entering your password when asked for, using the built in Access linking utility.  This will initially encode your password in the connection string (hard coded basically).

So ... actually, you probably don't need this code initially.  

Hope that makes sense ?

mx
0
 
charlesguzmanAuthor Commented:
Thanks MX,

I may have to use this article: http://support.microsoft.com/?id=275249. My only concern with this is that I believe that the VB references have to be re-referenced once access developer extensions is used. My hopes is that once I create the references initially, that they will stay in vb and that it will work in the access runtime environment.

What I mean by the above is that I have to add in two new references. I don't want to have to do that on each machine I put this on...plus I couldn't because it would be installed as a program rather than exist as an editable accdb. I have to use extensions because they don't have 2007 - they have 2003 and I need the attachment field.

I did get all those KB's you sent me before. Very valuable information which I will look over. I'm self taught in this and still am learning as I go :) Thanks again man.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Not sure why you would need to user ADOX?
If you are going the runtime route, that in fact ... that code/link I posted could still work. It can also prompt the user if you incorporate the GetFileName function.  And there is no issue with references per se.

mx
0
 
charlesguzmanAuthor Commented:
Well,

Maybe I didn't specify what I'm trying to do properly. Let me try to state it like this (and thanks for your patience)

1. No tables are linked to the FE
2. User Starts up the application and the Login form appears
3. The user enters their credentials (which are stored in the pw-protected BE)
4. The user presses login, and the database connects to the BE (through code with hard-coded pw)
    to get the user's information.
5. Once the database form has compared the users entered data vs the actual data, it disconnects the
    table link by deleting it.
6. This process continues until: Once the user has successfully entered the appropriate username
    and pw, the database FE will then link all the tables to the FE and the login process will be complete.

So basically, I'm not refreshing existing links, but rather specifying the database BE through the code, the BE password through the code, and the names of the remote tables to link. No prompts should appear for any reason to the user.

I do think your code could be adjusted to work, but as I tried it out (and it did work to refresh the links), I'm not sure how to make that code link to existing tables that I specify.
0
 
charlesguzmanAuthor Commented:
Hi MX,

Just wanted you to know that I found the answer. I'll post it so other's can see it as well. This will easily link a table to a password-protected back end whenever you want with little code:

Your assistance and expertise is very much appreciated.

Set dbs = CurrentDb
Set tblDef = dbs.CreateTableDef(LocalTableName)
tblDef.Connect = "MS Access;DATABASE=" & RemoteDBName & ";" & "PWD=YourPassword;"
tblDef.SourceTableName = TableInRemoteDB
dbs.TableDefs.Append tblDef
Set tblDef = Nothing
Set dbs = Nothing

Open in new window

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Charlie,

Once the user has logged on and created all the table links, how do you stop someone from linking to the running front end and getting all the connection string information or importing the linked tables?


0
 
charlesguzmanAuthor Commented:
The HiTechCoach,

Is it possible to link to a packaged solution running as a .exe? This is made to run in the Microsoft Access Runtime Environment only. I really don't know an answer to your question (other than using access's built-in user-level security?). The idea is that the person logging in is a trusted employee. Do you have an answer to that question or was it rhetorical?

Thanks for the input HiTechChoach, it is a valid question.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Is it possible to link to a packaged solution running as a .exe? "
No. But, you will want to package your FE as an MDE (ACCDE in A2007/10), to minimize users messing with the FE objects.  Unfortunately, no matter how you slice and dice it, with all the known tricks, it's still possible to get the FE open, and look at the linked table connection string.

Trusted Employee is your answer ...

Also, ULS (User Level Security) is no longer support with the ACCDB format, but you can run an A2003 mdb with ULS in the A2007/10 environment.

mx
0
 
charlesguzmanAuthor Commented:
Mx,

That's what I thought originally. Access is not the best at protecting itself from malicious employees or end users will bad intentions.  Hopefully the employees will be busy working rather than trying to gain access to privileged information.

charlie
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Access is not the best at protecting itself from malicious employees or end users will bad intentions."
Yeah You Right, lol.
I'm sure (?) you are aware that ... the Access password on A2003 and prior is EASILY hackable with many free tools on the internet.  Now, supposed the password paradigm has been improved in the ACCDB format ...

mx
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
You might want to look into this:

vPPC Toolkit for 2007

0
 
charlesguzmanAuthor Commented:
Thanks, I have bookmarked your suggestion in case someone asks me to make a more secure solution in the future. Plus it's nice to know there are security options like this available.

charlie
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now