access97 runtime error 3075 missing operator

[JMCN]

I have an ftp program that i inherited and i tried to modify it to ftp
over my file however, i receive an error message :

run-time error '3075
syntax error (missing operator) in query expression " Get
PROD6-12-22-03-299-2
c:temp\'PROD6FTCERT.txt 0 As Expr1;'.

i'm clueless why i have a missing operator? if anyone has a
suggestions that would be greatly appreciated :)
thanks in advance - jung

here is part of the code:

If Me.chkSeriesInt = -1 Then
myreport = "'PROD6"
saveasname = "Prod6"
myshortname = rst.Fields("DataBaseLongName") 'Database name
myftpdir = rst.Fields("DatabaseFTPAddress") 'Location of files
on FTP server

'change FTP directory to correct location
'strSQL = "INSERT INTO [tblFTPText] ( [Text String] ) SELECT
'CD & myftpdir AS Expr1;"
strSQL = "INSERT INTO [tblFTPText] ( [Text String] ) SELECT
'CD " & myftpdir & "]' AS Expr1;"

Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute

If myshortname = "USBCERT" Then
myfiledate = Me.txtusbcertfiledateSeriesInt
myopnum = Me.txtusbcertopnumseriesint
myfilenumber = Me.txtusbcertfilenumSeriesInt
'ElseIf myshortname = "USBMABS" Then
'myfiledate = Me.txtusbmabsfiledateActive
'myopnum = me.txtusbmabsopnumseriesint
'myfilenumber = Me.txtusbmabsfilenumActive
Else '"FTCERT"
myfiledate = Me.txtftcertfiledateSeriesInt
myopnum = Me.txtftcertopnumseriesint
myfilenumber = Me.txtftcertfilenumSeriesInt
End If

strSQL = "INSERT INTO [tblFTPText] ( [Text String] ) SELECT
'GET PROD6-" & Format(myfiledate, "mm-dd-yy") & "-" & myopnum & "-" &
myfilenumber ' & "-" & myreport
strSQL = strSQL & " " & Me.txtSavedDataLocation & myreport &
myshortname & ".txt 0'"
strSQL = strSQL & " AS Expr1;"

'i receive the error message on this line
Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute
End If

[Douglas J. Steele]

It looks as though you're trying to issue FTP commands as SQL. You can't do
that.

There are 2 different approaches to FTP illustrated at "The Access Web"

http://www.mvps.org/access/modules/mdl0015.htm shows you how to create a
script file and call it from Access.

http://www.mvps.org/access/modules/mdl0037.htm shows you how to use the
Internet Data Transfer library.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"JMCN" <pi******@yahoo.fr> wrote in message
news:27**************************@posting.google.c om...

I have an ftp program that i inherited and i tried to modify it to ftp
over my file however, i receive an error message :

run-time error '3075
syntax error (missing operator) in query expression " Get
PROD6-12-22-03-299-2
c:temp\'PROD6FTCERT.txt 0 As Expr1;'.

i'm clueless why i have a missing operator? if anyone has a
suggestions that would be greatly appreciated :)
thanks in advance - jung

here is part of the code:

If Me.chkSeriesInt = -1 Then
myreport = "'PROD6"
saveasname = "Prod6"
myshortname = rst.Fields("DataBaseLongName") 'Database name
myftpdir = rst.Fields("DatabaseFTPAddress") 'Location of files
on FTP server

'change FTP directory to correct location
'strSQL = "INSERT INTO [tblFTPText] ( [Text String] ) SELECT
'CD & myftpdir AS Expr1;"
strSQL = "INSERT INTO [tblFTPText] ( [Text String] ) SELECT
'CD " & myftpdir & "]' AS Expr1;"

Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute

If myshortname = "USBCERT" Then
myfiledate = Me.txtusbcertfiledateSeriesInt
myopnum = Me.txtusbcertopnumseriesint
myfilenumber = Me.txtusbcertfilenumSeriesInt
'ElseIf myshortname = "USBMABS" Then
'myfiledate = Me.txtusbmabsfiledateActive
'myopnum = me.txtusbmabsopnumseriesint
'myfilenumber = Me.txtusbmabsfilenumActive
Else '"FTCERT"
myfiledate = Me.txtftcertfiledateSeriesInt
myopnum = Me.txtftcertopnumseriesint
myfilenumber = Me.txtftcertfilenumSeriesInt
End If

strSQL = "INSERT INTO [tblFTPText] ( [Text String] ) SELECT
'GET PROD6-" & Format(myfiledate, "mm-dd-yy") & "-" & myopnum & "-" &
myfilenumber ' & "-" & myreport
strSQL = strSQL & " " & Me.txtSavedDataLocation & myreport &
myshortname & ".txt 0'"
strSQL = strSQL & " AS Expr1;"

'i receive the error message on this line
Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute
End If