Import Data from Excel into Access

[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

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[romperstomper]

You are confusing ADO and DAO. An ADODB.Connection object does not have an OpenRecordset method (nor would dbOpenTable mean anything to it as that's a DAO constant). You need something more like:

Set rs = New ADODB.Recordset
rs.Open "ChannelGroupList", conn, adopendynamic, adlockoptimistic, adcmdtable

[Jollyfrog]

Ah Fantastic, i can now grab all the data from my storage sucessfully :-)

[Jollyfrog]

Hmm ok now when i try to add new data i get a different error!

Private Sub SendAccess()
 
  Dim conn As New ADODB.Connection
  Dim sNWind As String
  Dim rst As New ADODB.Recordset
  
  strSQL = "SELECT * FROM tblMain;"
  
 sNWind = _
      "C:\Documents and Settings\jonathan.broughton\Desktop\ChannelEconomics.accdb"
      
   conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
      sNWind & ";"
  
  rst.Open "ChannelGroupList", conn
  
   r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A

rst.AddNew ' create a new record
' add values to each field in the record
rst.Fields("Channel Group") = Range("A" & r).Value

' add more fields if necessary...
rst.Update ' stores the new record
End With
r = r + 1 ' next row
Loop


  Set rsData = Nothing
  Set dbData = Nothing
 
End Sub

It compiles nicely but then errors on rst.AddNew ' create a new record
with current recordset does not support updating. this may be a limitation if the provider, or of the selected locktype

[romperstomper]

Spot the difference between your code and what I posted...
(by default you get a forward only, read only cursor)

[Jollyfrog]

Ah and now it all makes sense

thanks for your help, i'm very grateful :-D

Jonathan

[romperstomper]

Glad to help.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save