Truncating SQL Server table from Access

[lbrady]

Hi,
I'm not an Access person, so I apologize for my ignorance.

I have a customer who is using MS Access to import data into SQL Server. He is first deleting all the data from the SQL Server table. It is this delete that is taking an unacceptable amount of time. I told him (being a SQL Server person) that TRUNCATE is much faster and more efficient than DELETE, since the end result is to clear the table completely.

Is there a way to perform a TRUNCATE against a SQL Server table linked table in Access??

Or is there a more efficient way to remove the SQL data?



If this is not the appropriate forum for this, please let me know and I will move it as directed.

TIA!

[June7]

Never heard of TRUNCATE. Delete then rebuild the table? But I doubt could do that either from the Access side. Sorry.

[rpeare]

This is a SQL problem not an Access problem. If he's importing data you give him he should be using the DROP TABLE, CREATE TABLE procedures in SQL. If the table you're supplying is large every time you delete a record or add or modify a record an accompanying record is created in a SQL log file and if your customer has not set an upper bound to the log file it will get huge very quickly and can really cause some problems. With the DROP TABLE the table is deleted and none of these individual records are generated.

If he creates an SSIS job to drop and create the table he can schedule the job through SQL to run on a specific day or time or he can manually kick it off.

[Tim van Steenbergen]

Here is the solution:
http://bytes.com/topic/access/answer...3-truncate-vba