Solved

Front end program and back end data passwords.

Posted on 2011-02-17
10
395 Views
Last Modified: 2012-06-27
I have a database split between front end file (queries, forms etc) and back end file (data). Up until now I've only passworded the front end for different users without bothering about the data. So I've set a database password for the back end, working on a copy of the data file at the moment. When I open the front end with its user name and password and then try to relink the tables to the copy of the back end it says "not a valid password", but it hasn't actually asked for the password. What is the correct sequence of actions?

Notes: Both files are in Access 2000 format though I always work on them with Access 2007 - under Windows 7. To set the database password for the back end I used Access 2003.
0
Comment
Question by:TonyVile
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 19

Expert Comment

by:Bardobrave
ID: 34915224
You probably must pass the password and user to back end database from front end one when you make the connection. I mean... the table linking is not going to ask you for user/pass, it will expect them passed as parameters of the connection order.
0
 
LVL 84
ID: 34915520
If I'm not mistaken, the FE and BE passwords must be the same.
0
 

Author Comment

by:TonyVile
ID: 34916831
That's part of the way, thanks. What I'm finding however is that if I link to the BE at the beginning and it's the same password that's OK, but if I do a Relink it's not. Any ideas?
0
 
LVL 10

Accepted Solution

by:
conagraman earned 167 total points
ID: 34927147
good question
 i didnt know how to do that eaither. what i was completly forgetting was the obvious i am making a connection to a database. so the correct connection string is needed.

the back end connection string with a password  is :
                           ";DATABASE=" & "YourPath" & ";PWD=" & "YourPassword" '& ;"



the below code in a button will reconnect with a password. change mypwd = your password.


Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Dim dbpath As String
Dim boolresult As String
Dim MyPwd As String
Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Back End|*.accdb"
objDialog.FilterIndex = 1

boolresult = objDialog.ShowOpen

dbpath = objDialog.FileName
MyPwd = "password"
'strConnect = CurrentProject.Path
strConnect = ";DATABASE=" & dbpath & ";PWD=" & MyPwd '& ;"



Set db = CurrentDb
For Each tdf In db.TableDefs

If tdf.Connect <> "" Then

tdf.Connect = strConnect
tdf.RefreshLink
End If
Next tdf

Set tdf = Nothing
Set dbs = Nothing
0
 
LVL 84
ID: 34928759
< if I link to the BE at the beginning and it's the same password that's OK, but if I do a Relink it's not>

How are you relinking? If you're doing so through the Linked Table Manager, then you'll have to reenter the password I believe.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:TonyVile
ID: 34930349
That's the thing. It doesn't ask me for a password but produces the error message "not a valid password". Aaargh
0
 
LVL 84
ID: 34930631
Are you using the LTM? If so, you might try deleting and recreating those links as needed. Better still, include code to automate the process, and delete/recreate the links as needed.
0
 
LVL 10

Expert Comment

by:conagraman
ID: 34935627
tonyvile

that is the error when you use that code without the password on a passworded backend. try the code i gave you in a buttons click event should do the trick
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 83 total points
ID: 34937090
Just an FYI:

If I'm not mistaken, this code:

CreateObject("UserAccounts.CommonDialog")

won't work on operating systems other than Windows XP. I don't use it, but I've seen it referenced here a few times, and seem to recall others posting odd results with that syntax. Could be a permissions issue, of course, but it seems to have been removed during one of the "security releases" for other versions of Windows.


0
 
LVL 10

Assisted Solution

by:conagraman
conagraman earned 167 total points
ID: 34942675
I was not aware of that thanks for the tip LSM
well i was just using that to locate the back end you can use anyway you want. another dialog or just hardcode it.   the below code is the nitty gritty : )


Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Dim dbpath As String
Dim MyPwd As String


dbpath = "c:\yourdatabasePath.accdb"  '--------Your back end path goes here
MyPwd = "password" '--------Your back end password goes here

strConnect = ";DATABASE=" & dbpath & ";PWD=" & MyPwd

Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Connect <> "" Then
tdf.Connect = strConnect
tdf.RefreshLink
End If
Next tdf

Set tdf = Nothing
Set dbs = Nothing
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

863 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

26 Experts available now in Live!

Get 1:1 Help Now