Linking to .CSV file

[Pauldohert]

I am linking to a csv file with the following

  Me!JobHolder = mID
  DoCmd.DeleteObject acTable, "TempTPSCSV"
  On Error GoTo Err_Handler
  DoCmd.TransferText acLinkDelim, , "TempTPSCSV", Me!FileSelected, True

Which works fine - except that I intermitently get error 3501 - ie file is already open or user needs permissions. Neither or which seems to be true - as on subsequent tries it eventually works OK.

THis seems to happen when file has circe 50k records , not when it has 100 records.

Any ideas whats causing this - and how to get round it.

Ta

 

[Moniker]

Is there a reason you can't just import this table? All the Transfer actions (TransferText, TransferDatabase, TransferSpreadsheet) are pretty unreliable except under very specific conditions. I know that there are some unlisted limitations, and these functions typically have memory leaks as well.

When you get the error, can you go to Task Manager (Ctrl-Alt-Del, then Task Manager) and see if anything else would be hitting that table?

If you must link to this for whatever reason, have you tried it with any other methods? And, are you sure you're getting all 50,000 records? I'm betting you may not be.

 

[dallr]

I would recommend that you actually import the data. As Moniker mentioned linking to these types of files can cause problems. From personal experience i have seen where linking to a text file caused just one (1) record to not to show. When looping through the recordset the count would have been one less than the text file.

After importing the data from the text file all was showing.

I suggest this might be your best option.

Dallr

 

[Pauldohert]

Thanks both of you, have changed it to import the data rather than link.

However the error is the same - seems like is OK first time the (large) csv is used in an access session. Then becomes unreliable to say the least?!

Any ideas?

Ta

 

[Moniker]

If it's imported, then it will act like any other table in Access. Do we have a corruption issue here? Did you compact/repair after the import? What, specifically, is happening?

 

[Pauldohert]

I can only be as specific as Micropenis tells me - error 3501?

I may try normalisation.