Transferring multiple rows from excel to access at a time

[Vigneshrs87]

Hi,

My requirement is as below.

I have an excel spread sheet with data in sheet1 of workbook book3.xlsm, what i want to do is transfer data from range A10:P30 into a table named hi in access 2007.

I know how to do it using a loop but i do not want to use a loop i want to transfer all the rows at a time.

I have tried using the below code.

Sub Update()
Dim cn, cn1 As ADODB.Connection
Dim rs, rs1 As ADODB.Recordset
Set cn = New ADODB.Connection
Set cn1 = New ADODB.Connection
Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0; Data Source=C:\Documents and Settings\220409\My Documents\Database21.accdb"
rs.Open "select count('ID') from Hi", cn
a = rs.Fields(0).Value
rs.Close
cn1.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Documents and Settings\220409\My Documents\book3.xlsm; Extended Properties=Excel 12.0"
rs1.Open "insert into Hi select * FROM [Sheet1$A10:P30] IN C:\Documents and Settings\220409\My Documents\book3.xlsm, [Excel 12.0]", cn
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub


I get and error saying that there is a syntax error in from clause, I have even tried removing the in clause totally.

When i use Cn as the connection it says access engine cannot find sheet1$ and when i use cn1 as the connection it says access engine could not find Hi.

Please help me modify the code or provide me with a new solution.

Thanks in advaance.

 

[boblarson]

This is kind of a duplicate post of your other one but I'll ask the same question here - is the code that is here in the same database that you are wanting to import the data?

 

[Vigneshrs87]

Hi boblarson,

No the code is in an excel file.