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]

See answer to your multipost of this question in programming.

--
Regards,
Tom Ogilvy

"dmplacebo" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> dmplacebo
> ------------------------------------------------------------------------
> dmplacebo's Profile:
http://www.excelforum.com/member.php...o&userid=25095
> View this thread: http://www.excelforum.com/showthread...hreadid=386071
>

[virender singh]

please try to strin.instead of using sql server name in connection string
use IP address of the machine, i think you will get the solution

regards

puede ser utilizar IP demachina en connection string , deja te el nombre de servidor e connection string
qreo que es funciona
saludos


g connection in