Nothing Generates for OleDbCommandBuilder

[Paul]

I'm trying to update a Microsoft Access database. I can successfully
read from the database and submit a command to DELETE all records from
a table. Now I'm trying to add records to the table that I just
cleared. Below is the code:

myConnection = New
OleDb.OleDbConnection(accessConnectionStringConsta nt & accessMdb)

myCommand.Connection = myConnection
myCommand.CommandText = SqlString
myCommand.CommandType = CommandType.Text
myCommand.CommandTimeout = 420
myDataAdapter.SelectCommand = myCommand

Dim myCommandBuilder As New
OleDb.OleDbCommandBuilder(myDataAdapter)

myConnection.Open()
myDataAdapter.Update(dst.Tables(TableName))

This code is part of a Sub that receives the correct SELECT statement
in the "SqlString" parameter. After the

Dim myCommandBuilder As New
OleDb.OleDbCommandBuilder(myDataAdapter)

statement, the INSERT, UPDATE and DELETE statements are nothing in
myDataAdapter. So when the

myDataAdapter.Update(dst.Tables(TableName))

statement runs, an exception is thrown stating that the INSERT INTO
statement is invalid. What am I doing wrong?

[Paul]

I should also say that my SELECT statements is this:

SELECT * FROM Users

I've seen other posts stating that it is possible there are columns
that are reservered words. So I encased each column withing square
brackets [], but I still got the same exception.

[Paul]

OK, I figured out why I am getting the problem.

My problem is that one of the columns is the word Password. Enclosing
it in square brackets like "[Password]" in the SELECT statement does
not fix the problem. I got around it in order to test this by not
including it. Unfortunately I cannot change the column name.

I'm reading from a SQL Server table and writing to an Access table.
I'm going to try and do this manually with my own INSERT statements.

[Paul]

Man...it feels like I'm only talking to myself. I hope that's not
bad : @

Anyway, I verified that is the problem. And I cannot change the
column name. It must remain "Password" for now. Does anyone know a
way around this?

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

Paul,

Can't you supply your own Insert, Update and Delete commands with [Password]?

Isn't it just that the commandbuilder doesn't handle this properly?

Kerry Moorman
"Paul" wrote:

Man...it feels like I'm only talking to myself. I hope that's not
bad : @

Anyway, I verified that is the problem. And I cannot change the
column name. It must remain "Password" for now. Does anyone know a
way around this?

[Paul]

Thanks for the response Kerry.

Yes, I can supply my own and am currently working on that. However, I
was just wondering if there was a parameter or some setting to tell
the CommandBuilder to resolve this, possibly by enclosing the fields
in square brackets.

[Paul]

I successfully INSERTed the records (abou 6,700 of them) using a
command object and an INSERT statement for each individual record (all
6,700). The performance sucks. If I run the code with the
CommandBuilder and DataAdapter, without the Password column, the whole
process takes 1 min 48 seconds. If I run the process with the
individual INSERT statements it takes 19 minutes.

Does anyone have any possible resolutions?

I've tried overwriting the INSERT statement in the myDataAdapter
object using [Password], but that didn't seem to work. I'll work on
that more and see if I can get it to work.

Ultimately, I would like the CommandBuilder to resolve this.

[RobinS]

This explains why so few people use the CommandBuilder. ;-)

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------
"Paul" <pw****@hotmail.comwrote in message
news:11**********************@d57g2000hsg.googlegr oups.com...

>I successfully INSERTed the records (abou 6,700 of them) using a
command object and an INSERT statement for each individual record (all
6,700). The performance sucks. If I run the code with the
CommandBuilder and DataAdapter, without the Password column, the whole
process takes 1 min 48 seconds. If I run the process with the
individual INSERT statements it takes 19 minutes.

Does anyone have any possible resolutions?

I've tried overwriting the INSERT statement in the myDataAdapter
object using [Password], but that didn't seem to work. I'll work on
that more and see if I can get it to work.

Ultimately, I would like the CommandBuilder to resolve this.