Prev: Access front end for sql server
Next: maitre du destin de l'italie ...
From: Lester on 28 Jun 2008 19:10
I'm driving myself crazy with a problem in trying to translate a query
written for Access to that for SQL server. I would think that I would
use a trigger, but am not sure how to set it up.

We have a database that manages bookings in four banquet halls. It was
running in an Access database, but two years ago, I migrated it to SQL
server.

In the access database I used VBA to check to ensure no duplicate
bookings. I wanted no dup events:
* on same day
* within the same time span
* in the same hall
* with a status of booked or tentative (other statuses include quoted
- duplicates are ok with that)

I'm still using access as the front end -- (Not an ADP but tables
linked) but for some reason this script doesn't work anymore.

Should I be using a trigger at the database level, or try to 'translate'
this VBA/SQL to work with SQL Server?

Thanks for your help

Lester

======================================================================



The Access VBA is:





Private Sub CheckConflict()
Dim db As Database
Dim rec As Recordset
Dim BookDate, StartTime, EndTime As Date
Dim Hall As Variant
Dim sqlstring, CurrentName As String

MsgBox "CheckConflict running"

BookDate = [Forms]![Hall Booking from Calendar].[ActiveXCtl30]
BookDate = Format(BookDate, "yyyy/mm/dd")
Hall = [Forms]![Hall Booking from Calendar].[Hall]
StartTime = [Forms]![Hall Booking from Calendar].[StartTime]
EndTime = [Forms]![Hall Booking from Calendar].[EndTime]
CurrentName = [Forms]![Hall Booking from Calendar].[EventName]

On Error GoTo EmptySet
Set db = CurrentDb
sqlstring = "SELECT Events.HallsID, Events.StartTime, Events.EndTime,
Events.StatusID, Events.EventName, Events.Date, Halls.HallsID,
Halls.[Hall Name] FROM Halls INNER JOIN Events ON Halls.HallsID =
Events.HallsID WHERE ((((Events.StatusID)=2 Or
(Events.StatusID)=3))AND((((Events.StartTime) Between #12/30/1899 ' &
StartTime & '# And #12/30/1899 ' & EndTime & '#) OR ((Events.EndTime)
Between #12/30/1899 ' & StartTime & '# And #12/30/1899 ' & EndTime &
'#)) AND ((Events.Date)= #' & BookDate & '# ) AND (Not(Events.EventName=
'" & EventName & "' ))AND((Halls.HallsID)= ' & Hall & ' )));"
Set rec = db.OpenRecordset(sqlstring, dbOpenDynaset, dbSeeChanges)

MsgBox ("This conflicts with the " & rec(4) & " booked in the " &
rec(7) & " Banquet Hall on " & rec(5) & " between " & rec(1) & " and "
& rec(2))
'If there are no conflicts, then allow the booking to proceed
without warning

Exit Sub
EmptySet:

' There are no conflicts
MsgBox "no event conflicts"

Exit Sub

rec.Close
End Sub
From: Albert D. Kallal on 28 Jun 2008 23:06
First, you subject says duplicates, and you are actually looking for
collisions. (grand canyon of different question here).

Furthermore, the ***big*** problem here is that supposedly you migrated this
application, but for two years, the collision code don't work? That just
does not seem right????


Anyway, there is little, if any reason why your code is not working after
migration to sql server. About 99%, or more of code should run untouched
when you move it to sql server.

>
> In the access database I used VBA to check to ensure no duplicate
> bookings. I wanted no dup events:
> * on same day
> * within the same time span
> * in the same hall
> * with a status of booked or tentative (other statuses include quoted -
> duplicates are ok with that)
>
> I'm still using access as the front end -- (Not an ADP but tables linked)
> but for some reason this script doesn't work anymore.
>
> Should I be using a trigger at the database level, or try to 'translate'
> this VBA/SQL to work with SQL Server?

No, just get your old code working? Why re-invent the wheel. You can use a
query to test for collisions above.

To prevent collisions, the logic here is quite simple:

A collision occurs when:

