More than one script in Microsoft Access 2007

How can we run a series of lines in sql format in Ms Access
rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
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
rayluvsAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
CurrentDB.execute "UPDATE YourTable SET column1=SomeSpecificValue", dbfailonerror
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
...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
rayluvsAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
Yes
0
rayluvsAuthor Commented:
Today will test
0
rayluvsAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
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
rayluvsAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
<But for this question placed, we would like to know if we can run multiple lines within the SQL of Ms Access.>
No
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.