Dis/re-engage Form's bound table? (1 Viewer)

[gblack]

What I want to do is run an action query (i.e. make table query) using VBA from an OnClick event procedure to overwrite a table.

The kicker is, the button I am clicking is on a form that is bound to the table I am trying to overwrite.

Is there any way to disengage the form's bound table, while I am running this make table query, then rebind the Form to the table, once the make table query has completed, so I don't get an exclusive user error?

So far all I have been able to do is use the button to close the form and open another form then run the make table from the new opened form... then close that form and open the previous form based on the newly made table... seems very clunky.

I appreciate any insight,
Gary

 

[pbaldy]

You can try setting the record source property to "". Don't know if bound controls would then error. It would generally be more efficient to empty and repopulate the table than to delete and recreate it.

 

[gblack]

Thanks I'll try that...

With what I am doing, it takes a lot longer to delete the data from the table and append the new data to the table than to just copy over the table with the new data.

I am pulling in data from oracle, using a passthrough query, I then use a make-table query based on the passthrough, which overwrites the old table.

The database resides on a server (on a very slow network) and so every added step in the process slows things way down. Deleting hundreds of thousands of records from a table, in this environment, takes forever...

It's just easier and quicker to rewrite the table... unless I am not understanding what you meant by"empty and repopulate" I am assuming this means delete and append...no?

Thanks again for the tip, I'll give that a shot and report back...

 

[pbaldy]

Yes, I meant to delete records and append new ones.