Databases again

[coyote69]

How would I search the database to avoid overlapping appointments if the appointment date was in one field and the time was in another?

 

[Nagrom]

Just check both fields at the same time. We'd really need to know more about the database layout and how you are accessing the data to give you a better answer.

 

[coyote69]

this is an access database and the text boxes are linked to the data base through the properties window. the fields are in two separate columns in the table. One field is the appointment date and the other is the appointment time. I need it to not allow the same appointment time on the same day. Sorry for the confusion I am new at this VB stuff.

 

[Nagrom]

Sounds like you are using data bound controls. I think the easiest way to implement this would be to setup a multi-field index in your database and set its "Unique" property to "Yes". There is an "Indexes" button on your Access toolbar for building/editing indexes. The online help tells you how to do it (though it is pretty intuitive). Once you have done this the database won't let you store duplicate values. This doesn't stop a user from entering duplicate values, though - it just throws an error (which you can handle) if they try. You can always use ADO to run a validation query using the users input before you attempt to update the fields.

 

[coyote69]

I would prefer to do it through the ado query but I have not used this function? Is it fairly easy?

 

[Nagrom]

Here is an example on how to do this with ADO. First you need to set a reference in your project to the Microsoft ActiveX Data Objects library (msado15.dll).

'==========================================================

Sub CheckForAppointment()

' Declare variables
Dim objDBConnection As ADODB.Connection
Dim objDBRecordset As ADODB.Recordset
Dim strSQL As String

' Create the necessary objects
Set objDBConnection = CreateObject("ADODB.Connection&quot
Set objDBRecordset = CreateObject("ADODB.Recordset&quot

' Set the appropriate attributes for the objects
With objDBConnection
.ConnectionString = "Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb"
.CommandTimeout = 300 '5 minutes
.Open 'Opens the connection to the DB
End With

With objDBRecordset
.CacheSize = 1 'We only care if we find ANY records, so don't waste resources caching a lot
.CursorLocation = adUseServer 'Keeps the cursor at the DB server for better performance
.CursorType = adOpenForwardOnly 'This cursor type uses the lest resources and is fine for what we are doing here
.LockType = adLockReadOnly 'We don't need a more complex locking scheme since we are just "looking"
End With

' Set the query string
strSQL = &quot;select * from <table name> where <date field> = '<date search value>' and <time field> = '<time search value>'&quot;

' Execute the query and return data to the recordset
objDBRecordset.Open strSQL, objDBConnection

' Check to see if we found any records
If Not objDBRecordset.EOF Then
' We found records with that criteria - do whatever we need to
' in order to make the user change the appointment date and time
End If

' Cleanup after ourselves
objDBRecordset.Close
objDBConnection.Close

Set objDBConnection = Nothing
Set objDBRecordset = Nothing

End Sub

 

[coyote69]

I got an error message that the data source not found when it went to the connection.open line.

 

[Nagrom]

In the connection string did you put the path to your .mdb file? The one I have in there is just for the example.

 

[coyote69]

Yes I checked that over and over again. I did notice that in my library I did not have the (msado15.dll) file.

 

[Nagrom]

Are you familiar with adding references within your VB projects (no offense intended - I just don't know your level of experience with VB). You must have a reference to the Microsoft ActiveX Data Objects library for this to work. If the machine doesn't have that installed for some reason, just go to Microsoft.com and search the the latest MDAC to load. After it is installed, just set a reference within the VB project (I can explain how to do that if you haven't done it before). You will need the latest MDAC anyway if you use that exact code because I am referencing the Jet OLEDB 4.0, which is not in earlier releases of MDAC.

 

[coyote69]

I am just a beginner with the vb programming. I opened the project menu and clicked on references to find the file but it was not there. If this is wrong I can definitely use the help.

 

[Nagrom]

That's the right place to look. It sounds like you don't have the MDAC installed. Go to

http://www.microsoft.com/data/download_270RTM.htm

to download the latest MDAC. Try again after this is installed.

 

[coyote69]

Still having the same problem. I did the download and the file is still not there and obviously the error is still there.

 

[Nagrom]

If it is installed on your machine you should see a reference to it. If you installed the latest MDAC it should show up as &quot;Microsoft ActiveX Data Objects 2.7 Library&quot; in the list, and the DLL that reference points to is &quot;msado15.dll&quot;. If that is not there then I really don't know what to tell you because it seems like something is wrong with your installation or something. Are you sure you don't already have a reference to it (if so then it would be at the top of the list). If you do, and the reference was set before you updated the MDAC, uncheck the reference, close the References window, then go back in and re-add it. Let me know how it goes.

 

[coyote69]

Yes I am sure. I do have a Microsoft activex data objects 2.5 library checked

 

[Nagrom]

Okay, if you have that referenced then you can use ADO. I think I see the error now - I left off the &quot;Provider=&quot; keyword in the connection string in my code example. Use the connection string &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb&quot; replacing &quot;C:\db1.mdb&quot; with the path to your Access database. If you still have a problem, try &quot;Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\db1.mdb&quot; in case you don't have the 4.0 version installed (though I am pretty sure MDAC 2.7 includes that).

 

[coyote69]

that got it past that part but now I get an error at this line &quot; objdbRecordset.Open strSql, objdbconnection&quot;. Surely there must be another way to run this check.

 

[Nagrom]

What error message are you getting? Did you replace the <table name>, <field name>, etc with the appropriate names from your database? Also, is the date field actually a date data type in your Access database? If it is, I believe you have to surround the value for the date selection in #'s.

 

[coyote69]

I get a run time error.

.Open
End With

With objdbRecordset
.CacheSize = 1
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.LockType = adLockReadOnly

End With


strSql = &quot;select * FROM contactmanager where [apptdate] = '&quot; & datenow & &quot;' and where[time] = '&quot; & timenow & &quot;'&quot;




objdbRecordset.Open strSql, objdbconnection <<<<< THIS IS HIGHLIGHTED WHEN MSG OCCURS


If Not objdbRecordset.EOF Then
End If

objdbRecordset.Close
objdbconnection.Close

Set objdbconnection = Nothing
Set objdbRecordset = Nothing

End Sub

 

[Nagrom]

Is the apptdate field actually a date data type in the database? If so, try:

strSql = &quot;select * FROM contactmanager where [apptdate] = #&quot; & datenow & &quot;# and where[time] = '&quot; & timenow & &quot;'&quot;