Good Day All, I have some EEO data in an old dBase4 database that I have converted to an Access table. Since dBase was not a relational database, I didn't create any key fields. I linked all of the tables together on Employees' SSN; therefore, in order to use this data with the current Access database, I need to create a relationship between the old dBase4 EEO data and the Access employee data. The Access employee data was also an old dBase table, but after importing it into Access and adding a key field, EmployeeID, Access automatically assigned a number to each of my 1,200+ records. This part of the equation was simple, and additionally, I am attempting to do a few other things here:
1st - I need to use the SSN in the old dBase data (EEO) and do a FindFirst in tblMasterEmplye e. If I find a match, I want to take the EmployeeID from that record and put it in tblEEO_Employee Data, along with the data from the associated record in the old dBase table (tblEEO_DataFro mDBase).
2. If no match is found for the SSN in tblMasterEmploy ee, I want to Insert a new record and place the SSN in the table; save the record and then take the new EmployeeID from tblMasterEmploy ee with the data in tblEEO_DataFrom DBase and place it in tblEEO_Employee Data
Pretty Simple; huh? :o)
Here is the code: - Dim blnExist As Boolean
-
Dim rstSource As DAO.Recordset
-
Dim rstDest As DAO.Recordset
-
Dim rstGetID As DAO.Recordset
-
Dim lngEmpID As Long
-
Dim lngJGCtr As Long
-
lngJGCtr = 0
-
-
Set rstSource = CurrentDb.OpenRecordset("tblEEO_DataFromDBase")
-
Set rstDest = CurrentDb.OpenRecordset("tblEEO_EmployeeData")
-
Set rstGetID = CurrentDb.OpenRecordset("tblMasterEmployee")
-
-
Do Until rstSource.EOF
-
blnExist = DCount("*", "tblMasterEmployee", "SSN='" & rstSource!SSN & "'")
-
If Not blnExist Then
-
CurrentDb.Execute "insert into tblMasterEmployee (SSN) values ('" & rstGetID!SSN & "')"
-
DoCmd.GoToRecord , , acLast
-
rstGetID.FindFirst "rstGetID [SSN] = '" & rstGetID!SSN & "'"
-
'----------------^Get error on this line "Operation is not supported 'for this object" Run-time error 3251
-
End If
-
-
-
If rstGetID.NoMatch Then
-
MsgBox "Unable to find match!"
-
Else
-
MsgBox "The item is : " & rstGetID![SSN]
-
End If
-
rstDest.AddNew
-
rstDest!EmployeeID = rstGetID!EmployeeID
-
rstDest!Race = rstSource!Race
-
rstDest!Gender = rstSource!Gender
-
rstDest!PositionID = rstSource!Position
-
lngJGCtr = lngJGCtr + 1
-
If lngJGCtr < 6 Then
-
rstDest!JobGroupID = 5
-
Else
-
lngJGCtr = 0
-
rstDest!JobGroupID = 4
-
End If
-
rstDest!CategoryID = rstSource!Category
-
rstDest!Veteran = rstSource!Vet_Stat
-
rstDest!DisVet = rstSource!Dis_Vet
-
rstDest!VietNamVet = rstSource!Nam_Stat
-
rstDest!DisabilityStatus = rstSource!Handicap
-
rstDest!Disability = rstSource!H_Cap
-
rstDest.Update
-
rstSource.MoveNext
-
Loop
-
The program keeps blowing up at the Insert Into command with the following error:
"Operation is not supported for this object" Run-time error 3251
That error is only if SSN not found and I attempt to add the record and then find it for applicable data manipulation. However, all of the records that exist and do process shows an EmployeeID of the number "1". The first person in rstGetID has an employeeID of 1, so for reason, the program is only reading the first record.
Many thanks to whoever comes to my rescue.
David
Running on Windows XP and Access 2002 in 2000 mode.
5 4592 MMcCarthy 14,534
Recognized Expert Moderator MVP
I've made a couple of changes and also I have a question? -
-
Dim blnExist As Boolean
-
Dim rstSource As DAO.Recordset
-
Dim rstDest As DAO.Recordset
-
Dim rstGetID As DAO.Recordset
-
Dim lngEmpID As Long
-
Dim lngJGCtr As Long
-
lngJGCtr = 0
-
-
Set rstSource = CurrentDb.OpenRecordset("tblEEO_DataFromDBase")
-
Set rstDest = CurrentDb.OpenRecordset("tblEEO_EmployeeData")
-
Set rstGetID = CurrentDb.OpenRecordset("tblMasterEmployee")
-
-
rstSource.MoveFirst
-
Do Until rstSource.EOF
-
blnExist = DCount("*", "tblMasterEmployee", "SSN='" & rstSource!SSN & "'")
-
If Not blnExist Then
-
DoCmd.RunSQL "insert into tblMasterEmployee (SSN) values ('" & rstGetID!SSN & "')"
- ' what are you trying to do here?
-
' If you are trying to find the record you've just inserted than you
-
' can't as you would have to close and then reopen the recordset
-
DoCmd.GoToRecord , , acLast
-
rstGetID.FindFirst "rstGetID [SSN] = '" & rstGetID!SSN & "'"
-
-
End If
-
>
-
>
-
>
-
I've made a couple of changes and also I have a question? -
-
Dim blnExist As Boolean
-
Dim rstSource As DAO.Recordset
-
Dim rstDest As DAO.Recordset
-
Dim rstGetID As DAO.Recordset
-
Dim lngEmpID As Long
-
Dim lngJGCtr As Long
-
lngJGCtr = 0
-
-
Set rstSource = CurrentDb.OpenRecordset("tblEEO_DataFromDBase")
-
Set rstDest = CurrentDb.OpenRecordset("tblEEO_EmployeeData")
-
Set rstGetID = CurrentDb.OpenRecordset("tblMasterEmployee")
-
-
rstSource.MoveFirst
-
Do Until rstSource.EOF
-
blnExist = DCount("*", "tblMasterEmployee", "SSN='" & rstSource!SSN & "'")
-
If Not blnExist Then
-
DoCmd.RunSQL "insert into tblMasterEmployee (SSN) values ('" & rstGetID!SSN & "')"
- ' what are you trying to do here?
-
' If you are trying to find the record you've just inserted than you
-
' can't as you would have to close and then reopen the recordset
-
DoCmd.GoToRecord , , acLast
-
rstGetID.FindFirst "rstGetID [SSN] = '" & rstGetID!SSN & "'"
-
-
End If
-
>
-
>
-
>
-
- ' what are you trying to do here?
-
' If you are trying to find the record you've just inserted than you
-
' can't as you would have to close and then reopen the recordset[/b]
-
DoCmd.GoToRecord , , acLast
-
rstGetID.FindFirst "rstGetID [SSN] = '" & rstGetID!SSN & "'"
-
-
End If
Thanks for the insight and assistance. I mistakenly thought that you had to save the record before you could manipulate the data. After looking at your code, I only included the DoCmd.RunSQL command, and although it ran through the EOF of the recordset, all 1,200+ records still has an EmployeeID of 1. So for some reason my logic is forcing the program to only read the first record of the rstGetID recordset.
Any more suggestions?
David
MMcCarthy 14,534
Recognized Expert Moderator MVP
Try this ... -
-
Dim blnExist As Boolean
-
Dim rstSource As DAO.Recordset
-
Dim rstDest As DAO.Recordset
-
Dim rstGetID As DAO.Recordset
-
Dim lngEmpID As Long
-
Dim lngJGCtr As Long
-
lngJGCtr = 0
-
-
Set rstSource = CurrentDb.OpenRecordset("tblEEO_DataFromDBase")
-
Set rstDest = CurrentDb.OpenRecordset("tblEEO_EmployeeData")
-
Set rstGetID = CurrentDb.OpenRecordset("tblMasterEmployee")
-
-
rstSource.MoveFirst
-
-
Do Until rstSource.EOF
-
blnExist = DCount("*", "tblMasterEmployee", "SSN='" & rstSource!SSN & "'")
-
If Not blnExist Then
-
rstGetID.FindFirst "[SSN] = '" & rstSource!SSN & "'"
-
DoCmd.RunSQL "insert into tblMasterEmployee (SSN) values ('" & rstGetID!SSN & "')"
-
End If
-
If rstGetID.NoMatch Then
-
MsgBox "Unable to find match!"
-
Else
-
MsgBox "The item is : " & rstGetID![SSN]
-
End If
-
rstDest.AddNew
-
rstDest!EmployeeID = rstGetID!EmployeeID
-
rstDest!Race = rstSource!Race
-
rstDest!Gender = rstSource!Gender
-
rstDest!PositionID = rstSource!Position
-
lngJGCtr = lngJGCtr + 1
-
If lngJGCtr < 6 Then
-
rstDest!JobGroupID = 5
-
Else
-
lngJGCtr = 0
-
rstDest!JobGroupID = 4
-
End If
-
rstDest!CategoryID = rstSource!Category
-
rstDest!Veteran = rstSource!Vet_Stat
-
rstDest!DisVet = rstSource!Dis_Vet
-
rstDest!VietNamVet = rstSource!Nam_Stat
-
rstDest!DisabilityStatus = rstSource!Handicap
-
rstDest!Disability = rstSource!H_Cap
-
rstDest.Update
-
rstSource.MoveNext
-
Loop
-
The "If Not blnExist " condition was never true, so the program never hit the FindFirst statment. I commented out the If-Then statement, but still used the fiindfirst: - Do Until rstSource.EOF
-
blnExist = DCount("*", "tblMasterEmployee", "SSN='" & rstSource!SSN & "'")
-
' If Not blnExist Then
-
' rstGetID.FindFirst "[SSN] = '" & rstSource!SSN & "'"
-
' DoCmd.RunSQL "insert into tblMasterEmployee (SSN) values ('" & rstGetID!SSN & "')"
-
' End If
-
rstGetID.FindFirst "[SSN] = '" & rstSource!SSN & "'"
-
If rstGetID.NoMatch Then
-
'..............Rest of code
I received the following error:
Run-time error 3251 Operation is not supported for this type of object
However, when I ran your code with the If-Then statement, I noticed that the program is never finding a match on SSN in rstGetID, so the recordset pointer is always sitting at the first record, which is why when the program runs through EOF, all of the EmployeeIDs in rstDest is 1. For some reason, Access appears not to like the the FindFirst statement with the rstGetID recordset.
I figured it out. I needed to add dbOpenDynaset to Current.db statements: - Set rstSource = CurrentDb.OpenRecordset("tblEEO_DataFromDBase", dbOpenDynaset)
-
Set rstDest = CurrentDb.OpenRecordset("tblEEO_EmployeeData", dbOpenDynaset)
-
Set rstGetID = CurrentDb.OpenRecordset("tblMasterEmployee", dbOpenDynaset)
Thanks for pointing me in the right direction. Have a blessed day as you have blessed mine.
David
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: newbie_mw |
last post by:
Hi, I need urgent help with a novice problem. I would appreciate any
advice, suggestions... Thanks a lot in advance! Here it is:
I created a sign-up sheet (reg.html) where people fill in their first
name, last name, email, etc. The data are then sent to a PHP script
(reg.php). The data are then inserted into a table (reg) in MS SQL
server. I have declared the variables like this:
if (!(isset($_POST))) {
$FirstName = "" ;
|
by: newbie_mw |
last post by:
Seems my post was buried in more cries for help :-) I will try again.
It's probably a very novice question so please take a look! Thanks!
-----------------------------------------------------------------
I created a sign-up sheet (reg.html) where people fill in their first
name, last name, email, etc. The data are then sent to a PHP script
(reg.php). The data are then inserted into a table (reg) in MS SQL
server. I have declared the...
|
by: Chad Richardson |
last post by:
Is there a way in SQL Server 2000 to extract data from a table, such that
the result is a text file in the format of "Insert Into..." statements, i.e.
if the table has 5 rows, the result would be 5 lines of :
insert into Table (, , .... VALUES a,b,c)
insert into Table (, , .... VALUES d, e, f)
insert into Table (, , .... VALUES g, h, i)
insert into Table (, , .... VALUES j, k, l)
insert into Table (, , .... VALUES m, n, o)
|
by: anurag |
last post by:
Hi,
Is "MERGE INTO..." statement allowed to be executed in a dynamic SQL
statement, inside an SQL Stored Procedure?
I am using DB2 UDB 8.1.2 on Windows.
If it is not allowed (as I suspect), how do I go about converting an
Oracle Procedure that dynamically executes "MERGE INTO..."?
ONE RESTRICTION: My converted code has to execute faster than the
Oracle SP (everything else being the same).
|
by: Mark Harrison |
last post by:
So I have some data that I want to put into a table. If the
row already exists (as defined by the primary key), I would
like to update the row. Otherwise, I would like to insert
the row.
I've been doing something like
delete from foo where name = 'xx';
insert into foo values('xx',1,2,...);
| |
by: Takeadoe |
last post by:
Dear NG - I've got a bunch of SQL statements (Insert Into "Access Table
Name") generated from another software program. They look like this:
Pasting these lines into the SQL view as they are generates a couple of
different errors (missing semicolon being one of them). They work fine
if I paste 1 line at a time. Can someone tell me what I need to do to
remedy the situation? I can't hardly paste 1 at a time! Your help is
very much...
|
by: Bart op de grote markt |
last post by:
Hello
I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about...
|
by: ewpatton |
last post by:
Good day,
I've been trying to work with SQL and an Access database in order to
handle custom user profiles. I haven't had any trouble reading from my
database, but inserting new entries into it has been troublesome to
say the least.
My ASP.NET script is supposed to execute an INSERT INTO statement in
order to add a user to the database. Here is a sample:
|
by: paulquinlan100 |
last post by:
Hi
I'm using automation in excel to insert a lot of data into an Access
DB. Its all working fine, apart from the fact that the data entry
people occasionally put "n/a" in the cells instead of 0, which
obviously access doesnt like being put into a number field.
I know i can put if statements around every variable to check, but is
there a way to force it to just put the default value for that
particular field (mostly 0, but sometimes it...
|
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: 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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |