Urgent ..pls help.."Delete all records"

[bnath001]

I am trying to delete all records from MS Access database.
I am using VB 6.0 code and ADO's to accomplish this.

I keep getting the following error. Runtime error 3704.
Operation is not allowed when the object is closed.

I am using the following code:
Dim adoCon As ADODB.Connection
Dim adoRst As ADODB.Recordset

sSQL = "select * from tbl_XML_Detail"
Set adoCon = New ADODB.Connection
Set adoRst = New ADODB.Recordset

adoCon.ConnectionString = "Schema"
adoCon.CursorLocation = adUseClient
adoCon.Open

Set adoRst.ActiveConnection = adoCon

adoRst.Open sSQL, , adOpenForwardOnly, adLockOptimistic, adCmdText
adoRst.Delete adAffectAll

Pls help..

 

[Ruairi]

It doesnt look like you need to incur the extra overhead of pulling all this data into a recordset only to delete it. How about sending a text command to the DB.

    Dim cm As ADODB.Command

    Set cm = New ADODB.Command
    cm.CommandType = adCmdText
    Set cm.ActiveConnection = adoCon
    cm.CommandText = "delete from tbl_XML_Detail" 
    cm.Execute

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[bnath001]

Thank you so much.

Right now using "ODBC data Datasource Administrator" which is available in control panel, I have created a Data source called "Schema" which is connected to Access 2000 database.

If I have to send this application to users's, I believe that they should also create a DSN called "Schema".
Instead how can I write the connection string and include it in the code itself.

the access database is in "C:\" directory.
could you pls help me?

 

[Ruairi]

You could change App.Path to "C:" if you really want your DB in the root of the C: drive, but it would make more sense to just install it in the same location as your program and use App.Path

    Set m_cn = Nothing
    Set m_cn = New ADODB.Connection
    
    m_cn.CursorLocation = adUseServer
    m_cn.CommandTimeout = 60
    m_cn.ConnectionTimeout = 60
    m_cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    m_cn.ConnectionString = App.Path & "\" & "TheName.mdb"
    m_cn.Open

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[Karl Blessing]

why not do

"delete * from table"

as your query Karl Blessing aka kb244{fastHACK}

 

[bnath001]

Thanks guys.

regards
nath