OleDBdataAdapter in dll for com prob - need updatable query?

[=?Utf-8?B?UmljaA==?=]

Hello,

I created a simple dll in VB2005 to use ADO.Net -- oleDBDataAdapte r to write
data to an Excel file. The code (below) works fine from an Excel file
(invoke the dll from Excel to write to another Excel file)

But if I run the same dll (tlb) from MS Access - I get an error that I need
to use an updateable query. Why does the following code work fine in Excel
but not in MS Acces? Maybe because Excel doesn't use Jet? Any suggestions
appreciated what I can do to make this work from MS Access.

--------------------------------------------
Public Class ....

Public Sub WriteTestToExce l() Implements
_IWriteEntireLi stToExcel.Write TestExcel
Dim daOle As OleDbDataAdapte r, ds As DataSet
Dim connOle As OleDbConnection
Dim strPath As String
Try
strPath = "C:\1A\testa123 .xls"

connOle = New OleDbConnection
connOle.Connect ionString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" &
strPath & ";Extended Properties=""Ex cel 8.0;HDR=YES"""

ds = New DataSet

If connOle.State = ConnectionState .Closed Then connOle.Open()
daOle = New OleDbDataAdapte r
daOle.SelectCom mand = New OleDbCommand
daOle.SelectCom mand.Connection = connOle
daOle.SelectCom mand.CommandTex t = "Create Table tbl1 (fld1 varchar(10), fld2
varchar(10),
fld3 varchar(10))"
daOle.SelectCom mand.ExecuteNon Query()

daOle.SelectCom mand.CommandTex t = "Select * From [tbl1$]"
daOle.Fill(ds, "oleTbl1")

Dim dr As DataRow
For i As Integer = 0 To 3
dr = ds.Tables("oleT bl1").NewRow
dr(0) = "bill"
dr(1) = "Sue"
dr(2) = "Tim"
ds.Tables("oleT bl1").Rows.Add( dr)
Next

daOle.InsertCom mand = New OleDbCommand
daOle.InsertCom mand.Connection = connOle
daOle.InsertCom mand.CommandTex t = "Insert Into [tbl1$](fld1, fld2, fld3)
Select @fld1, @fld2, @fld3"
daOle.InsertCom mand.Parameters .Add("@fld1", OleDbType.VarCh ar, 10, "fld1")
daOle.InsertCom mand.Parameters .Add("@fld2", OleDbType.VarCh ar, 10, "fld2")
daOle.InsertCom mand.Parameters .Add("@fld3", OleDbType.VarCh ar, 10, "fld3")

daOle.Update(ds , "oleTbl1") '<--- bombs out here when called from Access

connOle.Close()
Catch ex As Exception
MsgBox("Error from WriteTestToExce l " & ex.Message)
End Try
End Sub
End Class

Thanks,
Rich

[Mr. Arnold]

Re: OleDBdataAdapte r in dll for com prob - need updatable query?


"Rich" <Rich@discussio ns.microsoft.co mwrote in message
news:F5E04D55-8450-4FD8-A5FE-BF55E46F51FA@mi crosoft.com...

Hello,
>
I created a simple dll in VB2005 to use ADO.Net -- oleDBDataAdapte r to
write
data to an Excel file. The code (below) works fine from an Excel file
(invoke the dll from Excel to write to another Excel file)
>
But if I run the same dll (tlb) from MS Access - I get an error that I
need
to use an updateable query. Why does the following code work fine in
Excel
but not in MS Acces? Maybe because Excel doesn't use Jet? Any
suggestions
appreciated what I can do to make this work from MS Access.
>

http://www.google.com/search?hl=en&q=updateable+query+microsoft+access&btnG=Google+Search

<snipped>

[=?Utf-8?B?UmljaA==?=]

Re: OleDBdataAdapte r in dll for com prob - need updatable query?

Thanks for this link. I went to it, but it dealt mostly with com AdO issues.
The issue I am encountering is probably similar to the com ADO issues since
the error occurs at

dataAdapter.Upd ate(dataset, "tbl1")

The "Update" word appears to be recognized. The thing is that I am actually
Inserting records into "tbl1" which is actually a reference to a table I
created the Excel Object model. I retrieve a copy of the Excel table I
create and fill it

dataAdapter.Fil l(dataset, "tbl1")

Now I have an empty "tbl1"

Then I loop a few times to write data to my in-memory table, then I need to
push that data contained by the in-memory table to the Excel table

dataAdapter.Upd ate(dataset, "tbl1")

This operation works fine if I run it from Excel, but complains when I run
it from Access. Is it a problem with OleDB?


"Mr. Arnold" wrote:

>
"Rich" <Rich@discussio ns.microsoft.co mwrote in message
news:F5E04D55-8450-4FD8-A5FE-BF55E46F51FA@mi crosoft.com...

Hello,

I created a simple dll in VB2005 to use ADO.Net -- oleDBDataAdapte r to
write
data to an Excel file. The code (below) works fine from an Excel file
(invoke the dll from Excel to write to another Excel file)

But if I run the same dll (tlb) from MS Access - I get an error that I
need
to use an updateable query. Why does the following code work fine in
Excel
but not in MS Acces? Maybe because Excel doesn't use Jet? Any
suggestions
appreciated what I can do to make this work from MS Access.

>

http://www.google.com/search?hl=en&q=updateable+query+microsoft+access&btnG=Google+Search

>
<snipped>
>
>