Runtime Error 3167 Record Deleted

[bill_hounslow]

I'm trying to transfer data from one Sql Server into a table on
another, using a simple INSERT query in an Access database with links
to tables on both servers (the reasons for this are complicated but it
IS the simplest solution, believe me).

The 'Select' clause of the query works fine when run alone, but, when I
run the INSERT query I get a Runtime Error 3167 Record Deleted.

I get the error even when I'm the only person accessing both servers.

Has anyone any experience with this problem?

Any help would be appreciate.

Bill Hounslow

[Stefan Kowalski]

<bi***********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I'm trying to transfer data from one Sql Server into a table on
another, using a simple INSERT query in an Access database with links
to tables on both servers (the reasons for this are complicated but it
IS the simplest solution, believe me).

The 'Select' clause of the query works fine when run alone, but, when I
run the INSERT query I get a Runtime Error 3167 Record Deleted.

I get the error even when I'm the only person accessing both servers.

Has anyone any experience with this problem?

Any help would be appreciate.

Bill Hounslow

As a starting point, have you tried using the SQL Server Query Analyzer to
write the query which does the transfer? At least then you have removed
Access/JET from the list of possible problems and have a more concrete
question to pose, i.e. this SQL statement does run, but I can't do it via
Access.

[Bill Hounslow]

"Stefan Kowalski" <[e-mail]> wrote in message news:<cv*********@hercules.btinternet.com>...

<bi***********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I'm trying to transfer data from one Sql Server into a table on
another, using a simple INSERT query in an Access database with links
to tables on both servers (the reasons for this are complicated but it
IS the simplest solution, believe me).

The 'Select' clause of the query works fine when run alone, but, when I
run the INSERT query I get a Runtime Error 3167 Record Deleted.

I get the error even when I'm the only person accessing both servers.

Has anyone any experience with this problem?

Any help would be appreciate.

Bill Hounslow

As a starting point, have you tried using the SQL Server Query Analyzer to
write the query which does the transfer? At least then you have removed
Access/JET from the list of possible problems and have a more concrete
question to pose, i.e. this SQL statement does run, but I can't do it via
Access.

The statement I'm using is a bog-standard Append query of the type:

INSERT INTO [Target Table] (target field1, target field2,...)
SELECT source field1, source field2,...
FROM [Source Query]
WHERE criteria

The source query contains some functions a number of JOINS to extract
and calculate the required data.

I said the reasons for doing it this way are complicated <g>.

The problem is that the database from which I'm transfering the data
is over-written every night, not just with new data but completely, so
any routines, views, procedures, etc, I write in that SQL database
would be destroyed. It is over-rittten with what's essentialy a copy
of the 'live' database, specifically to allow reporting without loss
of performance.

But what puzzles me is why the SELECT clause runs without any problem
when not part of the Append query, and we've never had problem
appending to tables in the target database before.

It has occurred to me that I could use two other methods:

1. Select into intermediate tables then append those to the target
tables.

2. Use VBA code to build recordsets then update the target tables one
record at a time.

Both of these would be slower and I don't yet know if they'll work, so
I wondered if there is a simple solution to the Runtime Error 3167
problem before I do the necessary work (and possibly wast even more
time on the problem <g>.

[Bill Hounslow]

bi***********@hotmail.com (Bill Hounslow) wrote in message news:<62*************************@posting.google.c om>...

"Stefan Kowalski" <[e-mail]> wrote in message news:<cv*********@hercules.btinternet.com>...

<bi***********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I'm trying to transfer data from one Sql Server into a table on
another, using a simple INSERT query in an Access database with links
to tables on both servers (the reasons for this are complicated but it
IS the simplest solution, believe me).

The 'Select' clause of the query works fine when run alone, but, when I
run the INSERT query I get a Runtime Error 3167 Record Deleted.

I get the error even when I'm the only person accessing both servers.

Has anyone any experience with this problem?

Any help would be appreciate.

Bill Hounslow

As a starting point, have you tried using the SQL Server Query Analyzer to
write the query which does the transfer? At least then you have removed
Access/JET from the list of possible problems and have a more concrete
question to pose, i.e. this SQL statement does run, but I can't do it via
Access.

The statement I'm using is a bog-standard Append query of the type:

INSERT INTO [Target Table] (target field1, target field2,...)
SELECT source field1, source field2,...
FROM [Source Query]
WHERE criteria

The source query contains some functions a number of JOINS to extract
and calculate the required data.

I said the reasons for doing it this way are complicated <g>.

The problem is that the database from which I'm transfering the data
is over-written every night, not just with new data but completely, so
any routines, views, procedures, etc, I write in that SQL database
would be destroyed. It is over-rittten with what's essentialy a copy
of the 'live' database, specifically to allow reporting without loss
of performance.

But what puzzles me is why the SELECT clause runs without any problem
when not part of the Append query, and we've never had problem
appending to tables in the target database before.

It has occurred to me that I could use two other methods:

1. Select into intermediate tables then append those to the target
tables.

2. Use VBA code to build recordsets then update the target tables one
record at a time.

Both of these would be slower and I don't yet know if they'll work, so
I wondered if there is a simple solution to the Runtime Error 3167
problem before I do the necessary work (and possibly wast even more
time on the problem <g>.

Further...

The first option above worked, so, although it's not as fast as I
would like, it works.

But it would still be interesting to know what's causing the Runtime
Error 3167..