Excel to Access via vba

[David Evans]

Sub ExcelToAccess()

' exports data from the active worksheet to a table in an Access database

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim R As Long

Sheets("F2 Expense Sheet").Select
    ' connect to the Access database
    Set cn = New ADODB.Connection
   cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
        "Data Source= \\XXXX\1. Template\Fund Expenses\Fund Stuff.accdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "Expenses", cn, adOpenKeyset, adLockOptimistic
    ' all records in a table
    R = 6 ' the start row in the worksheet
    Do While Len(Range("A" & R).Text) > 0
    Debug.Print R
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("Fund Name") = Range("A" & R).Value
            .Fields("Share Type") = Worksheets("F2 Expense Sheet").Range("B2").Value
            .Fields("Fund Number") = Range("B" & R).Value
            .Fields("Management Fees") = Range("D" & R).Value
            .Fields("Other Expenses") = Range("E" & R).Value
            .Fields("Fee Waiver Amount") = Range("H" & R).Value
            .Fields("Gross Fees") = Range("F" & R).Value
            .Fields("Net Fees") = Range("I" & R).Value
            .Fields("As of Date") = Worksheets("F2 Expense Sheet").Range("B3").Value
            .Update ' stores the new record
        End With
        R = R + 1 ' next row
    Loop
   
    rs.Close
    Set rs = Nothing

'    Sheets("Summary").Select
   
End Sub

The code fails here - rs.Open"Expenses", cn, adOpenKeyset, adLockOptimistic, although that Access file exists and has a Table named Expenses ...

Any ideas from the bored?

 

[Stevie]

Hiya David,
I made a mistake, my proposed solution is incorrect, so I have edited my message to be this. Sorry for false hope! I will continue to have a look, I was just double checking the MSDN articles for all your ADODB arguments.

 

[Stevie]

I can't test really without your files, and I can't find anything wrong with the arguments for cn.open or rs.open after looking through MSDN. Hopefully someone else will spot something I haven't

 

[David Evans]

I can't test really without your files, and I can't find anything wrong with the arguments for cn.open or rs.open after looking through MSDN. Hopefully someone else will spot something I haven't

Appreciate your help - I got it to work, but I'm not exactly certain what caused it ... I changed the db to a .mdb and took out the space from the table name ... and may have done some other things .. it was one of those afternoons when I thought nothing was going to work and bammo --- it worked. Always appreciate the willingness of people around here to even express an interest!

BTW, a very similar piece of code has worked flawlessly for months ....

duh for me - and sincere thanks to you ...
D