I have to work on an access 97 multiuser system (I used to be very strong in access 97-2000) that is linking to Sql Server (probably 6.5) I cannot upgrade. repeat: i cannot upgrade :(
The Problem:
currently they are using an odbc connection, and holding onto a windows 98 machine to relink tables with, since 2000/xp seems to cache some extra stuff that causes odbc errors.
to make things even more complicated, the code is written to require users to log into the SQL Server database with actual Sql Logins (as opposed to one set login & identifying the users from a login table). they do this via a login form in access.
and to top it all off, they are linking 164 tables.
The Question:
1) given that they have to use different sql server logins, how can I switch this to a dsnless connection that is somehow caching the SqlServer user/pass login for each table? doing a relink process each time is impractical due to the number of tables.
2) I can't seem to find the correct dsnless connection string. it should be dead simple, but it ignores the "trusted_connection=no" and tries to force windows authentication.
Thanks in advance for the help.