G
gregory.emerson
Hi, I am trying to query an access database using "Import External
Data". I originally recorded the macro, and used an arbitrary date to
filter for the data in Access. Now I am trying to make it more dynamic
by allowing the user to enter any date, and passing that variable to
the macro. However, I have ran into the several problems. I thought it
was because the format of the date was wrong, however when I change the
format, it still gives me an error under:
..Refresh BackgroundQuery = False. I would greatly appreciate anyones
help. Here is the code:
CorrectFormatIs = Format(PriceDate, "yyyy-mm-dd") & " 00:00:00"
Range("H1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=S:\Mid-Atlantic\PricingBoard\Board\DailyBoard.mdb;DefaultDir=S:\Mid-Atlantic\PricingBoard\Board;Driv"
_
), Array("erId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("H1"))
.CommandText = Array( _
"SELECT `~TMPCLP221001`.CoName, `~TMPCLP221001`.PriceType,
`~TMPCLP221001`.Size, `~TMPCLP221001`.ClosingProb,
`~TMPCLP221001`.DatetoCust, `~TMPCLP221001`.Product" & Chr(13) & "" &
Chr(10) & "FROM `S:\Mid-Atlantic\PricingBoard\Boa" _
, _
"rd\DailyBoard`.`~TMPCLP221001` `~TMPCLP221001`" & Chr(13) & ""
& Chr(10) & "WHERE (`~TMPCLP221001`.DatetoCust= {ts
'CorrectFormatIs'})" & Chr(13) & "" & Chr(10) & "ORDER BY
`~TMPCLP221001`.CoName" _
)
.Name = "Query from MS Access Database_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Data". I originally recorded the macro, and used an arbitrary date to
filter for the data in Access. Now I am trying to make it more dynamic
by allowing the user to enter any date, and passing that variable to
the macro. However, I have ran into the several problems. I thought it
was because the format of the date was wrong, however when I change the
format, it still gives me an error under:
..Refresh BackgroundQuery = False. I would greatly appreciate anyones
help. Here is the code:
CorrectFormatIs = Format(PriceDate, "yyyy-mm-dd") & " 00:00:00"
Range("H1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=S:\Mid-Atlantic\PricingBoard\Board\DailyBoard.mdb;DefaultDir=S:\Mid-Atlantic\PricingBoard\Board;Driv"
_
), Array("erId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("H1"))
.CommandText = Array( _
"SELECT `~TMPCLP221001`.CoName, `~TMPCLP221001`.PriceType,
`~TMPCLP221001`.Size, `~TMPCLP221001`.ClosingProb,
`~TMPCLP221001`.DatetoCust, `~TMPCLP221001`.Product" & Chr(13) & "" &
Chr(10) & "FROM `S:\Mid-Atlantic\PricingBoard\Boa" _
, _
"rd\DailyBoard`.`~TMPCLP221001` `~TMPCLP221001`" & Chr(13) & ""
& Chr(10) & "WHERE (`~TMPCLP221001`.DatetoCust= {ts
'CorrectFormatIs'})" & Chr(13) & "" & Chr(10) & "ORDER BY
`~TMPCLP221001`.CoName" _
)
.Name = "Query from MS Access Database_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With