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?
 
Jeffrey CoachmanConnect With a Mentor MIS 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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jeffrey CoachmanMIS LiasonCommented:
CurrentDB.execute "UPDATE YourTable SET column1=SomeSpecificValue", dbfailonerror
0
 
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
 
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
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.

All Courses

From novice to tech pro — start learning today.