Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

More than one script in Microsoft Access 2007

Posted on 2012-03-23
13
325 Views
Last Modified: 2012-04-06
How can we run a series of lines in sql format in Ms Access
0
Comment
Question by:rayluvs
  • 7
  • 6
13 Comments
 

Author Comment

by:rayluvs
ID: 37758026
To be mores specific, we want to run this similar SQL statement for around 5000 product lines:

update Table1 set Column1='month'
update Table2 set Column1='987'
update Table3 set Column1='Frank'

Open in new window

0
 

Author Comment

by:rayluvs
ID: 37758027
What we meant is that there are around 5000 product and want to update the column1 with specific values.  The example above are 3 lines for descriptive only.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37758439
CurrentDB.execute "UPDATE YourTable SET column1=SomeSpecificValue", dbfailonerror
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37758477
For example:

CurrentDB.execute "UPDATE YourTable SET Salary=100", dbfailonerror
...For numeric values

or
CurrentDB.execute "UPDATE YourTable SET Country='Spain'", dbfailonerror
...for text values

See here for more info:
http://www.w3schools.com/sql/sql_update.asp
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37758506
...Oh, I see your specifics...

Then obviously you will have to have some sort of "Translation" system (Table)to say what value to update witch record with...
Do you have such a system (or Table) for reference?

I am sure another Expert will help in more detail shortly...

Jeff
0
 

Author Comment

by:rayluvs
ID: 37776720
Remember, we need to run the UPDATE within the Ms Access SQL scripting area and would like to run multiple lines simultaneously, like Ms SQL Query Analyzer.

I'm not in the office, but using "CurrentDB.execute" will permit me to have multiple lines with Ms Access SQL scripting area?  

Based on your recommendation, can I do this within MsAccess?:

CurrentDB.execute "update Table1 set Column1='month'", dbfailonerror
CurrentDB.execute "update Table2 set Column1='987'", dbfailonerror
CurrentDB.execute "update Table3 set Column1='Frank'", dbfailonerror

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37776886
Yes
0
 

Author Comment

by:rayluvs
ID: 37796494
Today will test
0
 

Author Comment

by:rayluvs
ID: 37799589
Didn't work.

Places a simple "select * from product" as follows

               CurrentDB.execute "select * from product", dbfailonerror

One didn't work nor multiple lines as we need (see Pic).

Please advice.

FYI: we placed the lines in SQL

ee1
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37801655
What I posted is "Code", not a query.

Make a form
But a button on the form
On the OnClick event of the button but the "Code":

CurrentDB.execute "update Table1 set Column1='month'", dbfailonerror
CurrentDB.execute "update Table2 set Column1='987'", dbfailonerror
CurrentDB.execute "update Table3 set Column1='Frank'", dbfailonerror

JeffCoachman
0
 

Author Comment

by:rayluvs
ID: 37801800
Yes, thank you, via VB we can do what we need.

But for this question placed, we would like to know if we can run multiple lines within the SQL of Ms Access.  In ID: 37776720 I ask that this specific question and was answer yes.

Please advice.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37802185
<But for this question placed, we would like to know if we can run multiple lines within the SQL of Ms Access.>
No
0
 

Author Comment

by:rayluvs
ID: 37803087
The core of the question has been answered and is the same conclusion as all google results, is that the MsAcces SQL window can't have multiple statements.

Understood.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
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…

839 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