Import Excel Data to Access Programatically

[Jollyfrog]

Hi,

I'm trying to get Excel 2007 to send some data to an existing table in Access, the amount of data can vary in size so needs to be dynamic.


Sub DAOFromExcelToAccess()

' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim r As Long
Dim conn As New ADODB.Connection
Dim sNWind As String
Dim rs As Recordset

sNWind = _
"C:\Documents and Settings\jonathan.broughton\Desktop\ChannelEconomics.accdb"

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
sNWind & ";"
Set rs = conn.OpenRecordset("ChannelGroupList", dbOpenTable)


r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Channel Group") = Range("A" & r).Value

' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
conn.Close
Set rs = Nothing

End Sub


code currently errors on the line Set rs = conn.OpenRecordset("ChannelGroupList", dbOpenTable)
as this is wrong, but i need to be able to open the record at this location!

what am i doing wrong?

thanks

Jonathan

 

[xenou]

You are mixing ADO and DAO up here. dbOpenTable is a DAO constant and notwithstanding the name given to your procedure you're using ADO.

I guess you need to decide if you want to use DAO or ADO and adjust accordingly ... I've never used ADO with a table (usually I run select queries with ADO). This might work with only a few adjustments:

Set rs = New ADODB.Recordset
Call rs.Open ("ChannelGroupList", conn, adOpenDynamic, adLockOptimistic, adCmdTable)

Note that with ADO you need to create the new recordset first, then open it. You may need to do the same with the connection object.

Alternatively, you can go back to the drawing board and use DAO - in which case, use DAO objects and appropriate commands.

Good Luck - post back if you can't make progress. Erlandsen consulting has good pages on both ADO and DAO.

 

[Jollyfrog]

Ok, thanks

this also helps with my write problem which happened next

much appreaciated as my db is running smothly now

ty

Jonathan