Insert records update key

[Robert H]

A test and evaluation system we use is built around Access database.
It does not utilize the Access relational database structure but
instead just two tables: CDD which is the test configurations and MEA
which is the measured data. The common key is DeviceID which is a
sequential number.

Each record in the CDD table contains User information; test points,
pin numbers, etc and variables for the test system; voltage settings,
range scales, etc. An alphabetical "DeviceConfig" (A, B, C etc) field
is the primary test identifier from the operators viewpoint. There is
also a "DeviceCode" that that provides higher level grouping for the
DeviceConfigs. For example deviceCode "Module 01" may have configs A-
R and "Module 02" could also have configs A-R.

Sometimes after testing we realize we need to add a test
configuration. It is simple enough to just enter a new DeviceID to
the end of the sequence but if we need to inset something in the
logical testing sequence, for instance between Config "C" and "D" call
the new config "C1" or even call it "C" and rename the original C
though R to D though S, we are at a delima because we need to go into
the MEA table and update the DeviceIDs. We have resorted to exporting
both tables to Excel and editing that data and importing back to
Access. This is a cumbersome process and one that if you are not on
your best game can create a data error that may result in a
misdiagnosis down the road...

It is important during testing for the tests to sequence in
DeviceConfig order however the program sequences on the DeviceID. We
are not at liberty to modify the program so we need a method to easily
and safely modify the database

Is there a way, using ACCESS VBA to do this. My limited VBA
experience is in Excel and Visio. Being that Access does not have a
recorder I don't know where to start.

Any help will be appreciated
Robert

 

[Ken Snell \(MVP\)]

In ACCESS, one would either modify the data directly in the table (open it
in datasheet view), which would be similar to what you're doing in EXCEL.

Alternatively, one would run a series of update queries to modify one record
at a time (cumbersome), or run an update query that changes ech record's
DeviceConfig value to the next highest letter (using a crtierion that does
this only for letters already at or above a certain letter); and then run an
append query to add the new record to the table.

The first method is prone to similar errors as what you do now.

