Problems with .Refresh BackgroundQuery:=False

[dmplacebo]

Hello!!!!

My name is David and I'm a Spanish boy so perhaps my English is not as
good as it would be in order to explain my problem, but I'll try.

First of all, thank you in advance for all your help because I'm sure
that with your support my application will work in a good way.

The problem is that I've got the next piece of code:

Sub Busqueda(Dia As Date)
'
' Busqueda Macro
' Macro grabada el 04/07/2005 por s602043
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=*****;UID=SMP;PWD=****;SERVER=****;",
Destination:=Range( _
"A7"))
..CommandText = Array( _
"SELECT T_BLOC_ARRET.I_ZON_NUMERO,
T_BLOC_ARRET.C_BA__DATE_DE_DEBUT, T_BLOC_ARRET.C_BAP_LIBELLE_ARRET,
T_POSTE_ARCHIVE.I_HPO_DATE" & Chr(13) & "" & Chr(10) & "FROM
SMP.T_BLOC_ARRET T_BLOC_ARRET, SMP.T_POSTE_ARCHIVE T_POSTE_ARCHIV" _
, _
"E" & Chr(13) & "" & Chr(10) & "WHERE T_BLOC_ARRET.I_HPO_NUMERO
= T_POSTE_ARCHIVE.I_HPO_NUMERO AND T_BLOC_ARRET.I_ZON_NUMERO =
T_POSTE_ARCHIVE.I_ZON_NUMERO AND T_BLOC_ARRET.C_BA__DATE_DE_DEBUT > " &
Dia & " AND ((T_BLOC_ARRET.I_ZON_NUMERO=1002))")
..Name = "Consulta desde *****"
..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

I've got a form that gets information from the user (this information
is the date), after that, a macro that is called "Busqueda" receives
the information from the form and executes some SQL code. I think that
the main problem is when the information is sent to the worksheet but I
don't know because the error that appears on the screen is:

Error 1004, ODBC connection

Then, a yellow narrow points to the line:

..Refresh BackgroundQuery:=False

What can I do with this error?????.

I'm in a hurry so please!!!!!! answer me as soon as possible.

Thank you, David.

 

[Tom Ogilvy]

The query isn't executed until you Refresh the query. So this indicates
that your query is improperly formed. I can't tel you which part is
incorrect, but some aspect of the query is incorrect.

 

[Guest]

first you need to get that query working in the source db...

and it wiull be easier to debug if it hadn't been recorded using the Excel
Macro Recorder.

HTH

Philip

 

[dmplacebo]

I'll be working this days testing the code, and I hope it'll work soon
but... the problem is that the query was checked before in a different
program from Microsoft Query in what the query you've seen was
programmed.

I've got a doubt. The problem appeared when I included within the code
the param I get from the user, could it be the problem???, I'll check
it.

Thank you again for your help, David.

Whatever kind of help would be a help, thanks!!!!!.

 

[DM Unseen]

dmplacebo

the query should be:

..CommandText = Array( _
"SELECT T_BLOC_ARRET.I_ZON_NUMERO,
T_BLOC_ARRET.C_BA__DATE_DE_DEB­UT, T_BLOC_ARRET.C_BAP_LIBELLE_ARR­ET,

T_POSTE_ARCHIVE.I_HPO_DATE" & Chr(13) & "" & Chr(10) & "FROM
SMP.T_BLOC_ARRET T_BLOC_ARRET, SMP.T_POSTE_ARCHIVE T_POSTE_ARCHIV" _
, _
"E" & Chr(13) & "" & Chr(10) & "WHERE T_BLOC_ARRET.I_HPO_NUMERO
= T_POSTE_ARCHIVE.I_HPO_NUMERO AND T_BLOC_ARRET.I_ZON_NUMERO =
T_POSTE_ARCHIVE.I_ZON_NUMERO AND T_BLOC_ARRET.C_BA__DATE_DE_DEB­UT >
""" &
Format(Dia,"mm-dd-yy") & """ AND ((T_BLOC_ARRET.I_ZON_NUMERO=10­02))")


Note the changed string part:

""" &
Format(Dia,"mm/dd/yy") & """

The date should be enclosed in quotes, but since youre inside a string
you need to double them. Also you need to convert the date to a string
again for the SQL, so you need to know which string format is accepted
by the SQL source (I assume american date format)

DM Unseen

 

[dmplacebo]

Hi!!!

Thanks for your information but... I've got a doubt about the things
you've told me.

The macro that I'm using receives the param "Dia" in DATE format but
I'm not sure if this is the best option or if I should change to string
format.

My date base accepts the following format: (I was testing this in other
software)

TO_DATE('29/06/2005 22:59:00','DD/MM/YYYY HH24:MI:SS')

where Dia=29/06/2005 22:59:00

The problem is that I can't use the function TO_DATE in the VBA code,
that means I must use the function Format() or not????.

How can I include my param "Dia" in the appropiate format???.

Thank you, thank you and thank you again.

David.


T_BLOC_ARRET.C_BA__DATE_DE_DEB=ADUT >""" & Format(Dia,"mm-dd-yy") & """


Note the changed string part:

""" &
Format(Dia,"mm/dd/yy") & """

The date should be enclosed in quotes, but since youre inside a string
you need to double them. Also you need to convert the date to a string
again for the SQL, so you need to know which string format is accepted
by the SQL source (I assume american date format)

 

[DM Unseen]

OK, this would then be something like:

..... &" TO_DATE(' " & Format(Dia,"DD/MM/YYYY HH24:MI:SS") &" '
,'DD/MM/YYYY HH24:MI:SS')" & ........

This is very nasty stuff btw

I would start with using *implicit* date conversion(my pervious mail),
but that depends on the database platform and the conversion rules.
This would then eliminate the TO_DATE function.

coding a parameter inside an SQL string is *not* the most elegant way
to do this BTW. Parameter binding works much better(and no conversion
hassle). XL/MS Query can do this for you.

My advice for complex queries in XL is normally around the lines of:

Start with MS Query and make a simple query that works, but is probably
too simple to be actually useful (e.g. a simple select)
Then start adding your parameters in MS Query (like your date). to this
simple query and see if it runs. For this you need to learn a bit of MS
query, which is not the best of tools to use.

After the query runs with parameters you want to convert in into an
advanced query with this tool to edit a query directly in XL.to make it
do what you want:
http://homepages.paradise.net.nz/~robree/excel/queryeditor.html
By now the query should look OK and run in XL.

If you still need extra stuff *then* you use VBA to manipulate the
existing Query you made.

DM Unseen