Updating SQL table from MSAccess

[Fflyer]

I have an MSAccess database that is queried using the standard set of forms, queries, macros and reports. I am going to move the main table to SQL and access the database with ASP pages as these functions are going to be integrated into a website. I do however have a handful of individuals that will continue to update manually (much like an excel spreadsheet) the main table. I don't want to load SQL on their desktops and would much rather let them update the SQL table from the MSAccess application. Is that possible? What settings need to be made on the SQL table or MSAccess database?

Thank you,
Chad

 

[THoey]

Can't you just "LINK" the SQL tables to the Access DB and let them do it that way? I just tested it here. I created an ODBC reference to my SQL Server DB. Then in Access, I did a FILE -> GET EXTERNAL DATA -> LINK TABLE. I was able to open the table and view it.

Should be easy... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Fflyer]

Oh yeah, it is that easy and I can view all day the linked table. It is only when I open the table up to edit it that I get the error message (something like) "recordset is not updateable."

I think that the fact that it is a separate, different flavor and linked table says to MSAccess that this feature (editing a table) is not allowed. BUT, I do know it is possible as I have seen it work before.

Does that help explain my situation?

Thanks,
Chad

 

[RobertT687]

In my experience, in order for an SQL table to be updatable in Access, the table must have a primary key or unique index. Do either of these exist for the table(s) in question?

 

[Fflyer]

It might not BUT that doesn't mean that I can't give it one. I will try that.

Thank you,

Chad

 

[petermeachem]

Absolutely. You must have a primary key, and mention it in queries even if you don't want to use it. Make sure it is on the access grid, invisible if you wish. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm