Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Deleting Records in a table
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Secret Squirrel
 
Posts: n/a
Default 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?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default 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?
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
Secret Squirrel
 
Posts: n/a
Default 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?
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default 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?
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
Secret Squirrel
 
Posts: n/a
Default 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?
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default 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?
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting specific records [email protected] Excel Discussion (Misc queries) 6 June 22nd 05 11:35 PM
Deleting both duplicate records ruth_austin50 Excel Discussion (Misc queries) 1 June 22nd 05 05:06 PM
Deleting specific records from a column [email protected] Excel Discussion (Misc queries) 2 June 16th 05 04:02 AM
deleting duplicate records in a mail merge Mimi Excel Discussion (Misc queries) 1 April 7th 05 05:55 PM
How to delete duplicate records when I merge two lists (deleting . rinks Excel Worksheet Functions 10 December 11th 04 01:03 AM


All times are GMT +1. The time now is 10:07 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement