change connection string to linked table with VBA

[salmonella]

OK, I have an Access FE that I am connecting to a MySQL BE via linked tables.
Everything works fine however the user must enter a username and password
when connecting to the BE. To get around this, when the dB is opened I want
to reset the connection string for each linked table to one with the pw and
username, refresh the link (tablefefs.refreshlink), then set it back to the
string without the pw and username to secure it. I tried this on one table
and I could not change the table’s connection string.

To simplify things, if I run the following- should the connection string for
the linked table not be changed to “test�

Dim x As String
x = "test"
CurrentDb.TableDefs("table name").Connect = x
MsgBox "" & CurrentDb.TableDefs("table name").Connect


Can someone tell me what I am doing wrong?

thanks

 

[Banana]

As matter of fact, it is not necessary to reset the connection string so
password isn't stored.

Normally, it's sufficient to just store three things in a connection
string for both tables and queries:

"ODBC;Driver=<driver name>;Server=<server
name/location>;Database=<database name>"

Where you need to fill in values where I put in <>s. With MySQL, if you
are using MyODBC 5.1 and the server is on same machine, and database is
named "Foo", it'd look like this:

"ODBC;Driver={MySQL 5.1 ODBC Driver};Server=localhost;Database=Foo"

If you do not want your users to be prompted with a password dialog, you
need to first open a connection in the code. Access is smart enough to
reuse the same connection for all tables and queries as long they all
match the same driver/server/database combinations.

Look in VBA Editor help for help on writing VBA code using OpenDatabase
method, especially for against ODBC backend. It may end up looking
something like this:

Set MyDb = DBEngine.OpenDatabase("", dbDriverNoPrompt, False, "<complete
connection string with username & password included>")

HTH.

 

[salmonella]

Thanks for the reply.
I appreciate your suggestion on opening up the database with password etc.,
and I will give that a try if this fails (in fact it seems that it may be the
most direct way to do what I want)-However, I still don't understand why my
approach is not working and want to figure this out- can you suggest why it
is not working? a bit more info on things....

I am on a university network connecting to the BE on a server via a mapped
drive (z
the actual code I am testing is:
Dim x As String
x = CurrentDb.TableDefs("table_name").Connect ' connection string that is
working but that does not have pw/username

for your information x is (KEY ETC. MODIFIED)
x = "ODBC;driver=MYSQL ODBC 5.1
Driver;SERVER=143.453.172.23;DATABASE=10_be;PORT=3306;SSLKEY={Z:\certs\server-key.pem};SSLCERT={Z:\certs\server-cert.pem};SSLCAPATH={Z:\certs};SSLCIPHER={UYS-IIS-AES256-SHA:AES128-KKU}"

CurrentDb.TableDefs("TABLE NAME").Connect = x & "; UID=" & "USERNAME" & ";
PWD=" & "PW"

CurrentDb.TableDefs("TABLE NAME").RefreshLink

MsgBox "" & CurrentDb.TableDefs("table name").Connect & vbNewLine &
vbNewLine & x

and the msgbox gives an unchanged link and will query me for pw and username
when refreshlink is read (if I am not already connected via
passord/username.) suggesting that it is refreshing the original connstring
without the pw/username

I guess my question is:
why is this not working? :
why is the connection string held in tabledefts for the table not being
changed and used in the refeshlink step?


thanks!

 

[Banana]

Well, connect property can't be changed once the table has been appended
to the TableDefs collection, so it's routine to delete the table, then
recreate a new TableDef with the new connection.

Google for "Doug J. Steele's DNS-less connection" for an excellent
sample. I'm sure you can easily adapt it to the MySQL.

 

[salmonella]

will look into all this.

many thanks!!!

Well, connect property can't be changed once the table has been appended
to the TableDefs collection, so it's routine to delete the table, then
recreate a new TableDef with the new connection.

Google for "Doug J. Steele's DNS-less connection" for an excellent
sample. I'm sure you can easily adapt it to the MySQL.

 

[Piet Linden]

Well, connect property can't be changed once the table has been appended
to the TableDefs collection,

REALLY? Funny, I do it all the time.

As a matter of fact, I created a routine that connected to a list of
databases by updating a linked table's Connect property, ran a few
queries, and went on to the next database...

 

[Banana]

I went and checked and it looks I was wrong. One can change the Connect
property of a linked table. However, the Connect property will not be
actually updated until RefreshLink method is called. Miss that step and
we'd still have the old Connection string.

My apologies.

 

[salmonella]

great- is it possible to establish the connection at startup with pw/uid then
remove them so that things work without the pw/uid in the table link where
people can get into the BE?

do you know what is wrong with this

I am on a university network connecting to the BE on a server via a mapped
drive (z
the actual code I am testing is:
Dim x As String
x = CurrentDb.TableDefs("table_name").Connect ' connection string that is
working but that does not have pw/username
x = "ODBC;driver=MYSQL ODBC 5.1
Driver;SERVER=143.453.172.23;DATABASE=10_be;PORT=3306;SSLKEY={Z:\certs\server-key.pem};SSLCERT={Z:\certs\server-cert.pem};SSLCAPATH={Z:\certs};SSLCIPHER={UYS-IIS-AES256-SHA:AES128-KKU}"

CurrentDb.TableDefs("TABLE NAME").Connect = x & "; UID=" & "USERNAME" &
";PWD=" & "PW"

CurrentDb.TableDefs("TABLE NAME").RefreshLink

does not seem to like something because it queries me for pw/uid

any ideas

thanks!!!!!!!!!!

 

[Bryan]

Hey Sal -

Not sure if this is helpful, but have you tried setting up a linked table
and viewing the properties? This may help find a syntax problem. I've had
problems like this connecting to an Oracle server and it is a pain to figure
out (I had a wrong db name). Does the Driver name need to be in braces
(driver={})? It does for Oracle.

Is the server able to figure out the Z: drive? My SQL server has different
mapping than my desktop.

The syntax to update and refresh is the same as the code that I'm using so I
think it's something in your connection string (X).

Hope that helps - I can't think of much more and haven't had to work with SSL.

Bryan..

 

[salmonella]

Thanks for the comment Bryan-

actually that is what the problem was. I was using pwd and uid in the
connection string instead of password and user- always look for the simplest
solution first!!!

thanks for your time

 

[nik]

Hi,

Would you please explain what you mean by setting up the linked table
and viewing the properties. I am also trying to reset the connection
string in order to circumvent the user and password prompt, and am not
having much luck, so I would like to see the format of a connection
string with user and password information.

I can establish a new linked table, but don't see an option for
viewing the connection string.

I can also access the connection string of my current linked tables,
by watching "CurrentDb.TableDefs(i).Connect", but it does not have
user or password fields in it, so its not much help.

Thanks,
Nik