Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
08-03-2007, 08:07 PM #1
- Join Date
- Mar 2006
- Sumter, SC
- Thanked 4 Times in 4 Posts
Inserting Multiple Records via VBScript
The issue I'm having is trying to figure out the logic behind how to insert multiple records into an Access database with out having to run the add or edit scripts over and over again. Basicly its an event calendar where there is an option to insert a recurring event. (ex: party at bob's every wed night at 7:00). Assuming the starting date is 8/8/07 and I can use the dateadd function to add 7 days to the starting date, how could I use this to insert these dates into the database with out running the script over and over?
08-04-2007, 07:23 PM #2
- Join Date
- Dec 2002
- Arlington, Texas USA
- Thanked 8 Times in 8 Posts
one way would be to use some asp code to build a string of dates as a comma seperated list and then use that variable in your sql insert statement using an IN statement in your WHERE clause
Code:Dim startDate Dim iNumWeeks Dim sDates startDate = CDate("8/08/2007") sDates = "startDate" iNumWeeks = 10 'let's say the party every wednesday is for a period of 10 weeks. for i = 1 to iNumWeeks sDates = sDates & "','" & DateAdd("d",7,startDate) startDate = DateAdd("d",7,startDate) 'increment this value by 1 week Next 'as an insert statement sSQL = "INSERT INTO MyTable (EventField) VALUES('party at Bob''s - 7pm') WHERE DateField IN ('" & sDates & "')" 'as an update statement to update what may already be there sSQL = "UPDATE MyTable SET EventField = EventField + 'party at Bob''s - 7pm' WHERE DateField IN ('" & sDates & "')"
Last edited by miranda; 08-05-2007 at 01:08 AM.