connecting to password protected backend

I have a frontend that has some tables which are linked to a backend.
Now I want to make the backend password protected and when I try to
run it password protected I can't open it. In the first form that
opens in the front end, I added the following to the form_open event:

Dim cnn As ADODB.connection

Set cnn = New ADODB.connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Properties("Data Source") = "C:\backend.mdb"
cnn.Properties("Jet OLEDB:Database Password") = "password"
cnn.Open
'Set cnn = Nothing

It seems like it is running the code but not opening up the links or
not keeping the connection open. After I run this code and I click on
one of the linked tables I get the message "Not a valid password". How
can I get it to work as if there was no password on the backend? I
posted this message on this newsgroup and was told to remove the Set
cnn = Nothing but that doesn't fix things. I also tried DAO Tabledefs
and played around with .RefreshLink but I can't seem to sort out the
problem. I'm sure someone knows how to do this.


Thanx,
Chris

re: connecting to password protected backend

Once you put a password on the back-end, delete and recreate your table
links in the front-end. When you recreate them you will be prompted for the
password.

--
Wayne Morgan
Microsoft Access MVP


"Chris" <chrisw_14@hotmail.com> wrote in message
news:cfb38ed9.0404210355.4790b250@posting.google.c om...[color=blue]
> I have a frontend that has some tables which are linked to a backend.
> Now I want to make the backend password protected and when I try to
> run it password protected I can't open it. In the first form that
> opens in the front end, I added the following to the form_open event:
>
> Dim cnn As ADODB.connection
>
> Set cnn = New ADODB.connection
> cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
> cnn.Properties("Data Source") = "C:\backend.mdb"
> cnn.Properties("Jet OLEDB:Database Password") = "password"
> cnn.Open
> 'Set cnn = Nothing
>
> It seems like it is running the code but not opening up the links or
> not keeping the connection open. After I run this code and I click on
> one of the linked tables I get the message "Not a valid password". How
> can I get it to work as if there was no password on the backend? I
> posted this message on this newsgroup and was told to remove the Set
> cnn = Nothing but that doesn't fix things. I also tried DAO Tabledefs
> and played around with .RefreshLink but I can't seem to sort out the
> problem. I'm sure someone knows how to do this.
>
>
> Thanx,
> Chris[/color]

re: connecting to password protected backend

Thanks Wayne,
Great, it worked. I don't know why I didn't think of that. Another
question. When I delete the linked tables and re-add them and it asks
for the password, i type it in and I can connect to the backend
tables. I then close the frontend and re-open it, it DOESN'T ask for
the password to open the tables. Because I will install this database
on many other machines, I am wondering if it keeps the password for
the backend somewhere in the frontend file or on the computer.

I am currently not at home to fully test all this but I am wondering
two more things:

1) I have code to relink the frontend to another backend (with same
table names) also with a password protect, I am wondering how it will
affect this, whether it will ask for the password when the relink is
being done and whether I can use code similar to what I pasted in the
newsgroup message to do the relink.

2) Do I need the code I used on the form_open event, it seems like I
don't. But it may depend on whether it holds the password in the
frontend file or on the computer.

I will experiment at home but if you know any more about this i'd love
to hear it.

Thanks,
Chris

re: connecting to password protected backend

To the best of my knowledge, the password is stored in the front-end. As
long as you don't change the password and have previously connected with the
password, you should be able to run the reconnect code, but I haven't tried
it. To change the location of the link, you change the Connect property of
the TableDef object. The path must be a valid path and UNC paths are
allowed. If the back-end is on a network share, I would recommend using the
UNC path, it saves the hassel of setting up shared drives. To see if a table
is a linked table, check the Connect property. If it's not null or a zero
length string (I don't remember which right off-hand, but I think it is the
zero length string) then it is a linked table. You can also use the
Attributes property to see if it is a linked table.

You shouldn't need the code in your Open event.

--
Wayne Morgan
MS Access MVP


"Chris" <chrisw_14@hotmail.com> wrote in message
news:cfb38ed9.0404220123.2c77bbe6@posting.google.c om...[color=blue]
> Thanks Wayne,
> Great, it worked. I don't know why I didn't think of that. Another
> question. When I delete the linked tables and re-add them and it asks
> for the password, i type it in and I can connect to the backend
> tables. I then close the frontend and re-open it, it DOESN'T ask for
> the password to open the tables. Because I will install this database
> on many other machines, I am wondering if it keeps the password for
> the backend somewhere in the frontend file or on the computer.
>
> I am currently not at home to fully test all this but I am wondering
> two more things:
>
> 1) I have code to relink the frontend to another backend (with same
> table names) also with a password protect, I am wondering how it will
> affect this, whether it will ask for the password when the relink is
> being done and whether I can use code similar to what I pasted in the
> newsgroup message to do the relink.
>
> 2) Do I need the code I used on the form_open event, it seems like I
> don't. But it may depend on whether it holds the password in the
> frontend file or on the computer.
>
> I will experiment at home but if you know any more about this i'd love
> to hear it.
>
> Thanks,
> Chris[/color]