Solved

Failure of INSERT INTO...

Posted on 2002-06-08
3
211 Views
Last Modified: 2012-06-27
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.

stan
0
Comment
Question by:stanl
3 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 7064561
Hi stanl,

Why are you using a script?
You can define a linked table in access to your textfile and issue all needed queries on that linked table.

Removing duplicates could be done by a GroupBy query to select the first line of each unique value and followed by a delete of the non-matching.

Nic;o)
0
 
LVL 9

Accepted Solution

by:
ornicar earned 200 total points
ID: 7064991
If you want to turn the messages off, just use:

DoCmd.SetWarnings False

And to turn them on again:

DoCmd.SetWarnings True

The duplicates will not be added with no message. If this is ok for you, that's fine. I feel it would be better to import into an intermediate table allowing duplicates, then run a check-duplicates query to show these records. Very often it's better to check why there are duplicates and what there are. Just a guess.

0
 

Author Comment

by:stanl
ID: 7065160
As it turned out, if I changed my original OLE Object from the Jet OLEDB Provider to Access.Application the script worked fine.  Since the DoCmd is not part of the Provider Object, that got me thinking about making that change ( but there was never a need to even touch the SetWarnings property.

I agree about holding the duplicates, but the text files are created by 'cut-and-paste' which is subject to slight error.



0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question