Automate import of CSV file into Access DB table?

[mediasmyth]

I have a web site that uses a CSV file provided by a third party that I've imported into an ACCESS DB table. I'd like to automate the process of updating the ACCESS table using VBscript on ASP page.

Here's what I'd like the VBscript to do...

***********
Check for presence of the CSV file
If present then... (if not present Exit)
Compare file date of CSV file with ACCESS DB table file date
If CSV file is newer then...
Import CSV data into ACCESS table (overwriting the existing table)
If CSV file date is the same or older - then Exit
Compact the ACCESS table
EXIT

***********
Notes & Questions:
1) VBscript should run automatically once a day. (say 2:00am)
2) The ACCESS table has Indexes on four fields. Will the Import preserve these?
3) Some Field lengths of CSV file are longer than the ACCESS table field length (i.e. 255 characters to 30 characters in ACCESS table). The ACCESS table fields are already set to the correct length & data type.
4) CSV file is fairly small (300 to 400 records)

Any help or divine guidance would be greatly appreciated.

- mediasmyth

 

[PHV]

You have to play with the Server.MapPath method, the FileSystemObject and either ADO, OLE DB or OLE Automation with "Access.Application".

 

[PhillipFoster]

'Update the ACCESS print log database
'
Sub UpdateDataBase( lfName )
Dim db, objTF, myRow
Set db = WScript.CreateObject("ADODB.Connection&quot
db.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\Grot\My Documents\FaxPrint.mdb" & ";"

Set objFSO = WScript.CreateObject("Scripting.FileSystemObject&quot
Set objTF = objFSO.OpenTextFile(lfName, 1, True)
do while not objTF.AtEndOfStream
myRow = Split(objTF.Readline, ",&quot
db.Execute Replace("INSERT INTO FaxPrintLog (pDate, pTime, pCLI, pFileName, pSize, pPages, pProcessed) VALUES (" _
& "#" & myRow(0) & "#," _
& "#" & myRow(1) & "#," _
& "#" & myRow(2) & "#," _
& "#" & myRow(3) & "#," _
& "#" & myRow(4) & "#," _
& "#" & myRow(5) & "#," _
& "#" & myRow(6) & "#)","#",chr(34))
loop
objTf.Close
db.Close
Set objTF = Nothing
Set db = Nothing
End Sub