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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
Ok, thanks

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

much appreaciated as my db is running smothly now :)

ty

Jonathan
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top