The second method requires you to modify/edit a query (once it's been built)
and run it to update the data, and then to add a new record.

How many records are in your table -- in other words, how many records need
to be changed?
--

Ken Snell
<MS ACCESS MVP>

 

[Robert H]

Thanks for the reply Ken,

How many records are in your table -- in other words, how many records
need
to be changed?

The CDD table has 567 records, 36 Modules x 16 configurations (it's
actually configurations A - P). In this case there are 36 of each
configuration

The current MEA table has 257 records but will swell considerable once
we measure data on a complete array (so far only a few modules on two
ships have been measured). But consider five ships with the complete
array measured each year.

The second method requires you to modify/edit a query (once it's been
built)
and run it to update the data, and then to add a new record.

Is this done through VBA or is possible within the query design?

 

[Robert H]

Thanks for the reply Ken,

How many records are in your table -- in other words, how many records need
to be changed?

The CDD table has 567 records, 36 Modules x 16 configurations (it's
actually configurations A - P). In this case there are 36 of each
configuration

The current MEA table has 257 records but will swell considerable once
we measure data on a complete array (so far only a few modules on two
ships have been measured). But consider five ships with the complete
array measured each year.

The second method requires you to modify/edit a query (once it's been built)
and run it to update the data, and then to add a new record.

Is this done through VBA or is possible within the query design?

 

[Ken Snell \(MVP\)]

Let's start with a generic query that could be used to update the CDD table
by "upping" the configuration letter by one for all letters above a
specified letter, for just one module.

UPDATE CDD
SET CDD.ConfigurationLetter =
Chr(Asc(CDD.ConfigurationLetter)+1)
WHERE CDD.ModuleID = XMID
AND CDD.ConfigurationLetter > "CLID";


In the above, you would replace XMID with the actual value of the ModuleID
for the records to be changed (the query above assumes that ModuleID is a
numeric value; if it's a text value, put " characters on either side of the
actual value that you use for XMID). Also, you'd replace CLID with the
letter that is the highest one NOT to be changed; in other words, if you
want letters D through P changed, you'd use the letter C for CLID in the
above query.

You can create a new query in design view, and then don't select any table.
Close the table selection window. Then select SQL View from icon at left
edge of toolbar. Paste the above SQL statement into the window.



If you're just adding a single new record, you might be better to just do
that in datasheet view of the table after you run the above update query.
It'll be just as much work as editing an append query, I think.

 

[Robert H]

the line

Set cdd.ConfigurationLetter = chr(Asc(cdd.ConfigurationLetter)
+ 1)

returns an "object required" error

So figuring cdd being a table is probably an object I added

Dim cdd As Object

then I get - Object variable or With block variable not set (Error
91)

Its clear to me I do not have a good understanding of the Access
object model...

 

[Robert H]

Thanks Ken, your instructions for building the query were strait
forward but Running the query
Enter parameter value box: CDD.configurationletter
I input B

Enter parameter value box: CDD.ModuleID
I input "Module 01"

Seems right but then I get Running the query

Enter parameter value box: CDD.configurationletter
I input B

Enter parameter value box: CDD.ModuleID
I input "Module 01"

Seems right but then I get

Enter parameter value box: XMID
I input "Module 01"

Then I get "Operation Must Use and Updatable query"

 

[Ken Snell \(MVP\)]

Are you trying to enter the SQL statement that I provided into VBA code? I
was providing information on how to create a saved query.

I used generic names for the fields that contain the "configuration letter"
and the "module value" in your table; my generic names are
"ConfigurationLetter" and "ModuleID". You need to change these to the
correct field names from your table.

I need more information about what you're trying to do with the query SQL
statement that I provided?

 

[Robert H]

Are you trying to enter the SQL statement that I provided into VBA code? I

was providing information on how to create a saved query.

Its in a query named "Insert Config"

Upodate after your last post, is looks like

UPDATE CDD SET CDD.DeviceConfig = Chr(Asc(CDD.DeviceConfig)+1)
WHERE CDD.DeviceCode="Module 01" and CDD.DeviceConfig>"B";

and it works, just testing different things I found that by removing:
CDD.DeviceCode="Module 01" and
It runs on all modules...

 

[Robert H]

So I think the next step is to insert the new record(s) which should
probably appended from a table. However, it needs to be done in a way
that the new record(s) bump the Primary Key "DeviceID". Like so:

Befor
DeviceID DeviceCode DeviceConfig DeviceType etc...
1 Module 01 A LOAD 1
2 Module 01 B LOAD 2
3 Module 01 C +11
4 Module 01 D -11

After

DeviceID DeviceCode DeviceConfig DeviceType etc...
1 Module 01 A LOAD 1
2 Module 01 B LOAD 2
3 Module 01 C LOAD 1 to 2
4 Module 01 D +11
5 Module 01 E -11

Keeping in mind that the MEA table needs to be syncronised so the
existing records DeviceIDs will follow their counterparts in the CDD
table

 

[Ken Snell \(MVP\)]

Probably would be better if you modify the update query that we worked out
before so that the DeviceID value is also incremented:

UPDATE CDD SET CDD.DeviceConfig = Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID + 1
WHERE CDD.DeviceCode="Module 01" and CDD.DeviceConfig>"B";

This then will allow you to insert a record and use the "missing" DeviceID
value. (The above assumes that DeviceID is a numeric field, not an
autonumber field. If it's an autonumber field, then you will not be able to
"bump" the DeviceID values.)

I still am not understanding why you feel it's necessary to use an append
query instead of manually inserting data after you run the update query?
"Finding" the missing letter and DeviceID values for an append query to use
is going to require some complicated query work, and you'll still need to
provide data for the DeviceType and other fields, no? Can you provide more
information?

 

[Robert H]

Probably would be better if you modify the update query that we worked out
before so that the DeviceID value is also incremented:

UPDATE CDD SET CDD.DeviceConfig = Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID + 1
WHERE CDD.DeviceCode="Module 01" and CDD.DeviceConfig>"B";

I was expeimenting with this (just copied the code you provided and
chnaged the update field...) but im noticeng that I end up with just
the module 01 stuff updated so that when deviceID 4 gets bumped to 5
the is now two device ID 5s. I was trying to figurout a way to have it
bump everything after the first instance of Module 01 and B. I tried
using the First function but so far it seems I am trying to employ it
inapropriatly.

I still am not understanding why you feel it's necessary to use an append
query instead of manually inserting data after you run the update query?
"Finding" the missing letter and DeviceID values for an append query to use
is going to require some complicated query work, and you'll still need to
provide data for the DeviceType and other fields, no? Can you provide more
information?

yes I can manualy insert the new records afterwords. But I want to
avoid to error potential of the user (me and a few coworkers) missing
entering one of the new CDD records. There will be other data to
enter as you identified but if the placeholders, if you will, are in
place that can be safely done. My thought is to use a form to run
this query that I input the Config I want to bump....

But for now I think it is best to get the update to work and worry
about the append part later and as you have suggested, manualy enter
the new configs.

So after figuring out how to update all records after the first
occurance I think the critical item is synchonisinhg the old records
in the MEA table with the new DevideIDs. Thats a mystery to me.

One step at a time though

thanks for your help Ken

 

[Ken Snell \(MVP\)]

I guess I'm not understanding all your data structure -- from what you
posted, you should not end up with duplicate DeviceID values, you should
have a "gap" after the update query runs. For example:

BEFORE UPDATE QUERY:
DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD 1
2 Module 01 B LOAD 2
3 Module 01 C +11
4 Module 01 D -11


If you run this query:

UPDATE CDD SET CDD.DeviceConfig = Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID + 1
WHERE CDD.DeviceCode="Module 01" and CDD.DeviceConfig>"B";



AFTER UPDATE QUERY:
DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD 1
2 Module 01 B LOAD 2
4 Module 01 D +11
5 Module 01 E -11


So I am thinking there is more complexity to your data than I see here?

 

[Robert H]

BEFORE UPDATE QUERY:

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD 1
2 Module 01 B LOAD 2
3 Module 01 C +11
4 Module 01 D -11

If you run this query:

UPDATE CDD SET CDD.DeviceConfig = Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID + 1
WHERE CDD.DeviceCode="Module 01" and CDD.DeviceConfig>"B";

AFTER UPDATE QUERY:
DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD 1
2 Module 01 B LOAD 2
4 Module 01 D +11
5 Module 01 E -11

That part works, however, often, and in the case of the current
project I will need to bump Config C for all module. SO is I modify
the sql to:

UPDATE CDD SET CDD.DeviceConfig = Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID + 1
WHERE CDD.DeviceConfig>"B";
(removing the CDD.DeviceCode criteria) everything is fine for the
first module but any remaining module gets duplicates in the DeviceID

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD
2 Module 01 B LOAD
4 Module 01 D +11
5 Module 01 E -11
5 Module 02 A LOAD
6 Module 02 B LOAD
8 Module 02 D +11
9 Module 02 E -11
9 Module 03 A LOAD
10 Module 03 B LOAD
12 Module 03 D +11
13 Module 03 E -11

any configuration lower in the count than the criteria doesnt advance,
in this case A, so when the fourth record in Module 01 advances, it
duplicates the first item in Module 02...

 

[Ken Snell \(MVP\)]

Post an example of the data that you had before running the update query
without the Module being specified in the WHERE statement. I want to see the
before and after of your example.

Is DeviceID supposed to be unique for each record?

 

[Robert H]

Post an example of the data that you had before running the update query
without the Module being specified in the WHERE statement. I want to see the
before and after of your example.

Before:

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD
2 Module 01 B LOAD
3 Module 01 C +11
4 Module 01 D -11
5 Module 02 A LOAD
6 Module 02 B LOAD
7 Module 02 C +11
8 Module 02 D -11
9 Module 03 A LOAD
10 Module 03 B LOAD
11 Module 03 C +11
12 Module 03 D -11

After:

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD
2 Module 01 B LOAD
4 Module 01 D +11
5 Module 01 E -11
5 Module 02 A LOAD
6 Module 02 B LOAD
8 Module 02 D +11
9 Module 02 E -11
9 Module 03 A LOAD
10 Module 03 B LOAD
12 Module 03 D +11
13 Module 03 E -11

Is DeviceID supposed to be unique for each record?

Yes, it is Unique in the CDD table but can have many instances in the
MEA table depending on how many times the test that the CDD record
represents is ran.

Thanks
Robert

 

[Ken Snell \(MVP\)]

OK -- try this update query:

UPDATE CDD SET CDD.DeviceConfig = Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID + 1
WHERE (CDD.DeviceCode="Module 01" And CDD.DeviceConfig>"B")
OR (CDD.DeviceCode > "Module 01");

 

[Robert H]

With
UPDATE CDD SET CDD.DeviceConfig = Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID+1
WHERE (((CDD.DeviceConfig)>"B") AND ((CDD.DeviceCode)="Module 01")) OR
(((CDD.DeviceCode)>"Module 01"));

Results in:

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD
2 Module 01 B LOAD
4 Module 01 D +11
5 Module 01 E -11
6 Module 02 B LOAD
7 Module 02 C LOAD
8 Module 02 D +11
9 Module 02 E -11
10 Module 03 B LOAD
11 Module 03 C LOAD
12 Module 03 D +11
13 Module 03 E -11




I experimented with your WHERE modification:
UPDATE CDD SET CDD.DeviceConfig = Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID+1
WHERE (((CDD.DeviceConfig)>"B") AND ((CDD.DeviceCode)="Module 01")) OR
(((CDD.DeviceConfig)>"B") AND ((CDD.DeviceCode)>"Module 01"));

Resulting in:

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD
2 Module 01 B LOAD
4 Module 01 D +11
5 Module 01 E -11
5 Module 02 A LOAD
6 Module 02 B LOAD
8 Module 02 D +11
9 Module 02 E -11
9 Module 03 A LOAD
10 Module 03 B LOAD
12 Module 03 D +11
13 Module 03 E -11

Back to bumping "C" as desired but the original ID problem...

Still experimenting...

 

[Robert H]

UPDATE CDD SET CDD.DeviceConfig = Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID + 1
WHERE (CDD.DeviceCode="Module 01" And CDD.DeviceConfig>"B")
OR (CDD.DeviceCode > "Module 01");

Results in:

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD
2 Module 01 B LOAD
4 Module 01 D +11
5 Module 01 E -11
6 Module 02 B LOAD
7 Module 02 C LOAD
8 Module 02 D +11
9 Module 02 E -11
10 Module 03 B LOAD
11 Module 03 C LOAD
12 Module 03 D +11
13 Module 03 E -11

After Module 01, "A" gets pushed and the ID only gets pushed in the
first instance

I tried:
UPDATE CDD SET CDD.DeviceConfig = Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceID = CDD.DeviceID+1
WHERE (((CDD.DeviceConfig)>"B") AND ((CDD.DeviceCode)="Module 01")) OR
(((CDD.DeviceConfig)>"B") AND ((CDD.DeviceCode)>"Module 01"));

Results in:

DeviceID DeviceCode DeviceConfig DeviceType
1 Module 01 A LOAD
2 Module 01 B LOAD
4 Module 01 D +11
5 Module 01 E -11
5 Module 02 A LOAD
6 Module 02 B LOAD
8 Module 02 D +11
9 Module 02 E -11
9 Module 03 A LOAD
10 Module 03 B LOAD
12 Module 03 D +11
13 Module 03 E -11

Which pushed the correct config, "C", but the old ID problem returns

Still experimenting...

Thanks

 

[Ken Snell \(MVP\)]

OK - I think I see the "final" fix:

UPDATE CDD
SET CDD.DeviceConfig =
IIf(CDD.DeviceCode > "Module 01",Chr(Asc(CDD.DeviceConfig)+1),
CDD.DeviceConfig),
CDD.DeviceID = CDD.DeviceID + 1
WHERE (CDD.DeviceCode="Module 01" And CDD.DeviceConfig>"B")
OR (CDD.DeviceCode > "Module 01");