sql - Export Excel Spread Sheet to Access Table with A Timestamp? -
i use set 1 below.
set accessconn = createobject("adodb.connection") sexcel = "[excel 8.0;hdr=yes;imex=2;database=" & thisworkbook.fullname & "].[sheet1$]" accessconn.open "dsn=foo", "", "" ssql = "insert bartable select * " + sexcel accessconn.execute ssql
now if wanted add now() column in export possible? like
set accessconn = createobject("adodb.connection") sexcel = "[excel 8.0;hdr=yes;imex=2;database=" & thisworkbook.fullname & "].[sheet1$]" accessconn.open "dsn=foo", "", "" ssql = "insert bartable select *,now() " + sexcel accessconn.execute ssql
where last column in bartable date/time column , want insert current time it?
to enter date in sql may need convert text - access can handle 'now()' not sure if can when being passed sql string. below should it.
ssql = "insert bartable select *,#" & format(now(),"mm/dd/yyyy hh:mm:ss") & "# datetimestamp " + sexcel
in access have 2 functions use getting date or date , time stamp when building sql strings, haven't tested should translate excel vba okay:
function makesqldatetime(psourcedate variant) string dim sqlday string, sqlmonth string, sqlyear string, sqltime string sqlday = day(psourcedate) sqlmonth = month(psourcedate) sqlyear = right(year(psourcedate), 4) sqltime = hour(psourcedate) & ":" & minute(psourcedate) & ":" & second(psourcedate) makesqldatetime = "#" & sqlmonth & "/" & sqlday & "/" & sqlyear & " " & sqltime & "#" end function function makesqldate(psourcedate variant) string dim sqlday string dim sqlmonth string dim sqlyear string sqlday = day(psourcedate) sqlmonth = month(psourcedate) sqlyear = right(year(psourcedate), 4) makesqldate = "#" & sqlmonth & "/" & sqlday & "/" & sqlyear & "#" end function
to use these complete trying:
ssql = "insert bartable select *," & makesqldatetime(now()) & " datetimestamp " + sexcel
Comments
Post a Comment