Import Excel Data to Access Programatically

Jollyfrog

New Member
Joined
Sep 16, 2010
Messages
8
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
  1. 2013
Platform
  1. Windows
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:

Code:
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

New Member
Joined
Sep 16, 2010
Messages
8
Ok, thanks

this also helps with my write problem which happened next :)

much appreaciated as my db is running smothly now :)

ty

Jonathan
 

Watch MrExcel Video

Forum statistics

Threads
1,108,678
Messages
5,524,241
Members
409,566
Latest member
MickB

This Week's Hot Topics

Top