VBA - Excel macro to create Access database and update field properties (date/time format)

Question

I have an Excel macro that copies data from several Excel Worksheets to a single Access table. Everything is working well except that the data in access should be converted to mmmm yyyy whilst when I copy it to access, it converts always to dd/mm/yyyy.

The fields are "Start Date" and "End Date".

Here is an extract of my code:

Dim Catalog As Object
Dim cn As ADODB.connection
Dim dbPath as String, scn as String

dbPath = CPTwb.Path & "\Flatfile.accdb"`
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"

Set Catalog = CreateObject("ADOX.Catalog")`
Catalog.Create scn
Set Catalog = Nothing
Set cn = New ADODB.connection

With cn
    .Open scn
    .Execute "CREATE TABLE Flatfile ([Tracker Product] text(255) WITH 
         Compression, " & "[Contract ID] text(255) WITH Compression, " & _
         "[Client] text(255) WITH Compression, " & _
         "[Start Date] datetime, " & "[End Date] datetime)"
End With

TheSQL = "INSERT INTO Flatfile ([Tracker Product], [ContractID], [Client],[Start Date], [End Date])"
TheSQL = TheSQL & "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & flatPath & "]." & "[" & Application.ActiveSheet.name & "$]"

cn.Execute TheSQL
cn.close

I understand the property "datatime" always gets the values as system date and that it is not possible to edit via DDL.

Is there a way to apply via VBA the property like in this screenshot below?

Gustav · Accepted Answer · 2017-08-30 09:52:48Z

2

The format is for display only. A true date value is a numeric value.

So use this:

"SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & flatPath & "]." & "[" & Application.ActiveSheet.name & "$]"

as source in a simple select query. Add conversion (and filtering) as needed to make sure that you have true date values for [Start Date]. For example, the dates may be read as text, then convert with DateValue:

TrueStartDate: DateValue([DateColumnFromExcel])

Now, save that query and use it as source when you build TheSQL.

answered Aug 30, 2017 at 9:52

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA - Excel macro to create Access database and update field properties (date/time format)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related