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
3  15794 
<bi***********@ hotmail.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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.
"Stefan Kowalski" <a@b.com> wrote in message news:<cv******* **@hercules.bti nternet.com>... <bi***********@ hotmail.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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>. bi***********@h otmail.com (Bill Hounslow) wrote in message news:<62******* *************** ***@posting.goo gle.com>... "Stefan Kowalski" <a@b.com> wrote in message news:<cv******* **@hercules.bti nternet.com>... <bi***********@ hotmail.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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.. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Jamey |
last post by:
I perused old posts for an answer to this for at least an hour, and
I've found a work-around, but no definitive answer.
Synopsis of the problem:
On NotInList or ctl.Requery commands where a record has been deleted
(or appended via SQL), Access returns Runtime error 2118 when you get
to the Requery command which reads:
'You must save the current field before you run the Requery action.'
|
by: merco |
last post by:
HI,
i have an access MDB linked by ODBC to SQL2k.
I get a strange behaviour when i do an INSERT INTO to a local Mdb table
from "LEFT OUTER JOINED" SQL tables.
If the related table hasn't related record to the first table,
it get an Error 3167.
This behaviour is not present with MDB tables JOINS.
|
by: Matthew Louden |
last post by:
I want to read how many records in the table, and insert a record with id
field which increment the counter by 1. However, I had the following runtime
on Dim s As Integer = CInt(dr("t")). Since "t" (I want to represent the
count, but just a tempoary variable, not a field in table) doesnt exist in
the table
sqlStmt = "SELECT COUNT(*) As t FROM TimeSlot;"
cmd = New SqlCommand(sqlStmt, conn)
cmd.Connection.Open()
|
by: ruvi |
last post by:
I am getting runtime error 3021 - Either EOF or BOF is true or the current record has been deleted.....
I have 2 combo boxes in a form- One for the client and the other for the project.
When the form loads, the client combo box gets populated with all the clients.
When a particular client is clicked, the project combo box gets populated with all the projects corresponding to that client.
So far, so good. Now when I click a project in the...
|
by: maikanta |
last post by:
Hi Guys,
I wrote VBA code to delete the record using DoCmd Command. after that I wrote Select query to select the records from that table and assigned that query to the form recordsource property. But I am getting error at the Select query (Error: Runt time Error:3167 Records Deleted). Pls specify how to solve the above problem.
| | |
by: david720 |
last post by:
Error 3167 Record is deleted
And
Sometimes the main entry form displays a record in the form where all
fields are "#Delete"
Why do we get this error sometimes (about 2 times a week)?
It happens from different users and on different workstations.
Also in this application no records are ever deleted and would be
difficult for a user to delete a record
|
by: doma23 |
last post by:
Hi,
I implemented Front End / Back End on my database.
Both files are on the common disk (server) in the same folder.
My colleague and me were doing some multiuser testing and when we seleceted some periods this error pops up.
I.e. - we have two combo boxes, current period and previous period.
Periods are like F10,F09,H10,H09,1Q10,2Q09,3Q09 and so on, meaning full year, half year, quarter etc.
After some more testing, we discovered...
|
by: JonHuff |
last post by:
Why am i getting this error and more importantly how do i fix it? Here is my code. It runs fine until I modify it and add a new field to be imported into the DB. I will show the original code that runs fine then the modifed code (with the new field to be pulled in) which is giving me the error. It breaks at this line of code
LoanNo = NewImportRS.Fields(1)
I only change 2 lines of code from the original to the new code: the lines are :...
|
by: Ferwayne Yalung |
last post by:
The code works when I logged in as a admin, teacher or student.But when i try an unregistered username, runtime error 3021 (either BOF or EOF is true, or the current record
has been deleted. requested operation requires a current record
Here is my code:
Private Sub Command1_Click()
With Adodc2.Recordset
If .RecordCount = 0 Then
With Adodc1.Recordset
If Me.txtuser.Text = !IDno And txtpass.Text = !passcode Then
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
