MySQL Timestamp with MS Access

Posted on 2009-12-30
Last Modified: 2012-05-08
When I create a new record in MySQL via MS Access the timestamp field is blank.  I can't edit the record in Access unless I get a value in the timestamp field for either new or existing imported records, but Access won't let me insert data into that field.  How can I get a value into that field so I can edit my new record?  This was not a problem when I was connected via DSN, so why is my DSN-Less connection acting differently?  With a DSN connection I can create a new record with a blank timestamp, and then the timestamp is entered automaticaly by MySQL when I edit the record in Access.  
With a DSN-Less connection created via code, I can create a new record, but the blank timestamp has to be popumated from MySQL/Navicat before I can edit in Access.  Is there a parameter setting I need to make when make the connection via VBA to eliminate this difference?

Besides this issue, the DSN-less connection method is very easy to deploy and I'd rather make it work than depend on DSN.

This is the function code I am using to set up the DSN-Less connection:
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=MySQL ODBC 5.1 Driver;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function
Question by:DavidSmithstein

    Accepted Solution

    I think I figured it out.  The Timestamp field needs a default value of:
    Now everything works like it did with a DSN far  :-)
    LVL 84
    The TimeStamp is generated by the server ... how are you working with the data? Are you using bound forms, or are you working with SQL/VBA?
    LVL 4

    Expert Comment

    Just want to check in and make sure that everything is working for you.  If so, you may want to close this question out.

    Author Comment

    I using bound forms in an Access 2002 app with links to the MySQL database generated via VBA code. (DSN-Less)
    So far so good, I'm back to validation testing.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    I've written instructions for one router type, but this principle may be useful for others of the same brand and even other brands of router. Problem: I had an issue especially with mobile devices that refused to use DNS information supplied via…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now