OleDBdataAdapter in dll for com prob - need updatable query?

[Guest]

Hello,

I created a simple dll in VB2005 to use ADO.Net -- oleDBDataAdapter 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 WriteTestToExcel() Implements
_IWriteEntireListToExcel.WriteTestExcel
Dim daOle As OleDbDataAdapter, ds As DataSet
Dim connOle As OleDbConnection
Dim strPath As String
Try
strPath = "C:\1A\testa123.xls"

connOle = New OleDbConnection
connOle.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strPath & ";Extended Properties=""Excel 8.0;HDR=YES"""

ds = New DataSet

If connOle.State = ConnectionState.Closed Then connOle.Open()
daOle = New OleDbDataAdapter
daOle.SelectCommand = New OleDbCommand
daOle.SelectCommand.Connection = connOle
daOle.SelectCommand.CommandText = "Create Table tbl1 (fld1 varchar(10), fld2
varchar(10),
fld3 varchar(10))"
daOle.SelectCommand.ExecuteNonQuery()

daOle.SelectCommand.CommandText = "Select * From [tbl1$]"
daOle.Fill(ds, "oleTbl1")

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

daOle.InsertCommand = New OleDbCommand
daOle.InsertCommand.Connection = connOle
daOle.InsertCommand.CommandText = "Insert Into [tbl1$](fld1, fld2, fld3)
Select @fld1, @fld2, @fld3"
daOle.InsertCommand.Parameters.Add("@fld1", OleDbType.VarChar, 10, "fld1")
daOle.InsertCommand.Parameters.Add("@fld2", OleDbType.VarChar, 10, "fld2")
daOle.InsertCommand.Parameters.Add("@fld3", OleDbType.VarChar, 10, "fld3")

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

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

Thanks,
Rich

 

[Mr. Arnold]

Hello,

I created a simple dll in VB2005 to use ADO.Net -- oleDBDataAdapter 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>

 

[Guest]

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.Update(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.Fill(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.Update(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?