Trying to delete current Recordset from database! 2

[jasonatsmtc]

Hi,

What i am doing is filtering a table using certain specifications, i then want to take all of the records that meet these specs and delete them..
this is what I have for code right now, I dont get an error, but it does not delete anything from the database!

frmData.datRooms.Recordset.Delete
frmData.datRooms.Recordset.Filter = adFilterNone
MsgBox ("we have deleted your reservation!&quot

Thanks in advance!
Jason

 

[Terpsfan]

You're much better off using a query to accomplish this. You can either use an inline sql statement or a query within the database.

DELETE *
FROM tablename
WHERE wherecondition;

Or inline sql
set reference to microsoft activex data objects

Dim objConn as adodb.connection
Command1_Click()
Set objConn = New adodb.connection
objConn.ConnectionString = "enter your oledb provider info"
objConn.open
objConn.Execute "DELETE * FROM tablename WHERE wherecondition"

 

[jasonatsmtc]

Hi, thanks for the reply,

Here is the code that I am trying to use...

Deletestring = "DELETE FROM " & CurrentRoom & " WHERE [Customer Number] = " & CustomerNumber
frmData.datRooms.RecordSource = Deletestring
MsgBox ("we have deleted your reservation!&quot

It all goes through fine, but it still does not delete the records!
I tried the code from your first option, but that did not work, The second option, i dont understand how it works, what is "objConn"? also what information do i have to change to make it work for me? I know these are stupid questions, but I am very inexperianced when it comes to SQL!

Jason

 

[PraveenMenon]

Omega36's my man.....

I think it is always good to use the execute property to issue Data Modification queries to a database. I wud like to hear responses from others on this view...

My question is,
is there is a need to create a recordset object and access its properties or is it better to issue DMLs directly using the Connection Objects' execute property? All the Best
Praveen Menon
pcmin@rediffmail.com

 

[johnwm]

PraveenMenon,
You're a bit off-topic here! If you want a theoretical discussion - start your own thread - this poor guy is struggling!

jasonatsmtc,

In your code:
Deletestring = "DELETE FROM " & CurrentRoom & " WHERE [Customer Number] = " & CustomerNumber
frmData.datRooms.RecordSource = Deletestring
MsgBox ("we have deleted your reservation!&quot

you seem to have missed one or two bits.

Simplified syntax of delete is:
DELETE
[FROM] tablename
[WHERE] condition

For full details see SQL booksonline -if you don't have it installed with your SQL then get it from :

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

In omega36's second code:
objConn is defined in the Dim statement as :
Dim objConn as adodb.connection

You may need to check out ADO in your VBHelp to see how to modify for your exact needs.

There is a lot of MSDN stuff on ADO. Start from here:

http://msdn.microsoft.com/library/d...270/htm/mdmscadocodeexamplesinvisualbasic.asp

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[jasonatsmtc]

Thanks a lot everyone!
I was able to get it to work with this code:

Set objConn = New adodb.Connection
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\School\Applications in Software\hotel\data\thebigone.mdb;Persist Security Info=False"
objConn.Open
Deletestring = "DELETE FROM " & CurrentRoom & " WHERE [Customer Number] = " & CustomerNumber
objConn.Execute Deletestring
MsgBox ("we have deleted your reservation!&quot

 

[PraveenMenon]

am sory john for that... All the Best
Praveen Menon
pcmin@rediffmail.com

 

[johnwm]

PraveenMenon:
No probs - I may have been in grouch mode!
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'