473,326 Members | 2,128 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Please Help - Can't check for duplicates

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
Jun 28 '08 #1
3 2372
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
pl*****************@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

Jun 29 '08 #2
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.
Jun 30 '08 #3
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.
Jun 30 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Lowell Kirsh | last post by:
I have a script which I use to find all duplicates of files within a given directory and all its subdirectories. It seems like it's longer than it needs to be but I can't figure out how to shorten...
3
by: Josh Armstrong | last post by:
I need a form to check for duplicates before a new record is added. Table name: Links Field1: LinkSysNum Field2: SysNum For example, =539 is linked to =540 =544 The db is setup using a...
8
by: Iona | last post by:
Hi Allan, I'm using a nifty piece of code you put on here some time back to do a duplicate entry check as below. I'm using to check for duplicate names. However I am getting an error message on...
4
geo039
by: geo039 | last post by:
I tried to write a simple application that takes user input by text and time selected by date time picker. It displays the appt description in one list box and the time in another list box. I wrote...
9
by: TF | last post by:
Hello all, I made a ASP.NET 2.0 site that shows possible "recipes" for paint colors stored in an access dbase. Basically, 1000 colors are stored with specific RGB values in separate columns. A...
11
by: richardkreidl | last post by:
I'm trying to check for duplicates before I do an INSERT into the Access database table. I basically want to alert the user if the 'ProjectName' and the 'MileStones' are already in the table.. ...
3
by: Lester | last post by:
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...
40
by: kylie991 | last post by:
Hi I am stuck on checking if a 2D array has duplicates. I have to write a function to check if a column has duplicates and then another function to check if the rows have duplicates from a file. ...
1
by: tskmjk55 | last post by:
Recently, I have a requirement to develop a vb.net application wherein the input excel sheet data which has an average of 5000 records should be checked for Internal duplicates (duplicates within the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.