Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 29th, 2006, 06:46 AM
Registered User
Join Date: Sep 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default TransferText Ignores Index in Import Specification

When I import a text file by using the DoCmd.TransferText method with an import specification that applies an index with no replications to a field, the resulting table does not have the index and contains replications. However, when I import the text file manually using the same import specification, the resulting table contains the index. Has anyone else experienced this problem and is there a way around it so that I can programmatically import a text file to have an index field with no replications? Thanks.
Old September 29th, 2006, 05:21 PM
Authorized User
Join Date: Jul 2004
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts

There might be an easier way, but I can give you one way around it - Just import the table you like (lets say you name the new table "Customers").

'Run a distinct make table query from customers
Docmd.RunSQL "SELECT DISTINCT Field1, Field2, Field3 " _
& "into Customer_Unique from Customers"

'delete your Customer Table with Dups
CurrentDb.Execute "DROP TABLE Customers;"

'Add an index to your new table named Customer_Unique if you like
CurrentDb.Execute "CREATE UNIQUE INDEX CustID ON Customer_Unique(Field1) WITH DISALLOW NULL;"

Here you will have a Customer_Unique table "indexed" with unique records.

Similar Threads
Thread Thread Starter Forum Replies Last Post
TransferText Decimals Issue Pavesa Access 0 April 23rd, 2008 06:48 PM
DTS ignores a 2nd ext when loading a txt file mmusleh SQL Server 2000 5 July 24th, 2007 01:53 PM
doCmd.TransferText d_olivastro VB Databases Basics 1 March 31st, 2006 12:06 AM
TransferText method sc-prgmr Access VBA 0 October 12th, 2005 09:46 AM
transfertext and dates choatec Access 2 February 24th, 2004 09:06 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.