View Full Version : Inserting Multiple Records via VBScript

08-03-2007, 09:07 PM
Hi guys,

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, 08:23 PM
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

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

'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 & "')"