Acces calander with mysql

**zmbd** · Dec 2 '13, 01:45 PM

OK, as you've discovered: Internally, the date format that access uses, no matter what the local date setting is, is in the "MM/DD/YYYY HH:MM:SS" format.

Make sure that you are using "#" around your dates.

If you will please post the code where you made the change to reference the MYSQL table within the calendar it would be most helpful.

Please make sure to format the code using the [CODE/] button on the toolbar.

**dannyflee** · Dec 2 '13, 07:27 PM

Thanks for your reply,

What do you mean by putting "#" around the dates. Can you be more specific.

This is the code to populate the calendar:

Code:

Private Sub PopulateCalendar()
On Error GoTo Err_PopulateCalendar
Dim strFirstOfMonth As String, bytFirstWeekdayOfMonth As Byte, bytBlockCounter As Byte
Dim bytBlockDayOfMonth As Byte, lngBlockDate As Long, ctlDayBlock As TextBox
Dim bytDaysInMonth As Byte, bytEventDayOfMonth As Byte, lngFirstOfMonth As Long
Dim lngLastOfMonth As Long, lngFirstOfNextMonth As Long, lngLastOfPreviousMonth As Long
Dim lngEventDate As Long, bytBlankBlocksBefore As Byte, bytBlankBlocksAfter As Byte
Dim astrCalendarBlocks(1 To 42) As String, db As DAO.Database, rstEvents As DAO.Recordset
Dim strEvent As String
Dim lngSystemDate As Long   'CFB added 1-25-08
Dim ctlSystemDateBlock As TextBox, blnSystemDateIsShown As Boolean  'CFB added 1-25-08
Dim strSQL As String        'Added 4/16/2008
Dim lngFirstDateInRange As Long     'CFB added 2-18-10
Dim lngLastDateInRange As Long      '
Dim lngEachDateInRange As Long      '
Dim strStartTime As String          '

lngSystemDate = Date        'CFB added 1-25-08
intMonth = objCurrentDate.Month
intYear = objCurrentDate.Year
lstEvents.Visible = False
lblEventsOnDate.Visible = False
lblMonth.Caption = MonthAndYear(intMonth, intYear)
strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)

'*************************************************************************
  'ADezii
  'NOTE: Will work in the UK (United Kingdom) and other European Nations
  'strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear)


bytFirstWeekdayOfMonth = WeekDay(strFirstOfMonth)
lngFirstOfMonth = DateSerial(intYear, intMonth, 1)
lngFirstOfNextMonth = DateSerial(intYear, intMonth + 1, 1)
lngLastOfMonth = lngFirstOfNextMonth - 1
lngLastOfPreviousMonth = lngFirstOfMonth - 1
bytDaysInMonth = lngFirstOfNextMonth - lngFirstOfMonth
bytBlankBlocksBefore = bytFirstWeekdayOfMonth - 1
bytBlankBlocksAfter = 42 - (bytBlankBlocksBefore + bytDaysInMonth)
    
Set db = CurrentDb
         
strSQL = "SELECT sales1.naam_klant, sales1.woonplaats, sales1.date, tblVisitType.Type, tblVisitType.Code, sales1.time " & _
         "FROM tblVisitType INNER JOIN sales1 ON tblVisitType.TypeID = sales1.TypeID " & _
         "WHERE sales1.date Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
         " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"

Set rstEvents = db.OpenRecordset(strSQL)    'Added 4/16/2008

