Executing a Query 
Author Message
 Executing a Query

I'm just starting to experiment with ADO using VBA in Access 2000, and I'm
having a problem executing a simple 'Select * Into newTable' query.  The
query seems to execute (even takes a few seonds), but when I open up the new
table it is blank.  I know the connection works because there IS an empty
table.

I also know the SQL statement will generate results, I've ran it in the
query manager where it produces results.

Another thing, if I take off the criteria of the query, it works!  Which
leads me to think that is the problem  Can anyone help me to my problem,
does ADO have a way the query needs to be worded?

***************
Private Sub Command0_Click()
    Dim cmd1 As ADODB.Command

    Set cmd1 = New ADODB.Command
    cmd1.ActiveConnection = CurrentProject.Connection

    'If necessary, drop old version of temporary
    'stored procedure, or ignore resulting error
    cmd1.CommandText = "SELECT TEST2000.Field1, TEST2000.Field7,
TEST2000.Field4, TEST2000.Field6, TEST2000.Field5, TEST2000.Field3,
TEST2000.Field8, 0 AS LSize, '2' AS PR, 0 AS LR INTO pbrti1 FROM TEST2000
WHERE ( (TEST2000.[Field1] Like 'C6*' Or TEST2000.[Field1] Like 'C3*' Or
TEST2000.[Field1] Like 'C4*') AND TEST2000.Field4<>'64' AND TEST2000.Field3
Is Not Null AND TEST2000.[Field2] Is Not Null )"

cmd1.CommandType = adCmdText

On Error GoTo ErrorTrapper
cmd1.Execute
On Error GoTo 0
Set cmd1 = Nothing

End Sub

*****************
Can anyone offer any help into what I am doing wrong?



Sat, 26 Jun 2004 22:48:09 GMT
 Executing a Query

Hi Michael,

It looks like your SQL statement is fine. propbaly you
have condition in WHERE clause which does not select any
records. You can test it. Separate part of SELECT
statement (remove INSERT part) and execute statement. If
it will not return anything, but you are expecting some
one, then remove conditions one-by-one and run SQL
statement each time when you remove condition. This way
you can eliminate, and find out which condition causes
problem

Val

Quote:
>-----Original Message-----
>I'm just starting to experiment with ADO using VBA in

Access 2000, and I'm
Quote:
>having a problem executing a simple 'Select * Into

newTable' query.  The
Quote:
>query seems to execute (even takes a few seonds), but

when I open up the new
Quote:
>table it is blank.  I know the connection works because
there IS an empty
>table.

>I also know the SQL statement will generate results, I've
ran it in the
>query manager where it produces results.

>Another thing, if I take off the criteria of the query,
it works!  Which
>leads me to think that is the problem  Can anyone help me
to my problem,
>does ADO have a way the query needs to be worded?

>***************
>Private Sub Command0_Click()
>    Dim cmd1 As ADODB.Command

>    Set cmd1 = New ADODB.Command
>    cmd1.ActiveConnection = CurrentProject.Connection

>    'If necessary, drop old version of temporary
>    'stored procedure, or ignore resulting error
>    cmd1.CommandText = "SELECT TEST2000.Field1,
TEST2000.Field7,
>TEST2000.Field4, TEST2000.Field6, TEST2000.Field5,
TEST2000.Field3,
>TEST2000.Field8, 0 AS LSize, '2' AS PR, 0 AS LR INTO

pbrti1 FROM TEST2000
Quote:
>WHERE ( (TEST2000.[Field1] Like 'C6*' Or TEST2000.

[Field1] Like 'C3*' Or

- Show quoted text -

Quote:
>TEST2000.[Field1] Like 'C4*') AND TEST2000.Field4<>'64'
AND TEST2000.Field3
>Is Not Null AND TEST2000.[Field2] Is Not Null )"

>cmd1.CommandType = adCmdText

>On Error GoTo ErrorTrapper
>cmd1.Execute
>On Error GoTo 0
>Set cmd1 = Nothing

>End Sub

>*****************
>Can anyone offer any help into what I am doing wrong?

>.



Sat, 26 Jun 2004 23:29:45 GMT
 Executing a Query
Make sure your query actually returns rows; there seem to be quite a few
parameters in the WHERE clause.

Then, disable any error handlers you have, or switch VB to Break on All
Errors mode, and rerun your code. Your error handler may be shielding you
from seeing the true errors that occur.

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.



Mon, 28 Jun 2004 02:38:13 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Delay in Executing a Query in Query Analyzer with option Results in Text

2. Executing a Query on a Table or Query

3. Howto execute one query over multiple query results

4. Executing dbcc queries using osql

5. reference for syntax to execute dynamice queries

6. how can I execute a query on a remote SQL-server

7. executing access query from dts

8. Cannot execute select query error in SQL task ?

9. ODBC problem executing sql-query with join against Access-database

10. Cannot execute dynamic query in stored procedure

11. how to prompt the user to enter information when executing a query

12. to execute a query using variable


 
Powered by phpBB® Forum Software