Spot the SQL difference 1

[JR2913]

Apart from the slightly different order of field specifications, can some kind individual tell me what the difference is between these two SQL statements?

STATEMENT 1

SELECT GESTUCENTRES.SetId, GESTUCENTRES.StudentId, GESTUCENTRES.CandId, GESTUCENTRES.CentreId, GESTUCENTRES.CandNumber, GESTUCENTRES.CandStatus, GESTUCENTRES.CandQualifier, GESTUCENTRES.SecCentreId, GESTUCENTRES.SecCandNumber, GESTUCENTRES.DocGroup, GESTUCENTRES.CChanged, GESTUCENTRES.CReturned, GESTUCENTRES.CEmptyReturn, GESTUCENTRES.CFlaggedDelete FROM GESTUCENTRES WHERE (((GESTUCENTRES.CandNumber) In (SELECT [CandNumber] FROM [GESTUCENTRES] As Tmp GROUP BY [CandNumber] HAVING Count(*)>1 ))) ORDER BY GESTUCENTRES.CandNumber;

STATEMENT 2

SELECT GESTUCENTRES.CandNumber, GESTUCENTRES.SetId, GESTUCENTRES.StudentId, GESTUCENTRES.CandId, GESTUCENTRES.CentreId, GESTUCENTRES.CandStatus, GESTUCENTRES.CandQualifier, GESTUCENTRES.SecCentreId, GESTUCENTRES.SecCandNumber, GESTUCENTRES.DocGroup, GESTUCENTRES.CChanged, GESTUCENTRES.CReturned, GESTUCENTRES.CEmptyReturn, GESTUCENTRES.CFlaggedDelete FROM GESTUCENTRES WHERE (((GESTUCENTRES.CandNumber) In (SELECT [CandNumber] FROM [GESTUCENTRES] As Tmp GROUP BY [CandNumber] HAVING Count(*)>1 ))) ORDER BY GESTUCENTRES.CandNumber;

Statement 2 was generated using a query generated by the query wizard for finding duplicate records and works.

Statement 1 was generated in VBA from tables and fields selected by the user using combo boxes on a form. When the code is run it gets as far as the VBA instruction

DoCmd RunSQL SQL

where SQL = Statement 1, but falls over with Run-time error 2342 "A RunSQL action requires an argument consisting of an SQL statement".

Any ideas, please, would be much appreciated.

John R

 

[KenReay]

Hi

You cannot run a select query with DoCmd.RunSQL, you can run only action queries

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JR2913]

Thanks Ken

Not quite the answer I wanted, though! Is there a way of doing what I want to do (i.e. finding duplicate records) using VBA, allowing users to search any field from any table (bearing in mind there are over 600 tables in the database!)?


John R

 

[KenReay]

Hi

Yes,

You can open a recordset

in DAO

Dim Db as DAO.Database
Dim Rs as DAO.Recordset
Set db = CurrentDb()
set Rs = db.OpemRecordset(strSQL)

assuming yout strSQL is as you said

you can the examine Rs.Recordcount to see how many rows returned

iterate through the rows..etc

you can do similar things with ADO

depends on which version of Access you are using A97 uses DAO by default, A2K on uses ADO by default, but so long as you set the appropriate reference to the DAO or ADO library you can use either

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

OOPS, must get my keybord fixed

set Rs = db.OpemRecordset(strSQL)

should be

set Rs = db.OpenRecordset(strSQL)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JR2913]

Many thanks Ken - I'll give this a try.

I have been working on a version of my original idea that generates a new table and allows the user to view the contents. This works.

The only thing I haven't managed to do is completely delete the temporary table; have only emptied the existing fields - it must be deleted as the field contents will be different each time the search is carried out. I'm sure it's straightforward but

DoCmd.DeleteObject acTable, "tblDuplicates"

doesn't do the job.

John R

 

[KenReay]

Hi

in SQl

"DELETE * FROM MyTable;"

will emplty the table but still retain the table definition

"DROP MyTable;"

will delete the whole table structure and data

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JR2913]

Fantastic Ken - thanks so much - have a star.

John R