becca51178
Board Regular
- Joined
- Feb 19, 2012
- Messages
- 64
I have tried everything and I cannot get this code to work. Also, I am working in Excel and Access 2010
I have been struggling with this for a few days now.
I have an access database I use once a week. I have a list of orders in an excel spreadsheet. I want to be able to delete the orders in the access db from the previous week and append the new week's data.
Basically, a code that
1st) deletes records in an access table
2nd) copies from a named range in excel
3rd) appends the named range from Excel back to the same table in Access
I was able to put together a code that will delete the values with no issues, but the copying from excel and inserting into access just won't work!
Help please!
Here is the delete code that works fine:
Code:
Sub paste_access()
Dim cnn As ADODB.Connection
Dim dbCommand As ADODB.Command
Dim rs As ADODB.Recordset
Dim dbFileName As String
Dim dbtableName As String
Dim Rng As Range
Dim strSQL As String
dbFileName = "C:\Users\rwelch\Desktop\Access Databases\WasteData.accdb"
dbtableName = "LookupOrders"
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0;"
.ConnectionString = "Data Source=" & dbFileName & ";"
.Open
End With
Set dbCommand = New ADODB.Command
dbCommand.CommandText = "INSERT INTO LookupOrders SELECT * FROM [Open_Order] IN '" _
& ThisWorkbook.FullName
Set dbCommand.ActiveConnection = cnn
dbCommand.Execute
Set dbCommand = Nothing
Set dbConnection = Nothing
End Sub
I have been struggling with this for a few days now.
I have an access database I use once a week. I have a list of orders in an excel spreadsheet. I want to be able to delete the orders in the access db from the previous week and append the new week's data.
Basically, a code that
1st) deletes records in an access table
2nd) copies from a named range in excel
3rd) appends the named range from Excel back to the same table in Access
I was able to put together a code that will delete the values with no issues, but the copying from excel and inserting into access just won't work!
Help please!
Here is the delete code that works fine:
Code:
Dim cnn As ADODB.Connection
Dim dbCommand As ADODB.Command
Dim rs As ADODB.Recordset
Dim dbFileName As String
Dim dbtableName As String
Dim Rng As Range
Dim strSQL As String
dbFileName = "C:\Users\rwelch\Desktop\Access Databases\WasteData.accdb"
dbtableName = "LookupOrders"
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0;"
.ConnectionString = "Data Source=" & dbFileName & ";"
.Open
End With
Set dbCommand = New ADODB.Command
dbCommand.CommandText = "DELETE * from LookupOrders"
Set dbCommand.ActiveConnection = cnn
dbCommand.Execute
Set dbCommand = Nothing
Set dbConnection = Nothing
Set cnn = Nothing
end Sub