connecting to password protected backend

[Chris]

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

[Wayne Morgan]

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" <ch*******@hotmail.com> wrote in message
news:cf**************************@posting.google.c om...

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

[Chris]

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

[Wayne Morgan]

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" <ch*******@hotmail.com> wrote in message
news:cf**************************@posting.google.c om...

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