Solved

Need Read/Write access to Linked Tables

Posted on 2008-06-10
6
1,849 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

896 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

16 Experts available now in Live!

Get 1:1 Help Now