Help with Excel SQL Statements, Appending Excel Named Range to Access ADO

[becca51178]

I have tried everything and I cannot get this code to work. Also, I am working in Excel and Access 2010

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:

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

 

[xenou]

Moved to:
http://www.mrexcel.com/forum/showth...nts-Appending-Excel-Named-Range-to-Access-ADO