Linked Table Blocks Truncate Operation (1 Viewer)

[WayneRyan]

I have an Access Front-End with one SQL Server table linked.

There is a stored procedure that performs two operations:

1) Truncate Table ThatTable
2) Insert Into ThatTable Select * From ...

If the Access front-end is open, the process that presides over the linked
table "blocks" the stored procedure's Truncate operation. The Truncate
Table operation never completes.

If the Truncate Table is replaced with "Delete From ThatTable", then all
is well.

Anyone have any ideas on this?

Thanks,
Wayne

 

[Banana]

Just to be sure, are you saying you can truncate the table only if Access isn't open at all?

I'm aware of limitation upon truncation such as having a foreign key constraint or so that would prevent it from being easily truncated; it's discussed in BOL.

Furthermore, I had a odd problem with truncation and if my memory serves, I ended up doing DELETE FROM...

 

[WayneRyan]

Banana,

Yes, if Access is open at all, that process blocks the process that issues
the Truncate. Enterprise Manager shows it.

The "Delete From ..." syntax works fine though.

This contention for the table just seems funny.

Wayne

 

[Banana]

I quite agree. It was bizarre.

But to be 100% sure. Does Access still block truncation even if the form containing the table in question isn't open at all? At least I remember when I was dealing with that problem myself, it only happens if the subform is opened against the table I was trying to truncate.

 

[WayneRyan]

Banana,

The Access front-end just has to be open.

It doesn't interfere with the "Delete From ...".

Oh well, live and learn.

Wayne

 

[Banana]

Interesting. Not quite same situation as was with my which only required that the subform in question be closed.

Maybe, are you holding a persistent connection?

 

[WayneRyan]

Banana,

No persistent connection.

It's just weird as to why the Truncate command (which has no administrative "overhead") doesn't work at all, whereas the
"Delete From ..." (which has many more restrictions) works fine.

Sometimes I just want to know how these things think.

Thanks,
Wayne

 

[Banana]

Very curious.

I'm grasping for straws here, but I wonder if it's related to the table being linked? That is, if that table in question is removed from the Access, will the command work then?

Even if that was what was needed, I don't see it as a practical solution. I really, really wish I knew why TRUNCATE TABLE would not work in your situation as it hasn't in my situation.

 

[WayneRyan]

Banana,

Sorry, but I had to get back to work in order to see this, it's not really
even my app.

The Linked Table is the RowSource for a Listbox on a form. Whenever the
form is open, the Truncate is blocked.

If Access is open (but NO form), all is well.

Just a quirk I guess.

Thanks again,
Wayne

 

[Banana]

Yes, I do think it's definitely a quirk or even a bug but now it fits the pattern I was expecting: if you have a form(s) open that has a recordsource or rowsource binding that table in question, then Truncate Table just won't work. I have to dig up my old project but I *think* I worked around that by setting the subform's SourceObject (or was it recordsource?) to ZLS before executing the query then restoring the the property.

 

[WayneRyan]

Banana,

Thanks again.

The table is bound to a ListBox. During execution of the SP I agree that
setting the ListBox's RowSource to a ZLS should work fine.

Wayne