ADODB data adapter problem

[Steve Marshall]

Hi all,

I am building a VB application which uses quite a few data adapters. All
was going fine, then suddenly for one of them the wizard refused to generate
some of the SQL statements for manipulating the data. Anyone know why it
might not be able to do this? There doesn't seem to be any way to get
further information from the wizard. I'm using VS 2003, and the data source
is an Access MDB.

Thanks

[remove numeral in e-mail address if using e-mail]

 

[Cor Ligthert [MVP]]

Steve,

Can you tell a little bit more, probably are you using an OleDb
adapterwizard however maybe is it totally something different. And if it is
an OleDB adapterwizard at what place does it stop.

ADODB is another name for the classic ADO way, which has no wizards in
VS2003

Cor

 

[Steve Marshall]

Thanks for the reply. Yes, I'm using the OLEDB Adapter Wizard to create
these things. You will be aware that at one point in the process it tells
you that it has generated the SQL statements for SELECT, INSERT, DELETE etc.
For this one table it says it is unable to do that - some of them cannot be
generated. It can usually generate the SELECT statement, but some of the
others seem to cause a problem. It does carry on and create the adapter,
but some of the SQL statements are missing.

My first thought was that it did not like some of the field names in the
table, i.e. that they might have been reserved words in SQL. I have tried
changing some of them, but it did not get past the problem. The field names
in the table are:

ID an Access Autonumber field, and the primary index for
the table
Description text
Log boolean
LogAmp boolean
MarginState boolean
Margin number (single)
Mode text
Relative boolean
Display boolean

There is a one-to-many relationship based on the ID field to a child table.
This is the same arrangement as several other pairs of tables I am working
with.

I tried changing the names "Relative", "Display" and "Mode" thinking they
were the most likely to clash with something in SQL, but no dice. I have
other tables with the names ID, Description and Log, and have created data
adapters for them without problem. So it has me rather bemused at the
moment!

 

[Cor Ligthert [MVP]]

Steve,

Thanks nice descripted now. You say "relation". You know that than is
created a joined view for which the wizard *cannot* make the insert, delete
and update?

Cor

 

[Steve Marshall]

Well, thanks for that, but it doesn't really make sense. I have 3 other
pairs of tables in my MDB with identical relationships between them, and the
wizard was quite happy to generate all the SQL statements for them.

I repeat, I am only selecting fields from one table (the parent) in this
data adapter. I have made a data adapter for the child table, no problem.

 

[Steve Marshall]

Actually, I've just found a way around it. I told the wizard NOT to use the
"Optimistic Concurrency" option. It now generates all the SQL statements
quite happily. I'll have to think about the implications of this, but I
think it will be OK in this application.

 

[Cor Ligthert [MVP]]

Steve,

I repeat, I am only selecting fields from one table (the parent) in this
data adapter. I have made a data adapter for the child table, no problem.

Can you show me where I have read it wrong, maybe I miss a message and can
than see more in that text.

Cor