Solved

More than one script in Microsoft Access 2007

Posted on 2012-03-23
13
335 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
[X]
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
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

730 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