Need Read/Write access to Linked Tables

I'm updating a client's Access front ends programmatically to change linked tables from an insecure Access back end to a more secure MSSQL database.  The linked tables seem to be read-only, but the application splitter wizard makes me believe that read-write ODBC linking is ossible.  What do I need to do to allow insertion and deletion of records in these tables?  Thanks!
ftmickeyAsked:
Who is Participating?
 
bandrieseCommented:
Oh no. Much easier than that. Link the SQL Tables in Access from your ODBC connection (I use System DSNs), then build your forms based on those linked SQL tables. There some changes between using SQL as your data store vs. Access locally but not in the fundamentals we're discussing here. You don't need to write a bunch of code behind your forms simply because you're using SQL as the back-end. If you were creating your app in VB, (aka .NET) then you would, but not in Access. It will allow Read Write access so long as whomever is using the SQL Database has permissions to do so. Also, if you're linking to a view (SQL Query) that is not updateable that might be part of the problem also. If you're not able write data to your SQL tables from with-in Access, Access is likely NOT the problem.

A little more info that might help. If you're just testing/developing right now, and you have admin rights on the SQL Server (or domain admin), then you should be able to read write without setting up any specific permisions on the database, but only if you're a domain admin. Otherwise, you'll need to adjust the permisions so you have access. If I'm off base here, another things that comes to mind is make sure you have the latest updates (MDAC drivers) http://update.microsoft.com/.
0
 
bandrieseCommented:
You might check the parmissions on the SQL Server. Right-Click on the Database and click on Permissions. If you havn't set anything up yet, the best thing I've found is to 1) create a Group in on your Domain controler that contains the users who will use your database, then 2) add a login for that group in your security folder on your SQL Server. 3) Right-Click on the Database and click on Permissions. Add the new login (in this case a group) to the list and make sure they have Connect, Select, Insert, Update, Delete, at minimum. ODBC will allow Read and Write. I have an Access from end to a SQL Database, and works fine using the SQL Driver in ODBC.

See a screen shot attached.
Permissions.jpg
0
 
ftmickeyAuthor Commented:
Is is using SQL or Access forms and objects to do the updates?  I know that I can use SQL, but there's a WHOLE lot of code in these applications that takes the approach of assigning a table to the form and then modifying form controls to update the current record.  Reading I've done since the original question sounds as if Access links to ODBC tables are read-only by definition, and I'm afraid that may be my problem.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GRayLCommented:
You should be able to set read only or read/write permissions.   Remember the OS on the server also has to be 'informed' that the list of users need write permission on the folder containing the SQL db.
0
 
mrnevCommented:
Also, the tables will need a primary key defined before they will allow inserts / updates from within Access.  If the table doesnt have a logical primary key then you are going to have to create and use an identity column or something just to ensure you meet this requirement.
0
 
GRayLCommented:
ftmickey:  So what cured it?  You've split points but I'm afraid I'm none the wiser.  I don't think anyone reading this thread in the future, looking for an answer will, any the wiser either.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.