ExcelBanter - Deleting Records in a table

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Deleting Records in a table (https://www.excelbanter.com/excel-discussion-misc-queries/59627-deleting-records-table.html)

Deleting Records in a table

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?

Deleting Records in a table

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?

Deleting Records in a table

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?

Deleting Records in a table

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?

Deleting Records in a table

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?

Deleting Records in a table

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?