from Access to excel with DAO: error!

[thsdfh]

Hi,
>
I know this is specific ADO group, but i also a liitle bit DAO, so ...

> I want to send data from Access 2000 to Excel 2000, starting from an
Access
> form and using DAO. When i run this code below, i always get following
> error:
>
> "error 3027: cannot update: database or object is read only"
>
> I checked everything but can't find it. Strange is that when starting from
> Excel, i can receive the same data from Access.
> Any hints would be welcome
> thanks
> billy
>
> Private Sub command20_Click()
> Dim dbe,dba As DAO.Database
> Dim rste, rsta As Recordset
>
> 'open excel
> Set dbe = OpenDatabase("c:\excel\auto.xls", dbDriverNoPrompt, True, "excel
> 8.0")
> Set rste = dbe.OpenRecordset("auto")
>
> 'open access
> Set dba = OpenDatabase("c:\access\dao_ado2k.mdb")
> Set rsta = dba.OpenRecordset("select * from auto")
>
> rsta.MoveFirst
> Do While Not rsta.EOF
> rste.AddNew
> rste![auto_id] = rsta.Fields("auto_id").Value
> rste![kleur] = rsta.Fields("kleur").Value
> rste.Update
> rsta.MoveNext
> Loop
> End Sub
>
>

[SteveT]

Billy,

The code will run with slight changes:
Set dbe = OpenDatabase("c:\excel\auto.xls", dbDriverNoPrompt, False, "Excel 8.0;HDR=YES;IMEX=2;")
Set rste = dbe.OpenRecordset("auto$", dbOpenDynaset) 'note the $, I couldn't get it to work without it.

Also, your dim statements won't work quite the way you expect. If you don't explicity dimension a variable it's a variant. dbe and rste are variants the way you dimensioned them. And if you're going to explicity dimension a DAO.Database then you'd better do it for a recordset because you have ADO recordsets also.

> > Dim dbe,dba As DAO.Database
> > Dim rste, rsta As Recordset

Dim dbe as DAO.Database, dba As DAO.Database
Dim rste as DAO.Recordset, rsta As DAO.Recordset

Steve

thsdfh <sadfs@qdqsd> wrote in message news:Oz67emXpAHA.864@tkmsftngp04...

[qfdsb]

thanks steve, i'll try ...
<SteveT> schreef in bericht news:eAtuDVapAHA.1616@tkmsftngp04...