Can't insert into inner joined table

[Atlas]

Access 2003 + SPx, ADO 2.8, MS SQL Server 2000, .adp, bound form.

Into the load event, me.recordsource is set to

SELECT tblA.*, tblB.*
FROM tblA
INNER JOIN tblB ON tblA.IDB = tblB.IDB

None of the fileds of tblB is shown nor updated in the single form; tblB is
only joined for sorting purposes.

Now when inserting a new record from the form (basically in tblA) I get an
error:

"Cannot insert a non-null value into a timestamp column. Use INSERT with a
column list or with a default NULL for the timestamp column"

NOTE:
- Both tables allow NULL values into timestamps.
- None of the fields of tblB is "touched"
- removing the inner joined table ,solves the problem

Thanks

 

[Atlas]

Also tried "Unique table" option into the form properties with no
benefit.....

 

[Sylvain Lafontaine]

I had myself many problems with ADP when I want to use a joined table only
for sorting purposes. Your case is complicated by the fact that you are
using an INNER JOIN on table tblB but that there are no corresponding record
on tblB when you create a new record on table tblA.

Possible solutions:

1- Replace the INNER JOIN with a LEFT OUTER JOIN.

2- Set a default value for tblA.IDB: either NULL or pointing to a true value
of tblB.IDB.

3- Try to add an hidden control linked to one of the fields of table tblB.

4- Forget about writing « tblB.* » and instead take the time to specify only
the required fields: tlbB.B1, tblB.B2, ...

5- Create a Resync Command (always a good one when you have any kind trouble
with updating or even when you don't have any trouble at all: will makes
things going faster).

In my case, I always use a stored procedure instead of setting the
RecordSource in the OnLoad event, so I don't know if this will help you.

 

[Atlas]

Hi dear Sylvain,

I had myself many problems with ADP when I want to use a joined table only
for sorting purposes. Your case is complicated by the fact that you are
using an INNER JOIN on table tblB but that there are no corresponding
record on tblB when you create a new record on table tblA.

Possible solutions:

1- Replace the INNER JOIN with a LEFT OUTER JOIN.

Just tried, unfortunatelly it didn't work

2- Set a default value for tblA.IDB: either NULL or pointing to a true
value of tblB.IDB.

It always points to a real value

3- Try to add an hidden control linked to one of the fields of table tblB.

Tried to stuff the primary key of tblB, nothing

4- Forget about writing « tblB.* » and instead take the time to specify
only the required fields: tlbB.B1, tblB.B2, ...

I've started writing only the sorting field, then moved to the *, but
nothing

:-(

 

[Atlas]

for sorting purposes. Your case is complicated by the fact that you are

using an INNER JOIN on table tblB but that there are no corresponding
record on tblB when you create a new record on table tblA.

To be honest there's a fieldin tblA that is the counterpart of the join:
let's dig into it

tblA
ID
Fld1
...
IDB
...
Fldx

All those fields are on the form

tblB
IDB
Description
....
Fldx

None of those are on the form

now the recordsource

SELECT tblA.*, tblB.Description
FROM tblA
INNER JOIN tblB ON tblA.IDB = tblB.IDB
ORDER BY Description

I'm lost!

 

[Sylvain Lafontaine]

If you don't need to link the value of tblB.Description for the form, why
don't you just remove it from the Select statement?

 

[Atlas]

If you don't need to link the value of tblB.Description for the form, why
don't you just remove it from the Select statement?

It is used in the ORDER BY statement

 

[Atlas]

Sylvain,
digging on the net I've found this article :
http://support.microsoft.com/?kbid=815543
unfortunatelly it doesn't explain how to avoid the problem in Access.......

 

[Sylvain Lafontaine]

This article is for ADO.NET and has nothing to do with ADO or ADP.

 

[Atlas]

This article is for ADO.NET and has nothing to do with ADO or ADP.
Correct.

But it shows the same simptoms.

Aren't we dealing with ADO here?

I've never used ADO.NET but I imagine the two products are closer than they
appear.