Do While Not rstEvents.EOF
  'CFB added 2-18-10
  lngFirstDateInRange = rstEvents![Date]      '<Substitute for [Start Date]>
  If lngFirstDateInRange < lngFirstOfMonth Then
    lngFirstDateInRange = lngFirstOfMonth
  End If
  lngLastDateInRange = rstEvents![Date]         '<Substitute for [End Date]>
  If lngLastDateInRange > lngLastOfMonth Then
    lngLastDateInRange = lngLastOfMonth
  End If
  
  For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
    bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
    bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
                                              '<Substitute for [Title]>
      If astrCalendarBlocks(bytBlockCounter) = "" Then
        astrCalendarBlocks(bytBlockCounter) = Format$(rstEvents![Time], "hh:nn AM/PM") & vbCrLf & rstEvents![naam_klant] & ", " & _
                                              Left$(rstEvents![Woonplaats], 1) & "." & " [" & rstEvents! & "]"
      Else                                    '<Substitute for [Title]>
        astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
                                              Format$(rstEvents![Time], "hh:nn AM/PM") & vbCrLf & rstEvents![naam_klant] & ", " & _
                                              Left$(rstEvents![Woonplaats], 1) & "." & " [" & rstEvents! & "]"

      End If
  Next lngEachDateInRange
  'End of CFB added 2-18-10
  
    rstEvents.MoveNext
Loop
    
For bytBlockCounter = 1 To 42                           'blank blocks at start of month
  Select Case bytBlockCounter
    Case Is < bytFirstWeekdayOfMonth
      astrCalendarBlocks(bytBlockCounter) = ""
      ReferenceABlock ctlDayBlock, bytBlockCounter
      'ctlDayBlock.BackColor = 12632256
      ctlDayBlock.BackColor = 8421440
      ctlDayBlock = ""
      ctlDayBlock.Enabled = False
      ctlDayBlock.Tag = ""
    Case Is > bytBlankBlocksBefore + bytDaysInMonth     'blank blocks at end of month
      astrCalendarBlocks(bytBlockCounter) = ""
      ReferenceABlock ctlDayBlock, bytBlockCounter
      'ctlDayBlock.BackColor = 12632256
      ctlDayBlock.BackColor = 8421440
      ctlDayBlock = ""
      ctlDayBlock.Enabled = False
      ctlDayBlock.Tag = ""
        ctlDayBlock.Visible = Not (bytBlankBlocksAfter > 6 And bytBlockCounter > 35)
    Case Else   'blocks that hold days of the month
      bytBlockDayOfMonth = bytBlockCounter - bytBlankBlocksBefore
      ReferenceABlock ctlDayBlock, bytBlockCounter
      lngBlockDate = lngLastOfPreviousMonth + bytBlockDayOfMonth 'block's date
        If bytBlockDayOfMonth < 10 Then
          ctlDayBlock = Space(2) & bytBlockDayOfMonth & _
                        vbNewLine & astrCalendarBlocks(bytBlockCounter)
        Else
          ctlDayBlock = bytBlockDayOfMonth & _
                        vbNewLine & astrCalendarBlocks(bytBlockCounter)
        End If
            
        'If this block is the system date, change its color (CFB 1-25-08)
        If lngBlockDate = lngSystemDate Then
          ctlDayBlock.BackColor = RGB(0, 0, 255)
          ctlDayBlock.ForeColor = QBColor(15)
          Set ctlSystemDateBlock = ctlDayBlock
          blnSystemDateIsShown = True
        Else
          ctlDayBlock.BackColor = QBColor(15)
          ctlDayBlock.ForeColor = 8388608 '====> Added by ADezii on 1/28/2008 (Date
        End If                                  'Text was essentially invisible without it for
          ctlDayBlock.Visible = True            'Block representing current day position)
          ctlDayBlock.Enabled = True
          ctlDayBlock.Tag = lngBlockDate
  End Select
Next
 
'If the system date is in this month, show its events (CFB added 1-25-08)
If blnSystemDateIsShown Then
  PopulateEventsList ctlSystemDateBlock
End If
    
Call PopulateYearListBox    'Added by ADezii on 1/28/2008 - suggested by CFB

Exit_PopulateCalendar:
  Exit Sub
Err_PopulateCalendar:
  MsgBox Err.Description, vbExclamation, "Error in PopulateCalendar()"
  Call LogErrors(Err.Number, Err.Description, "frmCalendar", "PopulateCalendar() Sub-Routine", "Called from Multiple Locations")
    Resume Exit_PopulateCalendar
