modules in Access, connection strings, and macro usernames/passwords

[David Shorthouse]

Hello folks,

I'm not sure if this is possible, but I would appreciate any guidance. I
have a number of passthrough queries in an Access template I am developing
for clients as well as some linked tables. In order to make my life easier
in the event that I need to change a client's password or user id in SQL
Server, I would like this event to be easy to implement for the client. So,
I was thinking that perhaps I could have a macro that reads a client-side
table containing nothing more than one record with 2 fields: User ID and
Password such that these will be used in all of the connection strings in
the linked table and passthrough queries in an AutoExec macro. How can I
implement such an idea?

Thanks,

Dave
______________________________
Remove "_SPAM" to reply directly.

 

[Damien]

It is possible to dynamically set connection strings in Access, but why not
request a read-only account for your SQL database and use that in your
pass-thrus? Assuming the clients can't open the queries directly, that
should solve your problem right?

I'd the same for any tables you need, if you really need them, just wrap
them in a pass-thru, eg

qpas_client
SELECT * FROM tbl_client

If you need to write back to the server, open a separate connection, with
the users details.

Having said all of that, have you thought about using ADO instead? Linked
tables requires a local DSN etc which can be a real pain when distributing
your solution, and is the 'modern' way to do things, even with Access.

Let me know what you think.


Damien

 

[David Shorthouse]

Damien,

Well, things in life are never simple. What I am attempting to do is
develop an Access template that performs a full double join (i.e. append and
update via a union of a local table to a linked table) for clients who
aren't particularly adept at handling separate appends and updates. I
thought I would handle this for them. Another reason for this is because
client to server takes place over the Internet and I would much rather them
working on a local table and then click my macro in a form to perform the
full double join. To date, I have an AutoExec macro to handle the linked
table creation via a DSN-less connection module. Should the connection fail
because of flaky Internet issues, they can at least work on their local
table and when their Internet connection (or mine) is working once again,
they may use the form button I created to perform the update/append to their
linked table.

Now, since I am using a few modules to create the linked table (and a
few passthroughs to stored procs) and since I envision these folks losing
this lovely template I am developing for them, I would like to safeguard
against potential security issues. i.e. I can quickly change their sql
server passwords if need be making the lost Access template useless should
it wind up in the wrong hands. Since I would now need to send them a new
template, I thought it would be most convenient if all the connection string
parameters were tied to a table in the Access template such that all I would
need to do is change the password in the appropriate record within this
table (or macro), then fire the template off to them. Likewise, if I cycle
the SQL server passwords, I just need to inform these clients of their new
passwords and they can change the password record in their local table.

Thanks for the dialogue,

Dave