Alternative to DoCmd.RunSQL method

[Barbacuca]

Hello,

I'm trying to use VBA to implement a routine to automate queries and updates which must happen in sequence. Given that I am but a beginner in VBA coding, I'm struggling to make this work.

Below you'll find the code where I use DoCmd.RunSQL several times. I'd rather declare the SQL statements as Strings and then run them, but I don't know how to do it. Further this code below only works up to a point and then throws and error about misuse of the SELECT statement...

Thanks for your help!

Barbacuca

#### CODE ####

' *** IMPORT NEW CMTO ***
'
' This routine perfomrs 5 actions:
' 1) Deletes current content in CMTO table (qry_deleteCMTOrecords)
' 2) Imports table CMTO_ST.xls (DoCmd.TransferSpreadsheet)
' 3) Deletes content in lutbl_Identifier (qry_deleteIdentifier)
' 4) Finds all unique new identifiers in identifier-field in CMTO (qry_findAllPossibleIdentifier)
' 5) Appends new identifiers to lutbl_Identifier (qry_appendNewIdentifier)


Private Sub ImportNewCMTO_Click()
On Error GoTo ImportNewCMTO_Click_Err

' Deletes old CMTO contents
DoCmd.RunSQL "DELETE CMTO.*" _
& "FROM CMTO;", -1

' Imports CMTO
DoCmd.TransferSpreadsheet acImport, 8, "CMTO", "" _
& "W:\Project\Controls\" _
& "Estimate\System\Sources\CMTO_ST.xls", True, ""

' Deletes old identifier contents
DoCmd.RunSQL "DELETE lutbl_Identifier.*" _
& "FROM lutbl_Identifier;"

' Finds new identifiers
DoCmd.RunSQL "SELECT First(CMTO.identifier) AS [identifier Field], Count(CMTO.identifier) AS NumberOfDups" _
& "FROM CMTO" _
& "GROUP BY CMTO.identifier" _
& "HAVING (((Count(CMTO.identifier))>1));"

' Appends new identifiers to Identifier table
DoCmd.RunSQL "INSERT INTO lutbl_Identifier" _
& "SELECT qry_findAllPossibleIdentifiers.*" _
& "FROM qry_findAllPossibleIdentifiers;"

ImportNewCMTO_Click_Exit:
Exit Sub
ImportNewCMTO_Click_Err:
MsgBox Error$
Resume ImportNewCMTO_Click_Exit
End Sub

 

[namliam]

StrSQL = ""
StrSQL = StrSql & " Select... "
StrSQL = StrSql & " From ... "
StrSQL = StrSql & " Where "
etc...
Currentdb.Execute strSQL

 

[ajetrumpet]

to declare statements as strings, use this type of code:

dim strSQL as string

strSQL = "DELETE * FROM mytable"
   docmd.runsql strSQL

Note though, that SQL that is compiled on the fly in VBA can only be used to execute action queries, not SELECT queries. Take a look at "action queries" in the help menu to get a handle on this.

You may also use:

Currentdb.execute

instead of the runsql method. With that code, you do not have to set your warnings to FALSE to avoid the action queries message dialog that pops up everytime.

 

[DCrake]

also it may be useful to insert a DoEvents between the action queries to allow access to finish one process beforre attempting another.

David

 

[Barbacuca]

Thanks for you input lads, but I'm still finding problems. The following error message still comes up:

"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

I wonder if it has to do with the fact that the SQL statement points to the field name [identifier] under the variable findNewIdent

Dim db As Database
Dim rs As Recordset
Dim findNewIdent As String
 
' Finds new identifiers
 
    findNewIdent = "SELECT First(CMTO.identifier) AS [identifier]," _
                & "Count(CMTO.identifier) AS NumberOfDups" _
                & "FROM CMTO" _
                & "GROUP BY CMTO.identifier" _
                & "HAVING (((Count(CMTO.identifier))>1));"
 
Set db = CurrentDb
Set rs = db.OpenRecordset(findNewIdent)
 
    ' Appends new identifiers to Identifier table
    DoCmd.RunSQL "INSERT INTO lutbl_Identifier" _
                & "SELECT qry_findAllPossibleIdentifiers.*" _
                & "FROM qry_findAllPossibleIdentifiers;"

I'm not sure I'm using " Set " correctly either....

 

[namliam]

Try checking in detail your code vs the example provided by me, NOTICE in particular the spaces in my example and how you are lacking those same spaces!

 

[namliam]

Also, I will strongly suggest you abandon the line continuation non-sense... Instead again use my method of appending... Much more flexible, much more readable and as a result much more maintainable...