General discussion

Locked

Running an ADO query in Access 2000

By M_Ozog ·
I have been trying to execute an ADO query in my Access 2000 database. The command is supposed to create a table and populate it based on varying criteria. The reason I don't just use a standard Access query is because the criteria varies but it is in a very similar format. If I capture the command that was executed and put it into a new query it runs fine.

The problem is that the table is made but no data is entered. I have 0 records when there should be 24.

The commands used are:
Set Cmmd = New ADODB.Command
Set Cmmd.ActiveConnection = Cnn
Cmmd.CommandText = strSQL
Cmmd.CommandType = adCmdText
Cmmd.CommandTimeout = 45
Cmmd.Execute

I am new to ADO, so any assistance would be helpful.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Running an ADO query in Access 2000

by SteveD In reply to Running an ADO query in A ...

Not sure if this will help, but I would try a more direct approach, like this:

Cnn.Execute (strSQL)

I'm assuming that Cnn has been set to a valid ADO connection object that is in fact connected to your current database.

I hope this helps.
Steve Diamond

Collapse -

Running an ADO query in Access 2000

by M_Ozog In reply to Running an ADO query in A ...

Thanks for taking the time to help but this didn't work. I get the same results.

There doesn't seem to be a difference if I use the .Execute from the connection object or the command object.

Collapse -

Running an ADO query in Access 2000

by robert_a_mocarski In reply to Running an ADO query in A ...

Try splitting the creation of the table and the populating of it into two commands. Execute each separately. I have run into similar problems in the past with Access. It seems to get confused when you ask it to do much at one time.

Collapse -

Running an ADO query in Access 2000

by M_Ozog In reply to Running an ADO query in A ...

This helped. Thanks.

There was also another problem that I had to find out on my own. I used date fields in the WHERE clause and forgot to put the #'s. The query worked fine from the query builder but not as an ADO statement.

Collapse -

Running an ADO query in Access 2000

by spankil In reply to Running an ADO query in A ...

Hi,
can u please show me your SQL statement? if I am not wrong than you r creating a table and then Inserting data using Insert into Statement. If there is a where condition including with Like Operator then
In Access it will run fine WITH wild card '*'
but when you fire the Same Query from front end then you have to replace '*' with '%'

e.g
In Access
Select * from TableName where fieldname like 'C*'

will fetch all the records beginig with C in FieldName Field.

But in Case of Front end the Query becomes
Select * from TableName where fieldname like 'C%'

Hope this might help you.
All the best

Collapse -

Running an ADO query in Access 2000

by M_Ozog In reply to Running an ADO query in A ...

I don't use the LIKE *. When I refer to variables, I mean:
Patient.City = 'Detroit'
or
Patient.County='Wayne'
Where Detroit and Wayne are chosen from seperate combo boxes.

Collapse -

Running an ADO query in Access 2000

by M_Ozog In reply to Running an ADO query in A ...

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums