Solved

Need Read/Write access to Linked Tables

Posted on 2008-06-10
6
1,848 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
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now