?
Solved

MySQL Timestamp with MS Access

Posted on 2009-12-30
4
Medium Priority
?
911 Views
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
    Next
     
    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"
    Else
        '//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
'-----------------------------------------------------------------------------------------------------------------------------------
0
Comment
Question by:David Smithstein
  • 2
4 Comments
 

Accepted Solution

by:
David Smithstein earned 0 total points
ID: 26149610
I think I figured it out.  The Timestamp field needs a default value of:
CURRENT_TIMESTAMP
Now everything works like it did with a DSN connection.....so far  :-)
0
 
LVL 85
ID: 26149615
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?
0
 
LVL 4

Expert Comment

by:souquetbressand
ID: 26155067
Just want to check in and make sure that everything is working for you.  If so, you may want to close this question out.
0
 

Author Comment

by:David Smithstein
ID: 26156412
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.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

807 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