ADO teething problems

[BillCo]

I'm trying to make the crossover to ADO and I've stumbled into a
strange quirk in A2K (mdb, no linked tables).

I have a function which generates an INSERT query on the fly and runs
it.
DAO: CurrentDB.Execute StrSQL - works fine
DoCmd.RunSql StrSQL - works

But with ADO using cn.execute, I get Error -2147217833
"The field is too small to accept the amount of data you attempted to
add. Try inserting or pasting less data."

....which leads me to think that ADO treats field size/ type conflicts
differently to DAO / Straight Jet SQL queries.

Any takers?

[Lyle Fairfield]

BillCo wrote:

I'm trying to make the crossover to ADO and I've stumbled into a
strange quirk in A2K (mdb, no linked tables).

I have a function which generates an INSERT query on the fly and runs
it.
DAO: CurrentDB.Execute StrSQL - works fine
DoCmd.RunSql StrSQL - works

But with ADO using cn.execute, I get Error -2147217833
"The field is too small to accept the amount of data you attempted to
add. Try inserting or pasting less data."

...which leads me to think that ADO treats field size/ type conflicts
differently to DAO / Straight Jet SQL queries.

Any takers?

My mind is too big to accept the amount of data you attempted to add.
Try inserting or pasting more data ... like the SQL of the Insert Query.

[pietlinden]

Lyle Fairfield wrote:

My mind is too big to accept the amount of data you attempted to add.
Try inserting or pasting more data ... like the SQL of the Insert Query.

Must be too much travel. According to Elvis Costello "They say that
travel broadens the mind till you can't get your head out the door."
So, have you been traveling recently?

j/k!

[BillCo]

Lyle Fairfield wrote:

My mind is too big to accept the amount of data you attempted to add.
Try inserting or pasting more data ... like the SQL of the Insert Query.

As you wish - although i dont think it will help much. It's one of many
dynamically generated queries based on a set of transalation tables for
transforming data for import into differently shapped database. The
generated SQL works in ADO for other seed values - so it's just really
a case of finding the offending field for this case and trimmimg the
size.
- the confusion is how DAO runs it fine, DoCmd.RunSQL has no problems
and Access query runs it fine. What is ADO's problem?!?

INSERT INTO [COUNTERPARTY]
(
VALID_ON,
STATUS,
LOT_TYPE_FK,
IDE_COUNTERPARTY_REF,
IDE_INTERNAL_PARTY_REF,
IDE_SOURCESYS_REF,
IDE_SWIFT_BIC,
CDE_ECONOMICAL_SECTOR,
CDE_INTERNAL_SECTOR,
CTY_RESIDENCE,
CTY_NATIONALITY,
DES_ADDRESS,
DES_CITY,
DES_LONGNAME,
DES_SHORTNAME,
DES_POSTALCODE,
TYP_CLIENT_STATUS,
TYP_CPTY_TYPE
)
SELECT
[GLOB_CUSTOMER].[VALID_ON],
"0" AS [Constant_2],
"1" AS [Constant_3],
[GLOB_CUSTOMER].[CUSTOMER_NO],
"100010" AS [Constant_5],
"GLOBUS" AS [Constant_6],
[GLOB_CUSTOMER].[BIC],
[MAP_ECONOMICAL_SECTOR].[FIREField],
[GLOB_CUSTOMER].[SECTOR],
[GLOB_CUSTOMER].[RESIDENCE],
[GLOB_CUSTOMER].[NATIONALITY],
[GLOB_CUSTOMER].[STREET],
[GLOB_CUSTOMER].[TOWN_COUNTRY],
[GLOB_CUSTOMER].[SHORT_NAME],
[GLOB_CUSTOMER].[MNEMONIC],
[GLOB_CUSTOMER].[POST_CODE],
"2" AS [Constant_17],
[MAP_CPTY_TYPE].[FIREField]
FROM ((
GLOB_CUSTOMER
LEFT JOIN MAP_ECONOMICAL_SECTOR ON [GLOB_CUSTOMER].[SECTOR] =
[MAP_ECONOMICAL_SECTOR].[GLOBUSField])
LEFT JOIN MAP_CPTY_TYPE ON [GLOB_CUSTOMER].[SECTOR] =
[MAP_CPTY_TYPE].[GLOBUSField]);

[Lyle Fairfield]

I agree that nothing jumps out at you here.

I would be suspicious of "100010" and IDE_INTERNAL_PARTY_REF.

If IDE_INTERNAL_PARTY_REF is an integer field (not long integer) it can
hold a max value of 32767.

If one tries to insert a numeric value greater than 32767, say 32768
or 100010 into an integer field in DAO with CurrentDB.Execute StrSQL
DAO inserts the record but leaves the ineteger field Null.
DAO will fail however if we use
CurrentDB.Execute StrSQL, dbFailOnError

But in ADO the INSERT fails; the record is not INSERTED or APPENDED,
TTBOMK, always. I think ADO is superior in this respect.

My guess is that ADO isn't different in dealing with field sizes, but
that DAO is failing to report and break on an error.

But it's just a guess.

[AccessAl]

I found more coding is required with ado,

What works for me is:


im cmd As ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT * FROM myTable" '/* or any select statement or string variable
cmd.Execute

Set cmd = Nothing

See how that works.

Al