End Sub

This code is used to pouplate the eventlist:

Code:

Private Sub PopulateEventsList(ctlDayBlock As Control)
On Error GoTo Err_PopulateEventsList
Dim strSQL2 As String
           
strSQL2 = "SELECT sales1.id, sales1.naam_klant, sales1.woonplaats, sales1.date, sales1.time, " & _
          "tblVisitType.Type, tblVisitType.Code, sales1.adres FROM tblVisitType INNER JOIN sales1 ON " & _
          "tblVisitType.TypeID = sales1.TypeID " & _
          "WHERE sales1.date = " & ctlDayBlock.Tag & _
          " ORDER BY sales1.time, sales1.naam_klant, sales1.woonplaats;"

lstEvents.RowSource = strSQL2

lblEventsOnDate.Caption = Format(ctlDayBlock.Tag, "m-dd-yyyy")

If DCount("*", "sales1", "[date] = #" & CDate(ctlDayBlock.Tag) & "#") > 0 Then
  lstEvents.Visible = True
  lblEventsOnDate.Visible = True
Else
  lstEvents.Visible = True
  lblEventsOnDate.Visible = False
End If
    
Exit_PopulateEventsList:
  Exit Sub
  
Err_PopulateEventsList:
  MsgBox Err.Description, vbExclamation, "Error in PopulateEventsList()"
  Call LogErrors(Err.Number, Err.Description, "frmCalendar", "PopulateEventsList() Sub-Routine", _
                 "Called from PopulateCalendar() and all Text Boxes GotFocus() Events")
    Resume Exit_PopulateEventsList
End Sub

Additional info:

I've used the widescreen version of the calender and haven't made any changes to the script. I've only changed the linked table and fields.

