Getting data from an Access database

[Dave Jones]

I have an Excel spreadsheet created in Excel 2007 which extracts, via VBA/SQL, data from an Access database created in Access 2007. This works fine in Excel 2007. We are in the process of moving to Office 2013. I find that Excel 2013 does not extract Date/Time fields in the same way. I have a date/time field in Access with a "short time" format which when extracted to Excel 2013 gives a value of 00:00:00. Date/Time fields containing Dates export OK.
SQLString = "SELECT ID,SchedNo, OLSN, StartDate, ProductName, ResNo, StartTime, FinishDate, FinishTime, TotalMetreageProduced, DieNo, PumpNo FROM SchedDetails WHERE TimeOfRecord > #" & Format(Date - SearchDays, "mm/dd/yy") & "#"
Should I be doing something different in Excel 2013
Thanks

 

[Kyle123]

Try formatting the column as date after you've imported the data

 

[Dave Jones]

I tried that and I get a date of 00/01/1900 which I would expect as there is no date entered, only a time which shows as 00:00:00

 

[Kyle123]

What value is in the relevant field of the recordset when you set a breakpoint?

 

[Dave Jones]

I'm sorry but I do not know what you mean by "set a breakpoint"
Here is the code. Where would I set a breakpoint.
Sub GetLastFewSchedules()
Dim sConnectstring As String
Dim sQuerystring As String
Dim sdate As String
Dim MC As String
MC = Worksheets("MC").Range("A1")
Worksheets("StartHere").Range("C4:N100").Value = "" 'CLEAR RANGE
Worksheets("StartHere").Range("O5:O100").Value = ""
Worksheets("ScheduleSummary").Range("H8") = ""
'Define Search Period
SearchDays = Worksheets("StartHere").Range("O2")

'Search String
SQLString = "SELECT ID,SchedNo, OLSN, StartDate, ProductName, ResNo, StartTime, FinishDate, FinishTime, TotalMetreageProduced, DieNo, PumpNo FROM SchedDetails WHERE TimeOfRecord > #" & Format(Date - SearchDays, "mm/dd/yy") & "#"

'Run Query
sConnectstring = "ODBC;DSN=MS Access 97 Database;DBQ=U:\SHARE\Public\LogSheet Databases\" & MC & "\" & MC & "LogSheetBackEnd.accdb;Defaultdir=U:\SHARE\Public\LogSheet Databases\" & MC & ";Driveld=25;FIL=MS Access;MaxBufferSize=512;PageTimeout=5;"
Worksheets("StartHere").Activate
With ThisWorkbook.Worksheets("StartHere").QueryTables.Add(Connection:=sConnectstring, Destination:=Worksheets("StartHere").Range("C4"), Sql:=SQLString)
.Refresh
End With
End Sub
Thanks