RequestStartDate <= EndDate
and
RequestEndDate >= StartDate


The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.

And, of course, you could simply add:

RequestStartDate <= EndDate
and
RequestEndDate >= StartDate
and
RequestHall = Hall

Remember, if you make your date fields a date+ time, then the above will
even allow multiple days for a booking.

As for code....something like:


dim strWhere as string
dim dtRequeestStartDate as date
dim dtRequestEndDate as date
dim intHall as integer

dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")
intHall = inputbox("What room")


strWhere="#" & format(dtRequestStartDate,"mm/�dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtRequestEndDate,"mm/dd�/yyyy") & "# >= StartDate" &
_
" and hall = " & intHall


if dcount("*","tableBooking",strW�here) > 0 then
msgbox "sorry, you can't book
....bla bla bla....


The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates. However, what is nice here is that
the simple conditions above does return ANY collisions.

However, it really begs the question, why not fix the code you already built
before?...it should be working just fine? I guess I can't understand how
something been running for two years that don't work??

Regardless, it is a fairly simply query to get collisions as the above
shows, it just up to you to provide a nice booking form that checks if the
requested date(s) + room is available.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal(a)msn.com



--


>
> Thanks for your help
>
> Lester
>
> ======================================================================
>
>
>
> The Access VBA is:
>
>
>
>
>
> Private Sub CheckConflict()
> Dim db As Database
> Dim rec As Recordset
> Dim BookDate, StartTime, EndTime As Date
> Dim Hall As Variant
> Dim sqlstring, CurrentName As String
>
> MsgBox "CheckConflict running"
>
> BookDate = [Forms]![Hall Booking from Calendar].[ActiveXCtl30]
> BookDate = Format(BookDate, "yyyy/mm/dd")
> Hall = [Forms]![Hall Booking from Calendar].[Hall]
> StartTime = [Forms]![Hall Booking from Calendar].[StartTime]
> EndTime = [Forms]![Hall Booking from Calendar].[EndTime]
> CurrentName = [Forms]![Hall Booking from Calendar].[EventName]
>
> On Error GoTo EmptySet
> Set db = CurrentDb
> sqlstring = "SELECT Events.HallsID, Events.StartTime, Events.EndTime,
> Events.StatusID, Events.EventName, Events.Date, Halls.HallsID, Halls.[Hall
> Name] FROM Halls INNER JOIN Events ON Halls.HallsID = Events.HallsID WHERE
> ((((Events.StatusID)=2 Or (Events.StatusID)=3))AND((((Events.StartTime)
> Between #12/30/1899 ' & StartTime & '# And #12/30/1899 ' & EndTime & '#)
> OR ((Events.EndTime) Between #12/30/1899 ' & StartTime & '# And
> #12/30/1899 ' & EndTime & '#)) AND ((Events.Date)= #' & BookDate & '# )
> AND (Not(Events.EventName= '" & EventName & "' ))AND((Halls.HallsID)= ' &
> Hall & ' )));"
> Set rec = db.OpenRecordset(sqlstring, dbOpenDynaset, dbSeeChanges)
>
> MsgBox ("This conflicts with the " & rec(4) & " booked in the " &
> rec(7) & " Banquet Hall on " & rec(5) & " between " & rec(1) & " and " &
> rec(2))
> 'If there are no conflicts, then allow the booking to proceed without
> warning
>
> Exit Sub
> EmptySet:
>
> ' There are no conflicts
> MsgBox "no event conflicts"
>
> Exit Sub
>
> rec.Close
> End Sub


From: Ed Murphy on 30 Jun 2008 01:57
Lester wrote:

> Should I be using a trigger at the database level, or try to 'translate'
> this VBA/SQL to work with SQL Server?

The former will be bulletproof against any front end whatsoever.

> sqlstring = "SELECT Events.HallsID, Events.StartTime, Events.EndTime,
> Events.StatusID, Events.EventName, Events.Date, Halls.HallsID,
> Halls.[Hall Name] FROM Halls INNER JOIN Events ON Halls.HallsID =
> Events.HallsID WHERE ((((Events.StatusID)=2 Or
> (Events.StatusID)=3))AND((((Events.StartTime) Between #12/30/1899 ' &
> StartTime & '# And #12/30/1899 ' & EndTime & '#) OR ((Events.EndTime)
> Between #12/30/1899 ' & StartTime & '# And #12/30/1899 ' & EndTime &
> '#)) AND ((Events.Date)= #' & BookDate & '# ) AND (Not(Events.EventName=
> '" & EventName & "' ))AND((Halls.HallsID)= ' & Hall & ' )));"

This definitely won't work without some fixing up, but should at least
be in the ballpark:

create trigger Events_IU on Events as
begin

declare @EventName varchar(30)
declare @HallName varchar(30)
declare @Date datetime
declare @StartTime datetime
declare @EndTime datetime

SELECT @EventName = e.EventName,
@HallName = h.[Hall Name],
@Date = e.Date,
@StartTime = e.StartTime,
@EndTime = e.EndTime
FROM Events e
INNER JOIN Halls h ON h.HallsID = e.HallsID
WHERE e.Date = inserted.Date
AND (e.StartTime between inserted.StartTime and inserted.EndTime
OR e.EndTime between inserted.StartTime and inserted.EndTime)
AND h.HallsID = inserted.HallsID
AND e.StatusID in (2,3) -- booked, tentative
AND e.EventName <> inserted.EventName

if @EventName is not null
begin
raiserror('This conflicts with the %s booked in the %s Banquet Hall on
%s between %s and %s',
16, 1, e.EventName, h.[Hall Name], e.Date, e.StartTime, e.EndTime)
rollback transaction
end

end

Note that this will always generate a false positive if you try to
change the EventName of an existing row.
From: Ed Murphy on 30 Jun 2008 01:57
Lester wrote:

> Should I be using a trigger at the database level, or try to 'translate'
> this VBA/SQL to work with SQL Server?

The former will be bulletproof against any front end whatsoever.

> sqlstring = "SELECT Events.HallsID, Events.StartTime, Events.EndTime,
> Events.StatusID, Events.EventName, Events.Date, Halls.HallsID,
> Halls.[Hall Name] FROM Halls INNER JOIN Events ON Halls.HallsID =
> Events.HallsID WHERE ((((Events.StatusID)=2 Or
> (Events.StatusID)=3))AND((((Events.StartTime) Between #12/30/1899 ' &
> StartTime & '# And #12/30/1899 ' & EndTime & '#) OR ((Events.EndTime)
> Between #12/30/1899 ' & StartTime & '# And #12/30/1899 ' & EndTime &
> '#)) AND ((Events.Date)= #' & BookDate & '# ) AND (Not(Events.EventName=
> '" & EventName & "' ))AND((Halls.HallsID)= ' & Hall & ' )));"

This definitely won't work without some fixing up, but should at least
be in the ballpark:

create trigger Events_IU on Events as
begin

declare @EventName varchar(30)
declare @HallName varchar(30)
declare @Date datetime
declare @StartTime datetime
declare @EndTime datetime

SELECT @EventName = e.EventName,
@HallName = h.[Hall Name],
@Date = e.Date,
@StartTime = e.StartTime,
@EndTime = e.EndTime
FROM Events e
INNER JOIN Halls h ON h.HallsID = e.HallsID
WHERE e.Date = inserted.Date
AND (e.StartTime between inserted.StartTime and inserted.EndTime
OR e.EndTime between inserted.StartTime and inserted.EndTime)
AND h.HallsID = inserted.HallsID
AND e.StatusID in (2,3) -- booked, tentative
AND e.EventName <> inserted.EventName

if @EventName is not null
begin
raiserror('This conflicts with the %s booked in the %s Banquet Hall on
%s between %s and %s',
16, 1, @EventName, @HallName, @Date, @StartTime, @EndTime)
rollback transaction
end

end

Note that this will always generate a false positive if you try to
change the EventName of an existing row.
 | 
Pages: 1
Prev: Access front end for sql server
Next: maitre du destin de l'italie ...