469,307 Members | 1,681 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

Error Running Find Duplicates Query using ADO

171 100+
To put the below code simply, I have created a find duplicates query for a table using the Access query wizard. I have then tried to plug the Sql String (strSqlDupColl) copied from the Query Builder into VBA, using ADO I have tried to call the Query, but I get the following error : Invalid SQL statement expected; ‘DELETE’,’INSERT’,'UPDATE' or SELECT". I have no idea why this is occuring. Please help.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim cnn As ADODB.Connection
  4. Dim rst As New ADODB.Recordset
  5. Public strSubject As String
  6. Public strBody As String
  7.  
  8. Private Sub DupChk()
  9. Dim btCount As Byte
  10. Dim strSqlDupColl As String
  11. Set cnn = CurrentProject.Connection
  12. strSqlDupColl = "SELECT First(tblCollections.[CollectionID]) AS Coll, " & _
  13. "First(tblCollections.[OfficeID]) AS Off, Count(tblCollections.[CollectionID]) AS DupsNum " & _
  14. "FROM tblCollections " & _
  15. "GROUP BY tblCollections.[CollectionID], tblCollections.[OfficeID] " & _
  16. "HAVING (((Count(tblCollections.[CollectionID]))>1) AND ((Count(tblCollections.[OfficeID]))>1));"
  17. rst.Open "strSqlDupColl", cnn, adOpenDynamic, adLockOptimistic
  18.     With rst
  19.         If rst.BOF And rst.EOF And btCount <> 1 Then
  20.             ImportUpd
  21.                 btCount = 1
  22.         Else
  23.         strSubject = "Duplicate in tblCollections"
  24.          SendEmail
  25.         End If
Aug 18 '09 #1
9 2533
MikeTheBike
637 Expert 512MB
@iheartvba
Hi
Without looking too far into the code, I suggest first you remove the Quote marks round the query variable, ie. this

rst.Open "strSqlDupColl", cnn, adOpenDynamic, adLockOptimistic

should be this

rst.Open strSqlDupColl, cnn, adOpenDynamic, adLockOptimistic

??

MTB
Aug 18 '09 #2
iheartvba
171 100+
sorry about that mike, that was just me trying different things to get the code to work. Originally there were no quotes around strSqlDupColl and it was still not working.

Thanks
Aug 18 '09 #3
ADezii
8,800 Expert 8TB
@iheartvba
Before you attempt anything else, remove the Quotations from the strSqlDupColl String Variable, because ADO will interpret it as a Literal String, Replace Line #17 with:
Expand|Select|Wrap|Line Numbers
  1. rst.Open strSqlDupColl, cnn, adOpenDynamic, adLockOptimistic 
Aug 18 '09 #4
ADezii
8,800 Expert 8TB
@MikeTheBike
Sorry MikeTheBike, didn't realize that I was stepping on your toes.
Aug 18 '09 #5
ADezii
8,800 Expert 8TB
@iheartvba
Can you Attach the Database, or a Subset of it?
Aug 18 '09 #6
FishVal
2,653 Expert 2GB
@iheartvba
But did you get the same error message when there was no quotes?
Aug 18 '09 #7
MikeTheBike
637 Expert 512MB
@ADezii
Hi ADezil

There does not seem to be any damage, all toes seem to be functioning OK, but thanks for the concern!

Back on topic, iheartvba, an answer to FishVals question would be of some help.

MTB
Aug 19 '09 #8
iheartvba
171 100+
Ummm, it seems to be working now. I could very well have been putting quotes around the variables, sorry guys! Thanks for your help, it is greatly appreciated.
Aug 19 '09 #9
iheartvba
171 100+
@iheartvba
Actually I was right it still isn't working when I put a query made via the find duplicates query wizard via ADO, but I have just done my own find duplicates query, so It would be ok if someone wants to close this topic (sorry I don't know how to close the topic)

Thanks every1 for your help
Aug 21 '09 #10

Post your reply

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

Similar topics

11 posts views Thread by David Berry | last post: by
reply views Thread by Morten Gulbrandsen | last post: by
3 posts views Thread by Tom Mitchell | last post: by
2 posts views Thread by Phil | last post: by
4 posts views Thread by franc sutherland | last post: by
15 posts views Thread by Lawrence Krubner | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.