Solved

Running Multiple Queries

Posted on 2014-02-09
9
305 Views
Last Modified: 2014-02-13
I have a VBScript page that is designed to run about a dozen queries to create separate tables for different conditions. The first four are as follows:
 
sql="INSERT INTO membersBkup SELECT members.* FROM members"
 Set rs=conn.Execute(sql)
  
 sql="DELETE * from membersTemp"
 Set rs=conn.Execute(sql)
 
 sql="INSERT INTO membersTemp SELECT members.* FROM members"
 Set rs=conn.Execute(sql)
  
 sql="DELETE * from members"
 Set rs=conn.Execute(sql)

Open in new window

The entire sequence didn't work, so I tested running one at a time. When I run the first three, it runs as intended. When I run the first four, membersTemp is always empty.

It appears that members gets deleted before the insert into the temp file. Is this a timing issue? Is there a way to delay between executions? Is there a better way to do this?
0
Comment
Question by:slegy
[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
  • 4
  • 3
9 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 39846090
You could try to create just one sql statement and separate each line with a semi colon. If they doesn't work I would create a stored procedure with all of the statements located there.
0
 

Author Comment

by:slegy
ID: 39846180
Thank you so much again. I tried using the single statement with a semi colon separator and got the same results. I've never used a stored procedure, so I'll have to start researching.
0
 
LVL 33

Assisted Solution

by:Big Monty
Big Monty earned 100 total points
ID: 39846372
run the following query in your databse. it'll create the stored procedure that you can then call from wherever:

create PROCEDURE dbo.sp_doMembersMaintenance
@userID int
AS
BEGIN
   SET NOCOUNT ON;

INSERT INTO membersBkup SELECT members.* FROM members;
  
DELETE * from membersTemp;
 
INSERT INTO membersTemp SELECT members.* FROM members;

DELETE * from members;

END

Open in new window


and in your vbscript code, you would do thr following to run the stored procedure:

sql = exec sp_doMemberMaintenance"
set rs = conn.Execute( sql )

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 39847093
Are you using SQL Server or Access? You have included this in the Access zone, but not the SQL Server zone.

If you're using Access, then you can't run more than one statement at a time (i.e. the semi-colon delimited method), and Access does not use stored procedures (although it can call server-based SPs).

You don't need to set a rs variable when doing this so change this line:

Set rs = conn.execute(sql)

to this:

conn.Execute(sql)

When you run the first 4, does membersBkp contain data?

Are membersBkp and membersTemp EXACTLY the same structure as the members table? I get nervous when I see "SELECT *", unless you're using SELECT INTO syntax.

You might try changing the order in which these occur:

Delete from membersTemp
Insert into membersTemp and membersBkp
Delete from members
0
 

Author Comment

by:slegy
ID: 39847458
We are running an ASP site with Access. Yes, the tables are exactly the same structure. I am trying some of the suggested changes.
0
 

Author Comment

by:slegy
ID: 39847670
Obviously, I need to rethink this entire approach. It never occurred to me that the queries would not be executed sequentially. Here is the entire list. When I omit the three deletes at the bottom, everything works. When the deletes are included, nothing works - as if they are executed first. I would like to understand why it works this way.
 
sql="INSERT INTO membersBkup SELECT members.* FROM members"
 conn.Execute(sql)
  
 sql="DELETE * from membersTemp"
 conn.Execute(sql)
 
 sql="INSERT INTO membersTemp SELECT members.* FROM members"
 conn.Execute(sql)
 
 sql="DELETE * from membersRenew"
 conn.Execute(sql)
  
 sql="DELETE * from membersrenewUpdt"
 conn.Execute(sql)
  
 sql="DELETE * from membersNew"
 conn.Execute(sql)
  
 sql="DELETE * from membersUnresolved"
 conn.Execute(sql)
 
 sql="DELETE * from membersUnauthorized"
 conn.Execute(sql)
  
 sql="DELETE * from memLogin"
 conn.Execute(sql)
 
 sql="INSERT INTO membersRenew SELECT membersTemp.* FROM membersTemp WHERE membersTemp.appType='R' AND membersTemp.memUpdt='N' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="INSERT INTO membersrenewUpdt SELECT membersTemp.* FROM membersTemp WHERE membersTemp.appType='R' AND membersTemp.memUpdt='Y' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="INSERT INTO membersNew SELECT membersTemp.* FROM membersTemp WHERE membersTemp.appType='M' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="DELETE * FROM membersTemp WHERE membersTemp.appType='R' AND membersTemp.memUpdt='N' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="DELETE * FROM membersTemp WHERE membersTemp.appType='R' AND membersTemp.memUpdt='Y' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="DELETE * FROM membersTemp WHERE membersTemp.appType='M' AND membersTemp.paypalStatus='Pending'"
 conn.Execute(sql)
 
 sql="INSERT INTO membersUnresolved SELECT membersTemp.* FROM membersTemp"
 conn.Execute(sql)
 
 sql="INSERT INTO membersunauthBkup SELECT membersUnauth.* FROM membersUnauth"
 conn.Execute(sql)
 
 sql="INSERT INTO membersUnauthorized SELECT membersUnauth.* FROM membersUnauth"
 conn.Execute(sql)
 
 sql="INSERT INTO memberloginBkup SELECT memberLogin.* FROM memberLogin"
 conn.Execute(sql)
 
 sql="INSERT INTO memLogin SELECT memberLogin.* FROM memberLogin"
 conn.Execute(sql)
  
 sql="DELETE * from members"
 conn.Execute(sql)
 
 sql="DELETE * from membersUnauth"
 conn.Execute(sql)
  
 sql="DELETE * from memberLogin"
 conn.Execute(sql)

Open in new window

To clarify, this information is being downloaded to update a local database.
0
 

Author Comment

by:slegy
ID: 39848795
My apologies to both of you. This was serious programmer error. All is well.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39850442
good to hear, glad you got it working!

please close out this question, awarding ;points if any of the suggestions helped you resolve the problem, or do a request for attention if you need a mod to close it out.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

628 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