upload csv to Access table

[kit_kalen]

I want to provide a way for my customer to upload an Excel or csv file (created in Excel) to the server and have the data within the csv create new records within an Access database table. I don't know ASP/VBscript very well, so am looking for a pre-made script or a snippet of code I could modify for my own purposes. Can anybody steer me in a direction?

[sun818]

Will the CSV only contain "new records" or will be a CSV file contain "all records"? If it contains all the records for table, you could easily import the CSV file as a linked table into Access. Then you can perform your queries against the linked table. If you have a lot of records, the processing will be slow. But a few hundreds, no problem.

[kit_kalen]

The csv will contain only new records so each upload will add records to the table. Each csv will probably hold up to a few hundred records. (Eventually, we'll be moving this to a SQLServer.)

[WebJoe]

I'd still go the way sun818 proposed, as I find it easier to maintain just one database connection. This way the csv-file will "feel" just like another table.

The code would be something like

On Error Resume Next

Dim sProvider, sDBPath
Dim objConnection, objRecordset
Dim bTest
Dim sSQL

' Init all vars, set parameters
sProvider = "Microsoft.Jet.OLEDB.4.0"
sDBPath = "/db-folder/DBWithCSVLinked.mdb"

' Open database
bTest = bIsDBOpen(objConnection, sProvider, sDBPath)

If bTest Then
 sSQL = "INSERT INTO TargetTable "
 sSQL = sSQL & "(Field1, Field2) "
 sSQL = sSQL & "SELECT Field1, Field2 "
 sSQL = sSQL & "FROM CSVTable"
End If

objConnection.Execute(sSQL) 

If Err.Number = 0 Then
 Response.Write "OK"
Else
 Response.Write "Failed (" & Err.Number & ") " & Err.Description
End If

Function bIsDBOpen(objConnection, sProvider, sDBPath)

 Dim sConnectionString

 bIsDBOpen = False

 sConnectionString = "Provider=" & sProvider
 sConnectionString = sConnectionString & ";Data Source=" & Server.MapPath(sDBPath)

 Set objConnection = Server.CreateObject("ADODB.Connection") 
 objConnection.Open sConnectionString

 If Err.Number = 0 Then
 bIsDBOpen = True
 Else
 bIsDBOpen = False
 End If

End Function

[kit_kalen]

Thanks a bunch. I'll try this.