MS Access and Typed Dataset problem

[TigerMan]

Hi,

I am attempting to use a typed dataset with an Access database. I have the
data showing in a grid and that works prefectly but when I try to update, it
does nothing. I tracked the problem down to the fact that the DataAdapter
wizard does not generate UPDATE and DELETE statements. I don't have any
permissions at all on the database and I can edit and delete the records
within Access. If I upsize the same database to SQL Server, it works
perfectly.

TIA

 

[Scott M.]

Well, the DataAdapter Wizard makes Update and Delete statements when I use
it with an Access database, so the problem is not a limitation of the
DataAdapter, it's most likely that your Select statement is too complex for
the wizard to figure out the other statements. Either way, you can create
the Update and Delete statements manually like this:

DataAdapter.UpdateCommand.CommandText = "...."

 

[TigerMan]

Hi Scott,

The select statement is as below which seems straightforward to me:

SELECT Test1, Test2, Test3 FROM Table1

This is the code that was auto generated by VB 2005:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Db4DataSet.Table1'
table. You can move, or remove it, as needed.
Me.Table1TableAdapter.Fill(Me.Db4DataSet.Table1)
End Sub

Private Sub Table1BindingNavigatorSaveItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Table1BindingNavigatorSaveItem.Click
Me.Validate()
Me.Table1BindingSource.EndEdit()
Me.Table1TableAdapter.Update(Me.Db4DataSet.Table1)
End Sub

Where does the UpdateCommand go in this case?

Thanks

 

[Cor Ligthert [MVP]]

Tigerman,

In addition to Scott, does your Select contains the primary key, otherwise
you can be sure that it won't work.

Cor

 

[TigerMan]

Hi Cor,

That is what is was. Thanks a lot

Tigerman,

In addition to Scott, does your Select contains the primary key, otherwise
you can be sure that it won't work.

Cor

 

[TigerMan]

I found it was a combination of both the primary key and permissions in the
database. The database permissions seemed to have changed once I added a new
datasource and said yes to adding the database to the project. Once I took
it out of the project and changed the connection string, it works fine.

 

[Scott M.]

Be aware that when you make connections (visually) to MS Access. Visual
Studio always has the file open, which causes Access to create its .ldb
locking file and this can prevent other applications (namely your running
application) to be prevented from modifying (or sometimes even opening) the
database.

I've found (with Access), it's best NOT to use the visual designers in
VS.NET and have the work done manually in code.

 

[TigerMan]

Thanks for the info Scott, I will keep that in mind