By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

problem in executing a query in vb 6.0

100+
P: 198
Hi
How can i execute this query in vb 6.0
i use this query in ms-access and works but it shows the error that select statement can't be executed in vb 6.0
please give me help
my query is
SELECT Yp1EOp, Count(*) AS Entry_count
FROM master
GROUP BY master.Yp1EOp
ORDER BY master.Yp1EOp;

please help me
thanks in advance
varinder
Apr 11 '08 #1
Share this Question
Share on Google+
9 Replies


QVeen72
Expert 100+
P: 1,445
Hi,

If you are running the query from VB6, the Semicolon at the end may err.. so remove that..

Try this :

Expand|Select|Wrap|Line Numbers
  1. SELECT Yp1EOp, Count(*) AS Entry_count
  2. FROM master
  3. GROUP BY Yp1EOp
  4. ORDER BY Yp1EOp
  5.  
Regards
Veena
Apr 11 '08 #2

100+
P: 198
Hi
i am using this query
squery = " select Yp1EOp, count(*) from " & txttablename & " IN '" & txtchildMDBF & " ' Group By GROUP BY Yp1EOp ORDER BY Yp1EOp"
dbmaster.Execute (squery)

the error is cannot execute select query

where txttablename contain name of table and txtchildmdbf contain path for txttablename



Hi,

If you are running the query from VB6, the Semicolon at the end may err.. so remove that..

Try this :

Expand|Select|Wrap|Line Numbers
  1. SELECT Yp1EOp, Count(*) AS Entry_count
  2. FROM master
  3. GROUP BY Yp1EOp
  4. ORDER BY Yp1EOp
  5.  
Regards
Veena
Apr 11 '08 #3

QVeen72
Expert 100+
P: 1,445
Hi,

Well.. I had given you Select-Into Query..in one of my previous posts..
in your query you are not using "INTO"....
anyways check this :

Expand|Select|Wrap|Line Numbers
  1. squery = " select Yp1EOp, count(*) As ECount INTO  " & txtchildMDBF _
  2.     & " From " & txttablename & "  Group By GROUP BY Yp1EOp ORDER BY Yp1EOp"
  3. dbmaster.Execute (squery)
  4.  
Regards
Veena
Apr 11 '08 #4

100+
P: 198
Thanks Very very much

it is working

with best regards
varinder



Hi,

Well.. I had given you Select-Into Query..in one of my previous posts..
in your query you are not using "INTO"....
anyways check this :

Expand|Select|Wrap|Line Numbers
  1. squery = " select Yp1EOp, count(*) As ECount INTO  " & txtchildMDBF _
  2.     & " From " & txttablename & "  Group By GROUP BY Yp1EOp ORDER BY Yp1EOp"
  3. dbmaster.Execute (squery)
  4.  
Regards
Veena
Apr 11 '08 #5

QVeen72
Expert 100+
P: 1,445
Hi,

You are WelCome..

Regards
Veena
Apr 11 '08 #6

100+
P: 198
Hi Veena
actually i want to confirm can i transfer this table data into excel after this query which you gave me . I mean i want to show this table data into excelsheet

i use this method but it shows error "invalid operation" on bold line

squery = " select Yp1EOp, count(*) As ECount INTO " & txtchildMDBF _
& " From " & txttablename & " GROUP BY Yp1EOp ORDER BY Yp1EOp"
set rsmaster= dbmaster.openrecordset (squery) // INVALID OPERATION

is there any way to transfer this output into excelsheet after this query


pleassssssss help

thanks in advance




Hi,

You are WelCome..

Regards
Veena
Apr 11 '08 #7

QVeen72
Expert 100+
P: 1,445
Hi,

In that case, Create a Macro in Access, and run the Macro..
First Create a Query:

Select Field1, Count(*) From MyTable Group By Field1 Order By Field1

Run and save the Query say "MyQuery"

Now create a New Macro.. set these properties:
Action =OutputTo

ObjectType= Query
ObjectName = MyQuery
OutputFormat Type :Microsoft Excel (*.xls)
OutPut File : C:\MyExc.xls
AutoRun = No

and Save the Macro.. say "Macro1"
Now Call macro from VBCode:

Expand|Select|Wrap|Line Numbers
  1. Dim objAccess As New Access.Application
  2. With objAccess
  3.     .OpenCurrentDatabase "C:\MyDb.mdb"
  4.     .DoCmd.RunMacro "Macro1", 1
  5.     .CloseCurrentDatabase
  6. End With
  7. Set objAccess = Nothing
  8.  

So all the Records of the Query are exported to Excel : C:\MyExc.xls


Regards
Veena
Apr 11 '08 #8

100+
P: 198
Thnaks very much for helping
with best regards
varinder



Hi,

In that case, Create a Macro in Access, and run the Macro..
First Create a Query:

Select Field1, Count(*) From MyTable Group By Field1 Order By Field1

Run and save the Query say "MyQuery"

Now create a New Macro.. set these properties:
Action =OutputTo

ObjectType= Query
ObjectName = MyQuery
OutputFormat Type :Microsoft Excel (*.xls)
OutPut File : C:\MyExc.xls
AutoRun = No

and Save the Macro.. say "Macro1"
Now Call macro from VBCode:

Expand|Select|Wrap|Line Numbers
  1. Dim objAccess As New Access.Application
  2. With objAccess
  3.     .OpenCurrentDatabase "C:\MyDb.mdb"
  4.     .DoCmd.RunMacro "Macro1", 1
  5.     .CloseCurrentDatabase
  6. End With
  7. Set objAccess = Nothing
  8.  

So all the Records of the Query are exported to Excel : C:\MyExc.xls


Regards
Veena
Apr 11 '08 #9

100+
P: 198
Hi
i am facing a little bit of probleum
actually i want to execute more than one macro but the output should be
in one file
the execution is ok but when i execute macro2 then output of macro1 is deleted
is there any way so that the output of macro1 is also remain with the output of macro2

please help meeeeeeeeeee
Apr 11 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.