can't update subform tied to linked spreadsheet

[jender624]

Hi all!

I've got a table that I'm linking to an excel spreadsheet. I set that table as the recordsource of a subform through code, and then try to edit the data on the subform. I keep getting "This Recordset is not updatable."

Anybody have an idea why I can't edit the data on the subform?

Thanks!

jender624

 

[vbajock]

Spreadsheets cannot be used as multi-user tables. If more than one person tries to access the table, you'll get this message. If that is not the case, then the subform is based on a query that is returning non updateable records.

 

[jender624]

Thanks for the reply!

This will only be a single user process, so it shouldn't be a problem. As for your second point, there is no query that is feeding the subform. It is simply the table itself. I understand that certain queries return sets of data that are not editable, but I thought that setting a recordsource to a table, not a query, wouldn't cause a problem like this. The only thing I can think of is that the problem has something to do with either the fact that it's a linked table, or that it's a table linked to an excel file.

I'm pretty confused, anybody have an idea?

Thanks,

jender624

 

[vbajock]

Open the table manually and see if you can type in a value. If you can, the problem is on your form somewhere. Also, you can't have the form open in excel on your own machine at the same time you have it open in Access. Excel will treat this as multiuser.

 

[jender624]

I opened the table, and found that I am able to edit a value. However, I noticed that when I did edit the data through the table, closed the db, and opened the excel file through excel I kept getting a reference error, and couldn't open the excel file through excel from then on. I was using a copy for testing, so it wasn't a big deal. This may be a side issue, I'm not sure yet.

I looked at both the properties of the main form and subform, and don't see anything out of the ordinary, unless there's something i'm missing.

Is there a way to attach a file to a post in this forum? I don't see one, but maybe I'm blind.

Thanks,

jender624

 

[vbajock]

Sounds like your excel file is having problems. Is there VBA code attached to the Spreadsheet? Have you checked for viruses?

 

[jender624]

There is no vba code in the spreadsheet, and my computer is free of viruses (I ran a scan, just in case). If I can't get this to work, I think I have a workaround strategy that I can try. In the meantime, if there are any other ideas, please share.

Thanks,

jender624