Solved

Need Read/Write access to Linked Tables

Posted on 2008-06-10
6
1,853 Views
Last Modified: 2013-11-29
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!
0
Comment
Question by:ftmickey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 3

Assisted Solution

by:bandriese
bandriese earned 150 total points
ID: 21754580
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
 

Author Comment

by:ftmickey
ID: 21754755
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
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 25 total points
ID: 21755130
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Accepted Solution

by:
bandriese earned 150 total points
ID: 21755168
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
 
LVL 4

Assisted Solution

by:mrnev
mrnev earned 25 total points
ID: 21755196
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
 
LVL 44

Expert Comment

by:GRayL
ID: 21756071
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

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question