Access/MSSQL linkage, some background questions

[Bernhard Giden]

Hello everybody,

I have some background questions regarding linked tables. I've used the MSSMA tool to link my access database to a remote MSSQL server.

a) It seems that the SQL user password is stored as clear text. I want to change that: At the start of my access application the user should have to enter this password each time. Is that possible?

b) It looks like the connection to the remote MSSQL server is not encrypted. I can watch all the queries via a network sniffer like wireshark. Is there a possibility to enforce encryption?

This would be vital to me, because the MSSQL server is on a remote location - via open Internet (no VPN or something secure like that possible)...

c) I found articles on the internet telling me that the use of passthrough-queries is important. Otherwise all the joined tables are loaded to the client and then the where-clauses are applied. Using wireshark this doesn't seem to be true in access 2007. Are these articles nonsense or did I forgot something?

Looking forward for your answers!

Best regards,
Bernhard Giden

PS: Sorry for bad English, it's not my mother tongue.

[Bernhard Giden]

d) Also I'm looking for a quick way to relink the tables to another database. Like a development database.

Maybe there is a possibility to provide the connection data via VBA? I could read the data via VBA from a configuration file provided with my access application.

e) If some joins/where-clauses are applied on the client side (like in question c). Do I have to manually set the relationships in MS access? They haven't been loaded to MS access as far as I can see. If I open the relationships editor, none are there.

Best regards,
Bernhard Giden