Hi Justin

That looks like three questions to me <bg

1) If I understand you correctly you are wanting to have the date (and
maybe time) that the data was loaded recorded in the database. If this
is the case then you do not need to add any columns to your excel sheet.
Add the extra column to your access table, give it a data type of
Date/Time and use the Now() or Date functions in your upload query.
Now() will give you date and time, Date will give you just the date. I
have used Now() in the example below. If you want just the date replace
Now() with Date.

2.) Assuming your comment is in Column M (which I think it is from
earlier posts) then the example below will only load rows where the
value in M is not empty.

3.) Finally this example deletes all the rows which have just been
loaded into the database.

Sub loadData()
Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long
Dim delRows As Range

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=F:\System Analyst\Logistics\Logistics CDI.mdb"
i = Cells(Rows.Count, 1).End(xlUp).Row

For r = 12 To i '<<change start row
If Range("M" & r).Value < Empty Then
SQLStr = "INSERT INTO [test] " _
& "Values ('" & Range("A" & r).Value & "', '" _
& Range("B" & r).Value & "', '" _
& Range("C" & r).Value & "', '" & Range("D" & r).Value _
& "', '" & Range("E" & r).Value & "', '" _
& Range("F" & r).Value & "', '" & Range("G" & r).Value _
& "', '" & Range("H" & r).Value & "', '" _
& Range("I" & r).Value & "', '" & Range("J" & r).Value _
& "', '" & Range("K" & r).Value & "', '" _
& Range("L" & r).Value & "', '" & Range("M" & r).Value _
& "', '" & Range("N" & r).Value & "', '" & Now() & "')"

MyCn.Execute SQLStr
If delRows Is Nothing Then
Set delRows = Range("A" & r)
Else
Set delRows = Union(delRows, Range("A" & r))
End If
End If
Next r

delRows.EntireRow.Delete

MsgBox "Data has been uploaded to CDI ERROR DATA"
MyCn.Close
Set MyCn = Nothing

End Sub

Hope this helps
Rowan

Justin wrote:
Once again, it works perfectly
Last question.
I am uploading column a-n to a table in access. now the table has the same
headers but I need to have an add'l column (to be when the file was
uploaded). Now i tried to adjust it on the xcel sheet to add column 'O' but
it comes out as text. I tried to make that column in access a Date/Time
attribute but it will only accept text. What can i do so that I can make it a
date?

Also, is there a way that when you hit the upload button, it ONLY takes rows
that have a comment and leave the rest to be worked later on. and maybe
delete all records that was just upload so that it doesn't make a duplicate.

"Rowan Drummond" wrote: