Solved

More than one script in Microsoft Access 2007

Posted on 2012-03-23
13
291 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
Comment Utility
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
Comment Utility
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
Comment Utility
CurrentDB.execute "UPDATE YourTable SET column1=SomeSpecificValue", dbfailonerror
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
...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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Yes
0
 

Author Comment

by:rayluvs
Comment Utility
Today will test
0
 

Author Comment

by:rayluvs
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
<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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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.
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…

771 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

14 Experts available now in Live!

Get 1:1 Help Now