Link SQL Server to MS Access frontend

Hi All,
I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?

Regards:
PrathmeshHi All,
I have recently migrated from Access to SQL Server. I have come across a strange problem. I have got a table in my database whihc is not linked to any other table, but does include data and is used from time to time for reports. It is more used as a sort of lookup table. Now I had the frontend in Access and I have retained it. So I connect all the SQL Server tables via ODBC to Access. The problem with this table is for some reason it wont allow to add new records on Access side. You can just view the contents, but cannot add anything to it e.g a new record, etc. I tried giving explicit permissions of Select, Insert, update, etc. but still no result. There are other look up tables as well, that have the same problem. Can anybody tell me why this is so and is there any way to fix it?

Regards:
Prathmesh

Most likely the issue is that there is no primary key that Access can identify. By default, access will open an ODBC record set in update mode (you can add, delete, or modify the records that you see). But if access can not identify a primary key (or if none exists), then access will open the recordset in read only mode.

As a side note, sometimes even if you have a primary key, Access will "guess" the wrong primary key. In that case all the records will appear as "#DELETED" in the recordset view.

You may want to consider an Access Data Project (ADP) which uses ADO (not ODBC) to connect to a SQL back end. Access is a wonderful tool and a fine way to generate reports, but it does have some limitations.

Regards,

hmscott|||Just a couple of things you might want to look at:

1. When you linked the tables from SQL Server into the MSAccess application did you check the Save Password box?
2. Is the login you're using or does your loginID from LDAP show in the Security Manager in SQL Server and do you also have it in the database for Users with permissions to the Public role? (it usually defaults to this)
3. Sometimes I found that I had to go into the Roles (Public) and check (green check mark) all the user created tables (checking ALL columns in that row - including the RFI column.)
4. When you linked the SQL Server tables, were you prompted to identify a primary key in the linking table? (this goes back to hmscott's email)
5. Are all the DSN's the exact same name for anyone accessing the tables. If you have a different DSN Name for other users, you will have some problems.
6. How is your security set up in SQL Server (Mixed Authentication?)
7. You can also try creating a TimeStamp field on the tables but I doubt this would be an issue as it's usually a problem with having Memo (Text) fields in the table.
8. When you created the DSN for ODBC, did you create this as a System DSN so any user's who logs in to your machine would have permissions? Or are you using a User or File created DSN?

In regards to re-writing this as an ADP, I actually like the method of linking in the tables or writing the code with a "cnn" connection. Our forms are all unbound in the MSAccess application and utilize Write/Update/Delete functions. When a customer is retrieved from the search menu, the SQL Statement fires (which spikes the server a little), returns the values to the unbound form, and then closes the SQL Connection. If something is updated, a checkbox (called: DataUpdated on the form) is marked as true to indicate that the Update function needs to be called (or you could have a specific "Save" button on the form.) This method has worked great for us to make updates to the SQL Server tables without having to worry about someone having the MSAccess ADP or MDE file open and on a specific record (where they left it and went to lunch.) This makes it more difficult for SQL Server to update the new/updated field names in the table and sometimes ends up hanging up or takes an extremely long time and we have to find the user's in the database and have them close out so SQL Server can complete the update. Thus I've become a fan of writing unbound forms which has worked almost flawlessly (and very fast) for us even with external users connecting via Citrix or Remote Desktop Connection. (my thoughts on this is that it's all on how you write your code and also making sure your hardware is sufficient). Some people say MSAccess isn't an enterprise solution but if the code's written correctly, I believe you can make it act like an enterprise solution.

I'm attaching a program which automatically updates/creates DSN's for users on their machine from SQL Server (assuming that the user's login has permissions to do this.) You will need to delete the current dbo_XXXX linked tables in the attached Mdb and add in 1 linked table to the database you want a DSN created for. This makes it easy to create DSN's for other users, making sure the DSN name is the same as all you need to do is open the MDB and the DSN is created. It's not a fancy application but it does do the job.

I hope any of this helps.|||Hi hmscott and pkstormy,
hmscott you were spot on. The lookup table did not have any primary key. Yesterday late night i was trying to get this running and by chance hit upon this solution and in the morning saw your reply. Tks for the "deleted" reference, it is a good thing to keep in mind.

pkstormy, tks for all the ideas. I will record them and use them for future troubleshooting. As for the database, tks a lot for that too. I will definately go through it and try to incorporate those suggestions.

Initially the frontend-backend was Access but the functionality in my database outgrew Access so we decided to move to SQL Server. The emphasis now is move all the 4-5 systems that we have in the organization to SQL Server. Once that is done, then upgrading the frontend, based on all the suggestions, is on the agenda. Thanks for the help guys. Much appreciated.

Cheers.
Prathmesh