Updating Database from csv using ado and vbscript without recordset loop?

Question

I want to upload data in to an Access database from csv files using Vb Script & ADO so I don't have to open either Access or Excel.

So Far I have a working script below, but is there a better way to upload the data in one batch without having to create a record set and loop through each record?

I've created an example where I have 2 raw data files (csv) the file People.csv contains the fields 'PERSON' and 'COMPANY' and the other file contains 'COMPANY' and 'STATUS'. The database to be updated has one table 'tblPeople' with the Fields 'PERSON' and 'STATUS'.

Const ST_FILES = "C:\test\Files\"
Const ST_DB = "C:\test\db\mydb.accdb"

'Connection to Raw Files
Dim stCS: stCS = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ST_FILES & _
                ";Extended Properties=""text;HDR=YES;FMT=Delimited(,)"";"
Dim objCnFiles: set objCnFiles = CreateObject("ADODB.Connection"):objCnFiles.open stCS

'Connection to DB
stCS = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ST_DB
Dim objCnDb: set objCnDb = CreateObject("ADODB.Connection"):objCnDb.Open stCS

'Recordset of Raw Files
Dim objRS: set objRS = CreateObject("ADODB.RecordSet")
Dim stSQL: stSQL = "SELECT T1.PERSON, T2.STATUS " & _
                    "FROM People#csv T1 " & _
                    "INNER JOIN Company#csv T2 "& _
                    "ON T1.COMPANY = T2.COMPANY"
objRS.open stSQL, objCnFiles

'The part Below is the part I want to improve:
'Loops through the recordset inserting record into db line by line.
objRS.MoveFirst
Do until objRS.EOF
    stSQL = "INSERT INTO tblPeople (PERSON, STATUS) " & _
            "VALUES('" & objRS.Fields("PERSON") & "','" & _
            objRS.Fields("STATUS") & "')"

    objCnDb.Execute stSQL
    objRS.MoveNext
Loop

objRS.close
objCnFiles.close
objCnDb.close

WScript.echo "Done"
WScript.Quit

Edit: Solution Below using the solution provided by Ekkehard.Horner

Const ST_FILES = "C:\test\Files\"
Const ST_DB = "C:\test\db\mydb.accdb"

'Connection to Raw Files
Dim stCS: stCS = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ST_FILES & _
                ";Extended Properties=""text;HDR=YES;FMT=Delimited(,)"";"
Dim objCnFiles: set objCnFiles = CreateObject("ADODB.Connection"):objCnFiles.open stCS

Dim stSQL: stSQL = "INSERT INTO [tblPeople] IN '" & ST_DB & "'" &_
                    "SELECT T1.PERSON, T2.STATUS " & _
                    "FROM People#csv T1 " & _
                    "INNER JOIN Company#csv T2 "& _
                    "ON T1.COMPANY = T2.COMPANY"

objCnFiles.Execute stSQL
objCnFiles.close

WScript.echo "Done"
WScript.Quit

`INSERT` statements can apply multiple records in a single query, so you could build the `VALUES` section in the loop before execution. Also, instead of the loop you could use the `GetString()` method which can return the whole table as a text string and you can specify the column and row delimiters etc — , Jun 12 '13 at 07:50
I'm probably wrong on this but I thought with 'INSERT INTO ... VALUES ..." You could only insert one value at a time in Access. What would be the correct syntax? (I tried a couple of syntax's and couldn't get it too work). Also thanks for the GetString() method I didn't know it existed, looks very useful. — user2476561, Jun 13 '13 at 00:04

score 2 · Accepted Answer · answered Jun 12 '13 at 06:59

Use "INSERT/SELECT INTO IN 'External Database'". For your specific task (text -> mdb, your query) the statements would look like

Create new table:
SELECT T1.PERSON AS PERSON, T2.STATUS AS STATUS INTO [tblPeople] IN "P:\athto\your.mdb.mdb" FROM [People#csv] T1 INNER JOIN [Company#csv] T2 ON T1.COMPANY = T2.COMPANY

Append to existing table:
INSERT INTO [tblPeople] IN "P:\athto\your.mdb.mdb" SELECT T1.PERSON AS PERSON, T2.STATUS AS STATUS FROM [People#csv] T1 INNER JOIN [Company#csv] T2 ON T1.COMPANY = T2.COMPANY

Updating Database from csv using ado and vbscript without recordset loop?

1 Answers1

Linked