importing from access

[ceemo]

i have the below that imports data from a access recorded from a macro.
I want to pick up a date from another sheet to use as the
(ts_i_hist_agent_data.row_date={ts '2006-08-08 00:00:00'})

i have tried using a my as date and copying the date to mydate but this
doesnt work?

Also i have to enter a password each time i run this where in the code
can i put in the pass word so i dont have to do this each time? ive
tried chaning save password to true but no joy?


Range("A1").Select

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _

"ODBC;Description=dialler_access;DRIVER=SQL
Server;SERVER=wrkvictohok13be;UID=dialler_access;;APP=Microsoft Office
2003;WSID=WRKQUEEN" _

), Array("SRK4EC4")), Destination:=Range("A1"))

..CommandText = Array( _

"SELECT ts_i_hist_agent_data.agent_id,
ts_i_hist_agent_data.alloc, ts_i_hist_agent_data.avail,
ts_i_hist_agent_data.camp_id, ts_i_hist_agent_data.ccr_id,
ts_i_hist_agent_data.row_date, ts_i_hist_agent_" _

, _

"data.row_hour, ts_i_hist_agent_data.talk,
ts_i_hist_agent_data.wrap" & Chr(13) & "" & Chr(10) & "FROM
d_dialler.dbo.ts_i_hist_agent_data ts_i_hist_agent_data" & Chr(13) & ""
& Chr(10) & "WHERE (ts_i_hist_agent_data.row_date={ts '2006-08-08
00:00:00'})" _

)

..Name = "Query from dialler_access"

..FieldNames = True

..RowNumbers = False

..FillAdjacentFormulas = False

..PreserveFormatting = True

..RefreshOnFileOpen = False

..BackgroundQuery = True

..RefreshStyle = xlInsertDeleteCells

..SavePassword = True

..SaveData = True

..AdjustColumnWidth = True

..RefreshPeriod = 0

..PreserveColumnInfo = True

..Refresh BackgroundQuery:=False

End With

End Sub

 

[Tom Ogilvy]

s = format(Worksheets("Sheet1").Range("A1").Value,"yyyy-mm-dd 00:00:00")


then

(ts_i_hist_agent_data.row_date={ts '" & s & "'})

 

[ceemo]

thats great thank you very much. Does anyone have any clues on the
password bit. also when i run it, it sets it up from scratch witch
moves the old date to the right to fit in the new data. is there anyway
i can just get it to refresh over the top of the old import without this
happening. i did think of deleteing the columns first but this will
interfer with formulas linking to those cells

 

[ceemo]

does anyone know how to refresh with the new date raher than re-creatng
the query from scratch?