3 replies to this topic

[reed]

Instead of inserting 170000 records into my database individually, I decided to try 100 at once. (Would it be beneficial to do even more at once? Say...1000?)

I keep getting the "Missing semicolon (;) at end of SQL statement" error when it gets to ExecuteNonQuery();, but there IS a semicolon at the end.

What's going on?

My syntax is "INSERT INTO [TABLE](field1,field2,...,fieldn) VALUES (1,2,...,n),(1,2,...,n),...,(1,2,...,n);"



Thanks in advance.

[WingedPanther]

Jet may not support that syntax, and only allow one record to be inserted at a time.

[reed]

WingedPanther said:

Jet may not support that syntax, and only allow one record to be inserted at a time.

That's not what I wanted to hear. Before I build too much upon this Jet/Access thing I have going...Is there a more efficient/faster way of doing my database transactions?? Right now, it's coming out to about 2 1/2 minutes submitting 100,000 records and hogging 13% of my i7 processor along with 100Mb of ram...not ideal if I want to avoid locking up older (less capable) systems.

I've heard that using SQL is slow...so what's better?

[WingedPanther]

Access is generally viewed as a "toy" database by those who work with databases on a regular basis. I would suggest using something more robust, such as MySQL, Firebird, or one of the paid databases.