Importing External Data using a variable

[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

 

[Conan Kelly]

Gregory,

I'm assuming that PriceDate was assigned a value before CorrectFormatIs =
Format(PriceDate, "yyyy-mm-dd") & " 00:00:00" (the first line of the code
you posted).

Is PriceDate declared as a Date data type variable? Is the "DatetoCust"
field in the Access DB a Date data type or a Text data type?

At first glance, it appears that you don't have your concatenation for your
WHERE statement correct. Try changing:

"WHERE (`~TMPCLP221001`.DatetoCust= {ts'CorrectFormatIs'})"
to
"WHERE (`~TMPCLP221001`.DatetoCust= {ts'" & CorrectFormatIs & "'})"

(I'm not familiar with the "{ts'...'}" part of your WHERE statement. It
seems like it might be unnecessary, but it also makes me think that the
DatetoCust field is a text field.)

Also, if DatetoCust is a Date/time data type, you may need to surround your
date with #'s (pound/number signs). So you might try changing your WHERE
statement to:

"WHERE (`~TMPCLP221001`.DatetoCust= {ts'#" & CorrectFormatIs & "#'})"
or
"WHERE (`~TMPCLP221001`.DatetoCust= #" & CorrectFormatIs & "#)"

Don't quote me on this (I am not an expert), but I don't think that you need
to format it, just as long as it is a date data type.

Also, I THINK that you can eliminate all of the "Chr(13) & "" & Chr(10)", if
you wanted to; they are unnecessary. The SQL syntax does not require
statements to be on separate lines, it just looks for the key words:
SELECT, FROM, WHERE, ORDER BY, GROUP BY, etc... (I'm almost certain that
the space between Chr(13) and Chr (10) is unnecessary)

I hope some, if not all of this, helps,

Conan Kelly