Hi all,
i need a way inserting lots of data into an ms access database and as much as fast as possible, (more than 500000 rows...) im reading the data from a txt file... and ive tried using dataset and datatable but it takes lots of resources from the pc (to insert all the data into the memory...) and the direct approach is very slow (oledbcommand.excutenonquery() for every row ) is there a faster way ??

btw im using c#,

thanks very much for the help, im really stuck here...
Guy.

You should start by learning about databases. Then learning how .NET interacts with them.

There is absolutely no reason to use a DataSet to move a text file into Access. Unless you enjoy waiting for code to run.

If you need to process a huge text file you should read it line-by-line instead of bringing it all into memory.

Also, Access can not handle huge tables. If you really plan on having any table with half a million rows you should look into a "real" database.

Access is a real database, and I've worked in Access applications that were responsible for storing more than a million rows in a table, with several other tables. It can handle up to 2 GB per file, and substantially more with linked tables.

At the end of the day, if you don't know how to make a good application with Access, you'll be lost with SQL Server or Oracle.

What John said. Access is fine for large applications and can handle millions of rows in a table without issue.

Yes access is a database. Without any doubt it is having a lot of disadvantages. You will lack programmability at backend in access. And so it is good to have programmable database at backend for many reasons if you are going for a big database with many millions of records accessed simultaneously.

I love how the "we hate Access" bloc always comes out. Sure there are disadvantages, just like there are with SQL Server, Oracle, MySQL, or XML dumps. That's the problem with having free will - being able to make choices, is that there are always advantages and disadvantages.

You can connect to an Access database with Old School ASP or with ASP.NET, or with raw C++ or just about any other programming language. But you can also program against an Access database by using the tools inside Access: it allows for programming, user interface, reporting, and had hoc analysis. Not only can you do all the normal back-end stuff, but you have more options than you would with SQL Server.

The major downside to Access is it can only support 255 concurrent database connections. And does better with less. If you run a web site against it, you won't need this many; connection pooling typically lets you multiplex five database connections to server a hundred web clients. If that's not a problem, the rich programming model makes Access cheaper for the client, on top of being easier.

The long and the short of it is if you understand how computers work, there's no reason to fear Access automatically. And to answer FunTik's question, loose the data set, load in the text file row-by-row, and insert these into Access.