[AccessD] Access 97 nasty behaviour of subform

thank you, Stuart,
seems you are right. must be the Excel table not closing or such.
and I think it is not editing the subform.
Sometimes a long time delay between closing and opening the mainform agin 
helps,
Now I just found that in case of the error closing and then opening the form 
fixes it.

so this works:
Open form
run code
close form
open form
close form
open form
run code
and so on

I have made sure that all recordsets are set to Nothing,
I have set the dirty property of the subform as well as the main form false,

I have set the recordsource of the subform to "" in the OnOpen and to the 
Excel table in the OnLoad.

nothing helped.

Lembit

----- Original Message ----- 
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Sunday, October 01, 2006 1:23 AM
Subject: Re: [AccessD] Access 97 nasty behaviour of subform


> Sounds as though the first instance of the subform gets a lock on the 
> Excel
> table which isn't closed when the main form is closed.
> Opening the main form creates a new instance of the subform which tries to
> get a second lock and so can only get a RO connection.
>
> Editing the sub form must be completely destroying the first instance thus
> breaking the lock and allowing the next instance to establish a RW
> connection
>
> Not sure what to suggest on how to overcome it, but hopefully this gives
> you an idea of where to look.
>
> -- 
> Stuart
>
> On 30 Sep 2006 at 20:01, Lembit Soobik wrote:
>
>> good evening everyone,
>> I have a form with a subform,
>> form is continuous and has 3 little subforms in the header.
>> form is bound to a table.
>> subforms are bund to tables (which are named spaces in an EXCEL 
>> worksheet.
>>
>> form has a button. when I click it, some code runs and copies and 
>> processes
>> data from some other excel table to Access BE tables.
>>
>> works fine so far.
>>
>> at the end of the update I want to write to the one table on the excel 
>> sheet
>> the project-number to indicate it has been processed. this works as many
>> times as I click, but only UNTIL I close the main form and open it again.
>> NOW it tells me error 3027 database or object is read-only.
>>
>> If I now close the main form
>> open the subform and change something like allow updates, THen change it
>> back to what it was and save it, THEN I can open the main form and it 
>> will
>> happily run and update the exxel sheet as often as I like.
>>
>> close the main form, open it again ERROR 3027.
>> cahnge something on subform, save, close, mainform works again for one
>> opening.
>>
>> I have now put me.allowedits = tru in the subform onLoad event
>> nochange
>> I have in my code tried both:
>> a) open the table and write to the table
>> b) write to the control on the subform
>>
>> in both cases I get the effect that after closing the main form and
>> reopening it, I get 3027 error.
>>
>> any idea what I'm missing?
>>
>> thank you
>> Lembit
>>
>> -- 
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com