700000 records in Access database

[Barrow66]

I have 700000 records in a text file and want to load it in a database table.Can someone please help me go about writing a code to load these records without waiting for 3hours to load in Access table.I am using rs.AddNew to add records to Access database.
Any other solutions

 

[grnzbra]

Is it delimited or fixed field format?

 

[Guest_imported]

NB. This answer was given in "Importing CSV Files" thread. Please read this for further details.

If this does not help, let me know. I would probably use a test database before executing any code that you see on any forum. Its easier to resolve problems that way.

'-------------------

OK, however I would make a point of ensuring that the same files don't get imported twice (otherwise you will have duplicate data and/or errors)

before I give you the code, I will firstly let you know what it does.

If you put in :

Call MyImportFnctn("C:\My Documents&quot

Then it will look through "C:\my documents" for all csv files. It will then create tables of the same name, so, if your "C:\My Documents" folder contains :

melisadata.csv
noreendata.csv

then two tables would appear in your database, one called "melisadata", and the other called noreendata. Each containing its own data from the csv files respectively



here is the code :

Public Sub MyImportFnctn(Byval Pathnm as string)

'N.B. Ensure "Path" points to folder where csvs will be located. Eg. C:\My Documents

'filesystem object used for file I/O
Set fs = Application.FileSearch
With fs
.LookIn = Pathnm
.FileName = "*.csv"
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
'if they are found, then import into table
Docmd.TransferText acImportDelim, , Left$(.FoundFiles(i), Len(.FoundFiles(i)) - 4), .FoundFiles(i) , False
msgbox("Imported : " & .FoundFiles(i))

Next i
Else
MsgBox "There were no csv files found."
End If
End With

Msgbox("Done&quot

End Sub

'----------------------

Hope your happy with that.

Regards,
Mr Big.

 

[Barrow66]

Delimited

 

[Guest_imported]

Hi. Did my code work? I would consider using TransferText method, as the AddNew method is a long process. The system needs to run over your code instead of its own. Thats one reason why it is not running faster. Please let me know if my code helped.

Regards,
Mr Big

 

[Barrow66]

Mr.Big what reference library should I use to get Application.FileSearch

 

[Barrow66]

Thanks Mr.Big.

 

[Guest_imported]

Hi Barrow66

You should be able to use the application object from the default references. Did it work for you?

Regards,
Mr Big.

 

[Barrow66]

Yes. Thanks.

 

[hsitz]

A star for Mr. Big! I'll keep that method in mind for whenever I have to deal with the same problem. Thanks.

-- Herb