SELECT INTO syntax error

[rss0213]

Hi. I'm getting a syntax error in my vbscript trying to set a SQL variable to the following string value:

strSQL = "SELECT * INTO tblPaymentDetail FROM OPENDATASOURCE(Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Documents and Settings\apprss\Desktop\Purchasing Work\PaymentFiles\PaymentExport\Payment\PaymentDetailExport.csv;" & _
"Persist Security Info=False;" & _
"Extended Properties='text;HDR=Yes;FMT=Delimited'"
cn.Execute strSQL

Does anyone see the syntax error here?


Thanks!
Scott

 

[PWise]

OPENDATASOURCE???
is that not a sqlserver command

 

[rss0213]

I don't think so - try google "Access OPENDATASOURCE".

Thanks!
Scott

 

[rss0213]

BTW... the syntax error is something in the FROM clause. (I know... this doesn't really narrow it down that much, but that's all the vbscript run-time is telling me.)

Thanks!
Scott

 

[PWise]

try


sqlstr="SELECT * INTO tblPaymentDetail FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0'" & _ "'Data Source=C:\Documents and Settings\apprss\Desktop\Purchasing Work\PaymentFiles\PaymentExport\Payment\PaymentDetailExport.csv;" & _ "Persist Security Info=False;" & _ Extended Properties=""text;HDR=Yes;FMT=Delimited""')dt

 

[rss0213]

I tried this:

strSQL = "SELECT * INTO tblPaymentDetail FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0'" & _
"'Data Source=C:\Documents and Settings\apprss\Desktop\Purchasing Work\PaymentFiles\PaymentExport\Payment\PaymentDetailExport.csv;" & _
"Persist Security Info=False;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited""')dt"

It's basically the same thing, except I added a double-quote in front of Extended..." because this parm wasn't enclosed in double-quotes.

Thanks!
Scott

 

[rss0213]

Oh, and I got the same error - "syntax error in FROM clause.."

Thanks!
Scott

 

[PWise]

Are you trying to import into sqlserver or in to access?

 

[rss0213]

Import into Access from CSV file. I saw a blurb about this on another forum, so I thought I should be able to do it. Maybe it's not possible this way. Maybe I need to use INSERT INTO? I was just trying to see if I could do it without typing out all the fields (this was supposed to just import the entire file), because there are many in this CSV file.

Thanks!
Scott

 

[PHV]

Why not simply use the DoCmd.TransferText method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rss0213]

Ya, I'll use that.

Thanks!
Scott

 

[rss0213]

Great. Now it's telling me my import specification doesn't exist. I copied and pasted the import spec into my vbscript:

varPmtDetail = sCurPath & "\PaymentExport\Payment\PaymentDetailExport.csv"
Dim appAccess
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "C:\Documents and Settings\apprss\Desktop\Purchasing Work\PurchasingP2P.accdb"

If (objFSO.FileExists(varPmtDetail)) Then
appAccess.DoCmd.TransferText acImportDelim, "Import-PaymentDetailExport", "tblPaymentDetail", varPmtDetail
End If

Thanks!
Scott