VBscript Access query problem

[Jason83]

I'm trying to have a VBscript query an access database I have and copy the entries that have a 4 digit ProdID (there are ProdIDs with letters I'm looking for the ones with only 4 digits) into a new table. My query works perfect from inside of Access but when I take it and move it to a VBscript I get a blank table (it creates the TEMP table but it is empty. I believe the problem is with my WHERE statement. If I remove the WHERE statement from the VBscript it copies all of the entries from the TestDB table to the TEMP table. I'm running this script on a Windows XP Professional workstation with Office 2010 Professional Plus. Here is my code:

DIM objConn, SQL1
Set objConn=CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= TestDB.accdb"
SQL1 = "SELECT * INTO TEMP " &_
"FROM TestDB " &_
"WHERE (TestDB.ProdID Like '####');"
ObjConn.Execute SQL1
ObjConn.close
set ObjConn = nothing

Thanks!

 

[PHV]

You must use DAO to use the JetSQL wildcards.
ADO recognize only the ANSI SQL % and _

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