StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I think you can get this to work the way you want it to if you use an <a href="http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer" rel="noreferrer">"ODBC DSN-LESS connection"</a></p> <p>If you need to, keep your ODBC DSN's on your users' machines using windows authentication. Give your users read-only access to your database. (If they create a new mdb file and link the tables they'll only be able to read the data.)</p> <p>Create a SQL Login which has read/write permission to your database.</p> <p>Write a VBA routine which loops over your linked tables and resets the connection to use you SQL Login but be sure to use the "DSN-Less" syntax.</p> <pre><code>"ODBC;Driver={SQL Native Client};" & "Server=MyServerName;" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" </code></pre> <p>Call this routine as part of your startup code. </p> <p><strong>A couple of notes about this approach:</strong></p> <ul> <li><p>Access seems to have an issue with the connection info once you change from Read/Write to Read Only and try going back to Read/Write without closing and re-opening the database (mde/mdb) file. If you can change this once at startup to Read/Write and not change it during the session this solution should work.</p></li> <li><p>By using a DSN - Less connection you are able to hide the credentials from the user in code (assuming you're giving them an mde file you should be ok). Normally hard-coding connection strings isn't a good idea, but since you're dealing with an in-house app you should be ok with this approach.</p></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload