Excel VBA how to make multiple records in a database

Question

I need to fill the database with thousands of records daily, but my code currently manually inserts each one into the database one at a time.

Do While lngSQLLoop < lngCurrentRecord
    lngSQLLoop = lngSQLLoop + 1
    sql = "INSERT INTO db (key1, key2) VALUES ('value1', 'value2');"
    result = bInsertIntoDatabase(sql, True)
    If result = false Then lngFailed = lngFailed + 1
Loop

This works, but takes about 5 seconds for every 100 entries. Would there be a more efficient way to put this in a database? I tried

INSERT INTO db (key1, key2) VALUES ('value1-1', 'value2-1'), ('value1-2', 'value2-2'), ('value1-3', 'value2-3') ;

but this fails with a missing colon; a mistake suggesting that he does not like the meanings listed. Is there a way VBA will do this?

Answer 1

(), () SQL Server 2008.

: , SQL bInsertIntoDatabase.

, , .

, (, ), . :

• ( , )
• WHERE, .
• , .

Answer 2

(, ODBC), , :

INSERT INTO targetDBtable (key1, key2)
VALUES (SELECT key1, key2 FROM sourceDBtable);

Answer 3

.AddNew .Update : 0,25 , 10000 1.25 10000 10000 .

Answer 4

CSV, AccessTextTextText ( DoCmd) Access . CSV .

Even if you use code from Excel, you can still execute the TransferText method in Access through Automation.