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

Popular posts from this blog

Why does Ruby on Rails generate add a blank line to the end of a file? -

keyboard - Smiles and long press feature in Android -

node.js - Bad Request - node js ajax post -