Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
| Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 6th, 2004, 06:03 AM
Registered User
 
Join Date: Dec 2004
Location: London, , United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Clearing all records in an .mdb table from VB6

I have been trying for months to find a neat way to clear all records from a table in a access type .mdb database through my VB6 app. I am using the table "pullList" as a temp means to sort records and need to clear it before each new operation.

My form has an ADO object called "datPull" linked to the table

My only successful method is to create a loop and delete each record in turn but this can cause errors if duplicate records have got into the table or the table is empty.

Private Sub ClearPull()

    p$ = "Select Pulllist.* from Pulllist"
    datPull.RecordSource = p$
    datPull.CommandType = adCmdText
    datPull.Refresh
    'datPull.Recordset.MoveFirst
    While Not datPull.Recordset.EOF
        datPull.Recordset.Delete
        datPull.Recordset.MoveNext
    Wend
End Sub

- if I try to use the following SQL command it successfully deletes the records but returns an error in VB6 "Operation not allowed when the object is closed"


The code I am trying is

p$ = "Delete *.* from PullList"

datPull.RecordSource = p$
datPull.CommandType = adCmdText
datPull.Refresh

The error occurs on executing the last line

Any help gratefully accepted!

Rod



 
Old December 6th, 2004, 11:10 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there..

do "DELETE * FROM PullList" directly to the database.. dont use a command or a recordset.. that will clean the table...

HTH

Gonzalo
 
Old December 7th, 2004, 05:12 AM
Registered User
 
Join Date: Dec 2004
Location: London, , United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That sounds good - how do I send the command direct - must I open the database first, and set up a connection string? Can you give me some code pointers?

Thanks Rod

 
Old December 7th, 2004, 09:36 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

are you using ado or dao???

HTH

Gonzalo
 
Old December 9th, 2004, 01:59 PM
Registered User
 
Join Date: Dec 2004
Location: London, , United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am using the adodc object in VB6 to link to a ver7.0 mdb created using the VisData addin. I have a number of adodc objects in my program forms, each linked to the tables in the database. The "pulllist" table is the only one I use as a temp table - does that help? R

 
Old December 9th, 2004, 03:23 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

yes.. that help..
but to do a direct execute in the database you will have to forget about adodc and read something about connection object in msdn...

that way you can execute querys directly into the database..

the connection needs a connection string (that you have b/c you have to use it in every adodc) and then you do an open, after that do connection.execute your query and that is...

HTH

Gonzalo




Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create database (.mdb) file in vb6 code mrleokarthik Pro VB 6 13 August 7th, 2009 11:39 AM
Open mde or mdb access file from vb6 gorgo Pro VB 6 1 February 20th, 2006 01:39 AM
Passwrd protected mdb file from Crystl Rpt in vb6 Kaustav Pro VB Databases 1 October 24th, 2005 08:08 PM
Using VB6 SP2 to Open Access 2003 .mdb DWSTERRETT VB Databases Basics 1 June 13th, 2005 08:24 AM
copy table from one access .mdb to another jimsparks Classic ASP Databases 2 July 26th, 2003 05:22 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.