?
Solved

Need Read/Write access to Linked Tables

Posted on 2008-06-10
6
Medium Priority
?
1,859 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 600 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 100 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 3

Accepted Solution

by:
bandriese earned 600 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 100 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

771 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