Solved

Query string concatenation in Access SQL

Posted on 2009-04-06
2
1,215 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now