access 97: runtime Error 3146, form, pass-through query

[JMCN]

hi -

i have created a passthrough query and have it automatically connect
to the odbc and then on a separate form, i have the user type in the
date parameters. however when i run the following code on my form, i
receive an error that the odbc connection had failed "ODBC – call
failed. (Error 3146)", even though in the pass-through query i pasted
the properties odbc connect str to the qdf.connect code below. does
anyone know why i have this error message?

thanks in advance, jung.

Private Sub btnLiveWire_Dbl Click(Cancel As Integer)

Dim db As Database, qdf As QueryDef, sqltxt As String
Dim parm1 As String
Set db = DBEngine.Worksp aces(0).Databas es(0)

Set qdf = db.CreateQueryD ef("")

qdf.ReturnsReco rds = False

qdf.Connect = "ODBC;DSN=LiveW ire SQL
Server;Descript ion=LiveWire;UI D=JXMCCA3;DATAB ASE=LiveWire;Ne twork=DBMSSOCN; Trusted_Connect ion=Yes;UseProc ForPrepare=0;An siNPW=No"

parm1 = "Me!MyTextB ox" 'user changes the parameter date
sqltxt = "MyStoredProced ure parm1"

qdf.SQL = sqltxt
qdf.Execute 'error message "ODBC – call failed. (Error 3146)", "
qdf.Close
End Sub

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Should be:

sqltxt = "MyStoredProced ure '" & Me!MyTextBox & "'"

This supposes the parameter is a text string. If it is a numeric
value you don't have to include the single-quotes:

sqltxt = "MyStoredProced ure " & Me!MyTextBox

HTH,

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP7lvuYechKq OuFEgEQLOeACgmr UGfjW1X8Z8L7Fx0 Vck4dixAT4An0Jn
Jpur3sY0EzY+juu MJaRPL6TN
=9Z5x
-----END PGP SIGNATURE-----

JMCN wrote:

hi -

i have created a passthrough query and have it automatically connect
to the odbc and then on a separate form, i have the user type in the
date parameters. however when i run the following code on my form, i
receive an error that the odbc connection had failed "ODBC – call
failed. (Error 3146)", even though in the pass-through query i pasted
the properties odbc connect str to the qdf.connect code below. does
anyone know why i have this error message?

thanks in advance, jung.

Private Sub btnLiveWire_Dbl Click(Cancel As Integer)

Dim db As Database, qdf As QueryDef, sqltxt As String
Dim parm1 As String
Set db = DBEngine.Worksp aces(0).Databas es(0)

Set qdf = db.CreateQueryD ef("")

qdf.ReturnsReco rds = False

qdf.Connect = "ODBC;DSN=LiveW ire SQL
Server;Descript ion=LiveWire;UI D=JXMCCA3;DATAB ASE=LiveWire;Ne twork=DBMSSOCN; Trusted_Connect ion=Yes;UseProc ForPrepare=0;An siNPW=No"

parm1 = "Me!MyTextB ox" 'user changes the parameter date
sqltxt = "MyStoredProced ure parm1"

qdf.SQL = sqltxt
qdf.Execute 'error message "ODBC – call failed. (Error 3146)", "
qdf.Close
End Sub

[JMCN]

MGFoster <[e-mail] > wrote in message news:<0d******* **********@news read1.news.pas. earthlink.net>. ..

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Should be:

sqltxt = "MyStoredProced ure '" & Me!MyTextBox & "'"

This supposes the parameter is a text string. If it is a numeric
value you don't have to include the single-quotes:

sqltxt = "MyStoredProced ure " & Me!MyTextBox

HTH,

thanks again, it helped!