Solved

Query string concatenation in Access SQL

Posted on 2009-04-06
2
1,228 Views
Last Modified: 2013-11-27
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
Comment
Question by:edwardr
2 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 24083713
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
 

Author Closing Comment

by:edwardr
ID: 31567318
That works.
Thanks.
EdwardR
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question