It can be troublesome to convert a text file into an Access database. It takes a lot of time to open the file for sequential access and create new records using the AddNew method. Instead, use the Text ISAM driver and SQL to do the job for you. First, create a SCHEMA.INI file for the text file and place it in the same directory as the text file. Use this code to convert the database:
Dim db As Database, tbl as TableDefSet db = DBEngine.CreateDatabase(App.Path & _ "mymdb.mdb", dbLangGeneral, dbVersion_0)Set tbl = db.CreateTableDef("Temp")tbl.Connect = "Text;database=c:vbpjdata"tbl.SourceTableName = "Customer#txt"db.TableDefs.Append tbldb.Execute "Select Temp.* into NewTable from Temp"db.TableDefs.Delete tbl.Namedb.CloseSet tbl = NothingSet db = Nothing
Now, you only need to create indexes. You can use this method to convert text files in excess of 100,000 records in a few seconds.