Delete large numbers of records from Linked Table

[jambai]

Hi,

How to delete large numbers (100,000 - 1,000,000) records from linked table.

I am trying to delete from MS Access Forms.

Thanks
Jambai

 

[Remou]

A delete query?

 

[jambai]

yes

I am having more that 200,000 records.

Thanks

 

[Remou]

Ok, I'll rephase that, why not use a delete query?

 

[PHV]

I even say more:
Why not use a pass-through delete query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jambai]

I tried, there is no response..

The query is keep on running.The application hangs up.

The delete query works fine for low records number in the table.

Thanks
Jambai

 

[PHV]

This is the reason why I talked about a pass-through query ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jambai]

PHV,

Can you tell me how to write a one.
The below one failed for me.

tempDefTable is from SQLSERVER and the name is NTSQLSERVER
strDelDefTemp = "DELETE OPENQUERY (NTSQLSERVER,'DELETE FROM tempDefTable')"
DoCmd.RunSQL strDelDefTemp

dbo_temDefTable is linked table name.

Thanks
jambai

 

[PHV]

Why not simply execute a SP droping and recreating the table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jambai]

Multiple users is going to use this application.So I think droping and recreating will throw some table permission problem..

Please help me, to achieve this using pass-through.

Dim qdfPassThrough As DAO.QueryDef
Dim strConnect As String
strConnect = "Driver=SQL SERVER;SERVER = NTSQLSERVER;Database=TEST;UID=mlp;password=''"
qdfPassThrough.Connect = "ODBC;" & strConnect

Please validate the above

Thanks
Jambai