Problems Executing SQL Statements 
Author Message
 Problems Executing SQL Statements

Is there a trick to executing SQL statements in VB?  I have no trouble
executing SELECT statements, but when it comes to doing anything else with
VB & SQL, I bomb.

Here is the code I am trying to use to delete all the records in a table:

Dim db As Database
Dim rs As Recordset

Dim SQLstr As String

Set db = data1.Database
Set rs = db.OpenRecordset("myTable")

SQLstr = "DELETE mytable.* FROM mytable;"

Set rs = db.OpenRecordset(SQLstr)

Set data1 = rs

This code produces an invalid operation error (3219).

As always - any help is greatly appreciated!

--
Jeff Miller
El Paso, TX



Sun, 11 Nov 2001 03:00:00 GMT  
 Problems Executing SQL Statements
First of all, the Delete statement does not return any records, second the
Delete statement is incorrect.  Try it like this:

Dim db As Database
Dim rs As Recordset

Dim SQLstr As String

Set db = data1.Database

SQLstr = "DELETE FROM mytable"
db.Execute  SQLstr

Set rs = db.OpenRecordset("myTable")

Set data1 = rs

You have to execute the Delete statement first and then get your recordset.

Hope this helps,
Mike Reck
Ameritech Data Networking Solutions

Quote:

>Is there a trick to executing SQL statements in VB?  I have no trouble
>executing SELECT statements, but when it comes to doing anything else with
>VB & SQL, I bomb.

>Here is the code I am trying to use to delete all the records in a table:

>Dim db As Database
>Dim rs As Recordset

>Dim SQLstr As String

>Set db = data1.Database
>Set rs = db.OpenRecordset("myTable")

>SQLstr = "DELETE mytable.* FROM mytable;"

>Set rs = db.OpenRecordset(SQLstr)

>Set data1 = rs

>This code produces an invalid operation error (3219).

>As always - any help is greatly appreciated!

>--
>Jeff Miller
>El Paso, TX



Sat, 24 Nov 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. SQL statement error (Problem when more than once execute)

2. ADO problem with multiple SQL statements using execute method

3. Write One SQL statement execute in Oracle and SQL Server

4. Can't execute a user define function in an SQL statement

5. errors different when executing SQL statements via recordset or connection object

6. Executing a v.long SQL script with multiple go statements

7. How to execute a long SQL statement?

8. Executing UPDATE SQL statements in DAO 3.5 and VB5

9. How to execute an SQL statement on a datacontrol

10. How to execute 2 SQL statements in the user-defined query

11. Bitwise ANDs ORs in SQL execute statement

12. Help: Execute statement (SQL)

 

 
Powered by phpBB® Forum Software