Thanks for the feedback....I'm glad I could help.

***********
Regards,
Ron


"Secret Squirrel" wrote:

Yes that works perfectly! Thank you very much!

"Ron Coderre" wrote:

Thanks....the code helps:

Before your run your INSERT query....do this:

SQLstr = "DELETE * FROM tblOctExt;"
MyCn.Execute SQLstr

That should do it.

Caveat: Make sure tblOctExt is the table you want purged, because there is
no undo.

Does that help?

***********
Regards,
Ron


"Secret Squirrel" wrote:

I haven't tried that yet. Here is my code. Let me know what you think will
work best. I have some code at the end but I don't think it's working
correctly.

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long
Dim delRows As Range
Dim mRow As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC
QA mdb\BC Quality Action
Database.mdb;SystemDB=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\Sys\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=*****;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

i = Cells(Rows.Count, 1).End(xlUp).Row

'from row 10, or whatever you want to start
For mRow = 10 To ActiveSheet.UsedRange.Rows.Count

'make sure there is data in that row by testing a colum you know will
have data
If Len(Cells(mRow, 10)) 0 Then

For r = 10 To i '<<Change Start Row
SQLStr = "INSERT INTO [tblOctExt] (RMA, DateNotified, DateDispositionMade,
Branch, [WO#], Customer, [PO#], CustomerPN, Qty, UnitOfMeasure, Operator,
DiscCode, DiscrepancyDescription, DispCode, TotalCost)" _
& " SELECT '" & (Cells(mRow, 1)) & "','" & (Cells(mRow, 2)) & "','" &
(Cells(mRow, 3)) & "','" & (Cells(mRow, 4)) _
& "','" & (Cells(mRow, 5)) & "','" & (Cells(mRow, 6)) & "','" &
(Cells(mRow, 7)) & "','" & (Cells(mRow, 8)) _
& "','" & (Cells(mRow, 9)) & "','" & (Cells(mRow, 10)) & "','" &
(Cells(mRow, 11)) & "','" & (Cells(mRow, 12)) _
& "','" & (Cells(mRow, 13)) & "','" & (Cells(mRow, 14)) & "','" &
(Cells(mRow, 15)) & "';"

' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

Next r

Debug.Print SQLStr

MyCn.Execute SQLStr

End If

Next mRow

If delRows Is Nothing Then
Set delRows = Range("A" & r)
Else
Set delRows = Union(delRows, Range("A" & r))
End If

delRows.EntireRow.Delete

MsgBox "Data has been uploaded to tblOctExt"
MyCn.Close
Set MyCn = Nothing

End Sub


"Ron Coderre" wrote:

Without your actual VBA code, I can only take a guess:

Have you tried this SQL?:
DELETE * FROM your_table;

Does that help?

***********
Regards,
Ron


"Secret Squirrel" wrote:

I have a macro in excel that I use to upload data into a table in Access.
What I want to do is have it delete any records in that table right before it
uploads the new data. Can anyone help me with the code for this function?