This is the calendar i've used:
(A wide angled version (for wider screen real-estate) has also been posted now at Post #327. The attachment there is Wide Calendar with Switchboard.zip .)

**zmbd** · Dec 2 '13, 09:23 PM

I've used the widescreen version of the calender and haven't made any changes to the script. I've only changed the linked table and fields.

Can open the linked table directly within the access FE?

If not, then this might explain why when you convert/import the SQLServer to a local table things start to work as expected.

ADezii is really the expert on "The Calendar" project and will have a better insight as to the better solution.

Just in case: How to create a DSN-less connection to SQL Server for linked tables in Access

**dannyflee** · Dec 2 '13, 10:02 PM

I can open the table in the FE and it works just fine. It's used in querys/forms/etc and i'm having no troubles at all.

Only the dates from the table won't show up in the calender as long as the table is a mysql table. I've also tried to make a query and link the script of the calendar to the query, but i gives the same (no) result.

So my thought was that the script of the calendar doesn't understand the dates provided by the MySQL table.

By the way the link you added shows how to connect to SQL and i'm using MySQL. I always heard those are not the same.

**zmbd** · Dec 2 '13, 10:27 PM

You're right about the MySQL v SQL Server.
I've been having issues with one of my backends and have that stuck in my head. Stupid updates /.,.\

Go in to the MYSQL and change the date column to "DATETIME"
It seems to me that was an issue with one of my early attempts and the DBA didn't have any issue changing the type cast for the field and I just re-read something about this while attempting to track down your issue... This may also be of some help since you are using the MySql backend:(SQL Data Types for Various DBs)

(among other type cast issues... it isn't pretty)

**dannyflee** · Dec 2 '13, 10:55 PM

I've tried changing the date column to "DATETIME" and it had no effect. Also set the time-column to "DATETIME" but also without succes.

Also found something about converting the date like:

Format(CDate(Fo rmat([sales1.date], "0000-00-00")), "dd-mm-yyyy")

But it gives an error

**ADezii** · Dec 3 '13, 01:44 AM

Try changing

Code:

'************** Code Intentionally Removed **************
strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)

'COMMENTS intentianally removed
'strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear)
'************** Code Intentionally Removed **************

to

Code:

'************** Code Intentionally Removed **************
'strFirstOfMonth = Str(intMonth) & "/1/" & Str(intYear)

'COMMENTS intentianally removed
strFirstOfMonth = "1/" & Str(intMonth) & Str(intYear)
'************** Code Intentionally Removed **************

in the PopulateCalenda r() Sub-Routine. If this is not successful, try an explicit Date Conversion (Code Lines 4 & 8)

Code:

'************** Code Intentionally Removed **************
Do While Not rstEvents.EOF
  'CFB added 2-18-10
  lngFirstDateInRange = CDate(rstEvents![Date])
    If lngFirstDateInRange < lngFirstOfMonth Then
      lngFirstDateInRange = lngFirstOfMonth
    End If
      lngLastDateInRange = CDate(rstEvents![Date])
    If lngLastDateInRange > lngLastOfMonth Then
      lngLastDateInRange = lngLastOfMonth
    End If
'************** Code Intentionally Removed **************

**dannyflee** · Dec 3 '13, 11:17 AM

Adezii,

I've made the changes you suggested in the script, but the result is the same.
The calendar is still empty.

**ADezii** · Dec 3 '13, 06:22 PM

Let's go back to the very basics and start from there.

Revert back to the Original Code that you were using.
I wrote a small Code Snippet that will:
1. Test and see if the Recordset (rstEvents) contains any Records for the current Month (December) or is Empty.
2. If the Recordset does contain records, how many?
3. Is the [Date] Field in the Recordset recognized as a Valid Date Format by Access?

Insert the following Code immediately after the Recordset (rstEvents) is created in the PopulateCalenda r() Sub-Routine as indicated below:

Code:

'*********************** Code Intentionally Omitted ***********************
Set rstEvents = db.OpenRecordset(strSQL)    'Added 4/16/2008

With rstEvents
  If .BOF And .EOF Then         'NO Records
    MsgBox "rstEvents contains 0 Records"
  Else
    .MoveLast: .MoveFirst       'for accurate Record Count
      MsgBox "rstEvents consists of " & .RecordCount & " Records"
        MsgBox "[Date] " & IIf(IsDate(![Date]), " IS ", " IS NOT ") & _
               "recognized by Access as a Valid Date Field"
  End If
    .Close: Set rstEvents = Nothing
End With

Exit Sub
'*********************** Code Intentionally Omitted ***********************

These questions will be answered, then the Routine will be gracefully exited.
We'll take it from here on.

**zmbd** · Dec 3 '13, 06:31 PM

Are we actually using "Date" as a field name?
It is one of the reserved token words in Access, even when enclosed within square braces "[]" this has caused me much grief. Slightly changing the name in the field has solved many an issue in my inherited DBs even though I did have to change it in tons of queries and forms.

**ADezii** · Dec 3 '13, 06:43 PM

@zmbd:
I thought of that also, and I always discourage its use, but the Field is always qualified by the Recordset Object, namely:

Code:

rstEvents![Date]

In this specific Instance, it should not cause any problem(s). I think that the key point here is that all works well with the converted, local Table.

**dannyflee** · Dec 3 '13, 07:23 PM

@Adezii

rstEvents contains 0 records.
The current month should contain 3 records

@zmbd
I've used the same fieldname as in the original calender.

**ADezii** · Dec 3 '13, 07:33 PM

rstEvents contains 0 records

That would explain the blank Calendar.

What does Msgbox display (True/False) after executing the below?

Code:

Set rstEvents = db.OpenRecordset(strSQL)
Msgbox IsDate(rstEvents![Date])

**dannyflee** · Dec 3 '13, 07:53 PM

It displays onwaar.
Is dutch for false.

Is it correct that this means that acces/the script indentifies the date as not a date format?

Acces calander with mysql

Acces calander with mysql

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment