I wrote a simple script using the Jet 4.0 Provider to assist in removing duplicates from a standard ASCII file, while keepng the original line number intact ( hence I could not use a DISTINCT clause ). The SQL statements follow:
; import the text file
'SELECT * INTO [line] FROM [input.txt] IN "" [TEXT;DATABASE=c:\temp]'
;add a line counter
'ALTER TABLE line ADD linecount COUNTER'
;create a temp table with a constraint
"CREATE TABLE temp (line TEXT (50),linecount COUNTER );"
'CREATE UNIQUE INDEX temp ON temp (line)'
'INSERT INTO temp (line,linecount) SELECT * FROM line;'
"SELECT line INTO [Text;DATABASE=c:\temp].[output.txt] FROM temp ORDER BY linecount"
I get no errors, but both the output file and temp table are blank.
If I open Access, create a query and type in the INSERT statment above, it work fine, but Access forces you to confirm 2 warning screens saying it cannot apend all records.
I was wondering if this could be part of the problem and perhaps I need to add something to the script to turn the warnings off.