Multiple connection strings

[isandell]

I have been tearing my hair out with this so I hope that someone can help.

I have an Access front end linking to MySQL server tables, via connection string (dsn-less connection). I regularly change the username and password and refresh the connection using this code:-

Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs

For Each Tdf In Tdfs

If Tdf.SourceTableName <> "" Then
Tdf.Connect = "ODBC;Driver={MySQL ODBC 5.1 Driver};Server=MyServer.co.uk;Database=myDataBase; User=myUsername;Password=myPassword;Option=0;"
Tdf.RefreshLink
End If
Next

That all works fine, but I now want to use different connection strings. I can get different connection strings to appear in the Table Properties, but when I try to open any table, it always the last connection string that was reset.

For example, if I use
If Tdf.SourceTableName = "SomeTable" Then
Tdf.connect = StrTwo
Tdf.RefreshLink

all the tables will use StrTwo, no matter what the Table Properties say.

Hope that's clear

Ian

 

[vbaInet]

Set tdf to Nothing before using a different connection string.

 

[isandell]

Set tdf to Nothing before using a different connection string.

Sorry, I should have siad that I know very little vba so I dont quite understand. Can you give be a line or two of code please and say where it goes?

Thanks

Ian

 

[vbaInet]

Pretty much what I said:

Set tdf = Nothing

"set tdf to Nothing"

 

[isandell]

Set tdf = Nothing

Does not work.

It seems that it is not possible to have two different connection strings in linked tables.

Access does not use the string that is seen in the linked table's properties. The string must be stored elsewhere in Access.

Yet another thing that doesn't work in Access.

Ian

 

[tehNellie]

Yet another thing that doesn't work in Access.

Ian

bad workman and all that...

I did have something a bit more constructive to add but I've lost the enthusiasm to type, the grumpy teenager act doesn't help really.

The only thing I can still be bothered to add is whether you really want linked tables that might be pointing to different data than what a user might think it is. If the location of the actual data is changing that's one thing, but re-pointing a table to somewhere else seems very risky to me especially if that re-pointing relies on a user remembering to do something.

 

[gemma-the-husky]

i tend to have a local table storing the connection strings i want to use for each table. I have a default setting for those tables with nothing explicit in this "linking table"

that way different tables can link to different back ends.


not sure if makes a difference, but I never use refreshlinks. I always recreate the tdf object from scratch.

 

[isandell]

bad workman and all that...
The only thing I can still be bothered to add is whether you really want linked tables that might be pointing to different data than what a user might think it is. If the location of the actual data is changing that's one thing, but re-pointing a table to somewhere else seems very risky to me especially if that re-pointing relies on a user remembering to do something.

I clearly have not explained properly.

If you try to link tables to two different strings, all table will only connect to one of those strings (I think the last one to have been connected). Although some tables will appear to connect to the other string, in practice they will not.

I might be wrong about this, but if anyone has actually managed to connect two linked tables to two diffect strings in the same database, I would be delighted to know.

Ian

 

[isandell]

i tend to have a local table storing the connection strings i want to use for each table. I have a default setting for those tables with nothing explicit in this "linking table"

that way different tables can link to different back ends.

I am interested in learning more how you do this. How do you store a connection string in a local table? I thought that connection strings are entirely to do with linking tables to backends.

Thanks

Ian