Append query from Jet/MDB to SQL Server doesn't properly handle one column

  • Thread starter Thread starter Rene
  • Start date Start date
R

Rene

Hello group,

This query is giving me a problem, in that one field is not carried over to
the destination table properly.

INSERT INTO BE_tT
SELECT FE_tT.*
FROM FE_tT;

On the front-end, the field causing problems is:
Primary key,
Autonumber (random)
in a Jet/MDB table Access 2002

On the back-end, its equivalent is defined as:
Key,
int
in an ODBC linked table to SQL Server 2000

When I execute the query, the back-end table gets a new random number,
instead of getting the value from the front-end.

What's wrong in my deffinitions/query?

Thanx!

René
Montreal
 
Dear Rene:

Given your description, it looks like it should be working as you
wish, assuming the destination table does NOT have this column set to
be an identity. I suspect that might be the problem. An identity
column cannot be updated - the database engine selects unique values
for it instead.

If that isn't the problem, then I'd suggest you try it this one step
at a time. First, please look at just the SELECT part of the query:

SELECT FE_tT.*
FROM FE_tT;

and the destination table layout:

SELECT TOP 1 * FROM BE_tT

If you have Query Analyzer (QA) you can put these into the window
simultaneously, run them, and see the results one over the other.
This is a good chance to match the columns one over the other and make
sure everything in the source query lines up with the proper
destination columns, being in the same order and of the same data
types from left to right.

Next, I would suggest you insert a single row into the destination
table to make sure this works as desired:

INSERT BE_tT VALUES('Value1', Value2, . . .)

Be sure to place text values and date values in single quotes, and get
the columns in exactly the sequence of the destination table. For the
column that is giving you a problem, be sure to use a value NOT
already in BE_tT and then look at the table to see what value was
placed in the new row for that column. I'll bet it ISN'T the value
you specified. Again, I think that is because the destination column
is identity. It will do that for sure if it is. You cannot choose
your own values for an identity column - they are assigned.

Hope this gets you closer to your goal.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

Thank you kindly for your reply. I have used your suggestions, but the
problem persists; and so, here is additional information.

1- The destination field causing the problem is not an Identity field;
2- The front-end is an Jet/MDB which is not linked to SQL Server;
So I can't look at the Select portion using the QueryAnalyzer;
3- I did run Insert Values in QueryAnalyzer;
The key field winds up being a random number other than the value I
specify;
Even when I enter a key value in a datagrid, the engine puts a random
number there too;

So indeed, it behaves as if that field were defined as an Identity column.
Yet when I look at the design that's not what I see.

Any suggestion will be very appreciated!

Regards

René
 
Back
Top