Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1276
  • Last Modified:

Query string concatenation in Access SQL

I wish to concatenate two or more SQL queries on an Access database, eg in the Code snippet

I can do this on a SQL Server database but Access does not like it.  It doesnt like me using semi-colon separators either.

 How can I make it work in Access?

sSQL = " DECLARE @oldMaxItemID int " _
 
                & " SET @oldMaxItemID = ( SELECT MAX(ItemID) " _
 
                & " FROM Controls ) " _
 
                & " " _
 
                & " INSERT INTO Controls " _
 
                & " SELECT ApplicationName, FormName, ParentName, " _
 
                & " ControlName, ControlType, ControlText, ControlBackColour, ControlForeColour, " _
 
                & " LanguageCode, TranslatedControlText " _
 
                & " FROM Controls " _
 
                & " WHERE LanguageCode = '" & AUST_ENGLISH_LANGUAGE_CODE & "' " _
 
                & " AND ApplicationName = '" & anApplicationName & "'  "
 
 
 
            sSQL = sSQL & " UPDATE Controls " _
 
                & " SET LanguageCode = '" & aLanguageCode & "' " _
 
                & " WHERE ItemID > @oldMaxItemID "
 
 
 
            sSQL = sSQL & " DECLARE @oldMaxMessageID int " _
 
                & " SET @oldMaxMessageID = ( SELECT MAX(MessageID) " _
 
                & " FROM Message ) " _
 
                & " " _
 
                & " INSERT INTO Message " _
 
                & " SELECT ApplicationName, Message, " _
 
                & " LanguageCode, [Translation] " _
 
                & " FROM Message " _
 
                & " WHERE LanguageCode = '" & AUST_ENGLISH_LANGUAGE_CODE & "' " _
 
                & " AND ApplicationName = '" & anApplicationName & "'  "
 
 
 
            sSQL = sSQL & " UPDATE Message " _
 
                & " SET LanguageCode = '" & aLanguageCode & "' " _
 
                & " WHERE MessageID > @oldMaxMessageID "

Open in new window

0
edwardr
Asked:
edwardr
1 Solution
 
Jeffrey CoachmanCommented:
You cannot concatenate SQL queries in Access.

You must run them one at a time indivudually:

Something like this:

CurrentDB.Execute "Select...", dbFailonError
CurrentDB.Execute "Delete...", dbFailonError
CurrentDB.Execute "Insert...", dbFailonError

JeffCoachman

0
 
edwardrAuthor Commented:
That works.
Thanks.
EdwardR
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now