importing dates from SQL Server into MS Access

Question

Im importing a table from SQL SErver into MS Access via a stored procedure. All the code is working fine, but I can't seem to import dates properly from SQL Server.

Here is my code in VBA:

Dim db As Database

Dim myTable As TableDef

Dim myField As Field

Dim myProp As Property

    Set db = CurrentDb

    Set myTable = db.CreateTableDef("tblPaxGraphData")

    With myTable

        .Fields.Append .CreateField("year_ending", dbDate)

        .Fields.Append .CreateField("pax", dbInteger)

        .Fields.Append .CreateField("transactions", dbInteger)

        .Fields.Append .CreateField("time_band", dbText)

        .Fields.Append .CreateField("day_type", dbText)

        .Fields.Append .CreateField("entrance", dbText)

        .Fields.Append .CreateField("ticketing_system", dbText)

    End With

    db.TableDefs.Append myTable

    Set myField = myTable.Fields("year_ending")

    Set myProp = myField.CreateProperty("Format", dbText, "Short Date")

    myField.Properties.Append myProp

    Application.RefreshDatabaseWindow

In SQL Server I have a column which is of type Date. 

Here is what it looks like after I import the date field (all the dates are suppose to be:

2013-03-31

2013-06-30

2013-09-30

2013-12-31

):

Answer 1

Solved it.

Just incase anyone has a similar issue, you need to look at the INSERT INTO statement. 

When inserting data, you need to pretend it is a text, and use the following format:

' " & DataSet!FieldName & " '.