Run time error '3420': Object invalid or no longer set. (1 Viewer)

[David44Coder]

Having trouble getting this code to work. It should add fieldname to HardDrives but just shows error 3420
What have I done wrong?

Sub AddField(fieldname)
    Dim MyT As TableDef: Dim MyF As Field
    
    Set MyT = CurrentDb("HardDrives")
    Set MyF = MyT.CreateField(fieldname, DB_TEXT, 255)
    MyT.Fields.Append MyF

End Sub

 

[theDBguy]

Try:

Set MyT = CurrentDb.TableDefs("HardDrives")

If that doesn't work, you might have to use another variable for the CurrentDb.

 

[David44Coder]

Thank you! Set MyT = CurrentDb.TableDefs("HardDrives") didn't change anything but your suggestion about
using a variable for CurrentDb did.

 

[David44Coder]

Hmmm I have a further problem, My intent was to use the serial number of various HDD as the field name.
But on line MyT.Fields.Append MyF
Run—time error '3125':
'0025_38D3_21C0_9E90.' is not a valid name. Make sure that it does
not include invalid characters or punctuation and that it is not too long.
Is it known what constitutes a valid field name or length ?

 

[theDBguy]

Hmmm I have a further problem, My intent was to use the serial number of various HDD as the field name.
But on line MyT.Fields.Append MyF
Run—time error '3125':
'0025_38D3_21C0_9E90.' is not a valid name. Make sure that it does
not include invalid characters or punctuation and that it is not too long.
Is it known what constitutes a valid field name or length ?

Double-check what the serial number is and make sure it doesn't contain any invalid characters.

 

[The_Doc_Man]

Guidelines for naming fields, controls, and objects - Microsoft Support

Use the following guidelines when naming fields, controls or objects in your Access database.

support.microsoft.com

This link will explain the rules for field names. HOWEVER... the idea of using data as a field name is very much not good. What is wrong with using DriveSerial as the field name and "0025_38D3_21C0_9E90" as the field data? Among other things, the NEXT time you do this, you will add a new field name that no other disk should have, but the drive's serial number will not be searchable because EVERY RECORD will have EVERY DRIVE SERIAL as a field name. Adding a field isn't a single-record operation - it's a whole-table operation. And you had better hope you never get more than 255 drives, because you can only have 255 fields in a record. Worse, since you are giving it a 255-byte short text, you can only have 16 fields like that before you blow out the record-length limitation. If you have as many as 16 drives, you are done.

Access specifications - Microsoft Support

Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table.

support.microsoft.com

 

[David44Coder]

I'm not quite sure how to reply Doc_Man as you've blown me away with that !
The period in the fieldname was the error. That aside, my plan was a lookup table for HDD backups.
The Drive serial number would be the fieldname and each foldername in that drive would be a new record in that field.
Is there something wrong with that logic ? I guess there must be but I can't follow exactly what you mean.
Can you not retrieve every record for a given fieldname in a query?

 

[The_Doc_Man]

You are right, I didn't notice the dot until you pointed it out but of course the linked article would explain it for you.

I suspect you are confusing container and content here. Since I can't read your mind, I have to suppose that you were designing something using an Excel-like mind-set. Access is oriented towards columnar displays so often resembles Excel worksheets - but it is not Excel.

I'll try to explain this referring to what you showed us and told us so far. There are some basic issues that come up in what you wrote. This next sequence of yours will create a persistent new field in an existing table.

    Set MyT = CurrentDb("HardDrives")
    Set MyF = MyT.CreateField(fieldname, DB_TEXT, 255)
    MyT.Fields.Append MyF

From your description in post #4, that was in fact your intent. But if your goal was to have a single drive record for which one of the elements was the drive's serial number, you can't search just the table for that field. Well, you can.. but it won't work the way you appear to want it. More specifically, if you add a field that contains the drive's serial number as a field name, then EVERY RECORD will have that same serial number as a field name because adding a field to a table is a "global" operation - it affects every record in the table. What will you do with that? If you then search for records with that as a field name, they ALL will have that name. If instead you make a fixed field name called DriveSN or something like that, then put the serial number as data in that field in a single record, you have a searchable field (called DriveSN) that should only point to a single record - which is what databases normally do for a search operation.

Adding a field name to a table has implications due to the fact that Access has data storage limits. You cannot have more than 255 fields. So if you add 255 drive records using the serial number as a field name, the 256th drive will fail irrevocably on field capacity. Sooner if you have other fields that aren't the drive serial number.

Also, if you add 16 drive records with the serial number as a name, the other 15 records each were ALSO added and will persist, meaning that each of those 255-byte short text fields you added will accumulate in contribution to the record size. An Access record is limited to 4000 bytes total not counting certain LontText and LongBinary field types (Memo and Attachment, e.g.) So adding a 17th drive using the drive serial number as a field name blows away the record-length limit.

It is time to step back and perhaps talk to us in common language rather than code to explain your intent. Stay away from field descriptions. Talk to us about concepts.

 

[The_Doc_Man]

On further review, it would APPEAR that perhaps your problem is that you would need parent/child tables to match your goals. But I want to see a description of what you wanted before I make any more guesses. (I've been known to jump to confusions before.)

 

[Josef P.]

Cause of error 3420:
TableDef needs an existing reference to DAO.Database.
But this is already gone in the code from #1 after Set MyT = CurrentDb("HardDrives").
=> Suggestion from theDBguy in #2

use another variable for the CurrentDb.

Sub AddField(fieldname)

    Dim MyT As TableDef: Dim MyF As Field
    Dim db As DAO.Database

    Set db = CurrentDb  ' <-- Holds the reference until db = nothing or the procedure is exited.
    Set MyT = db.TableDefs("HardDrives")
    Set MyF = MyT.CreateField(fieldname, DB_TEXT, 255)
    MyT.Fields.Append MyF

End Sub

or

Sub AddField(fieldname)

    Dim MyT As TableDef: Dim MyF As Field

    With CurrentDb ' <-- holds the reference until End With
      Set MyT = .TableDefs("HardDrives")
      Set MyF = MyT.CreateField(fieldname, DB_TEXT, 255)
      MyT.Fields.Append MyF
    End With

End Sub

 

[Gasman]

Hmmm I have a further problem, My intent was to use the serial number of various HDD as the field name.
But on line MyT.Fields.Append MyF
Run—time error '3125':
'0025_38D3_21C0_9E90.' is not a valid name. Make sure that it does
not include invalid characters or punctuation and that it is not too long.
Is it known what constitutes a valid field name or length ?

Surely you would have a field perhaps called HardDiskName and would populate that with the name of the hard disk?

 

[David44Coder]

Doc man, this was what threw me
> if you add a field that contains the drive's serial number as a field name, then EVERY RECORD will have that same serial number as a field name because adding a field to a table is a "global" operation.

Because I was sure each field was different because its field name was different.
But with more of my code working I can see what you must have been describing. The First field worked as expected, the second field had the next field name, but the new data didn't start at the first row but at the first empty row.

> If instead you make a fixed field name called DriveSN or something like that, then put the serial number as data in that field in a single record, you have a searchable field (called DriveSN) that should only point to a single record - which is what databases normally do for a search operation.

So each drives Folder names, instead of being in a list, would be in row? What would be each columns field name?

This mightn't be so good for reading but no doubt there's some way to display it as a list. My idea was just to open the table.
I have about 20 USB drives with backup of backups..a big mess and want to consolidate them. The idea was this would display all the folder names at once. I bought an app called FolderMatch which lets you update and sync folders nicely.

I'll stop doing what I was and try your suggestion. I'm still a bit hazy but that might change as I see results
Josef_P I had changed the code to your Example 1. Although I may not need to add fields now.

 

[Gasman]

IMO, each folder would be in a row on it's own, with the harddisk identifier/name

 

[Josef P.]

I'll stop doing what I was and try your suggestion. I'm still a bit hazy but that might change as I see results
Josef_P I had changed the code to your Example 1. Although I may not need to add fields now.

My hint was only to explain the error. It was not a call to leave the data model bad.

[OT]
matching the title: Run time error '3420': Object invalid or no longer set. .. a website worth reading

 

[The_Doc_Man]

Doc man, this was what threw me
> if you add a field that contains the drive's serial number as a field name, then EVERY RECORD will have that same serial number as a field name because adding a field to a table is a "global" operation.

Because I was sure each field was different because its field name was different.
But with more of my code working I can see what you must have been describing. The First field worked as expected, the second field had the next field name, but the new data didn't start at the first row but at the first empty row.

> If instead you make a fixed field name called DriveSN or something like that, then put the serial number as data in that field in a single record, you have a searchable field (called DriveSN) that should only point to a single record - which is what databases normally do for a search operation.

So each drives Folder names, instead of being in a list, would be in row? What would be each columns field name?

This mightn't be so good for reading but no doubt there's some way to display it as a list. My idea was just to open the table.
I have about 20 USB drives with backup of backups..a big mess and want to consolidate them. The idea was this would display all the folder names at once. I bought an app called FolderMatch which lets you update and sync folders nicely.

I'll stop doing what I was and try your suggestion. I'm still a bit hazy but that might change as I see results
Josef_P I had changed the code to your Example 1. Although I may not need to add fields now.

You need to look up parent/child relationships. A relational database fully supports such concepts. When you talk about lists and rows, you may have a correct picture in your mind, but I keep coming back to the sound of Excel stepping into the conversation. I am guessing you want to store something related to backing up multiple folders on hard drives. It may not be what you want, but this is how I might store this in an Access hierarchy of tables.

table tDisks: DriveID (Autonumber primary key), DriveName (could be drive letter), SystemName (if you are backing up disks on more than one system), DriveSerial (your pesky serial number), DriveSize (KB or GB or whatever), other things solely related to a disk drive - but not content

table tFolders: FolderID (Autonumber primary key), DriveFK (foreign key to tDisks: DriveID), FolderName (or full folder path on drive), other things solely related to that folder

table tBackups: BackupID( Autonumber primary key), FolderFK (foreign key to tFolders: FolderID), info about when & where the backup was made - possibly including container-file names OR tape-cartridge IDs

From your confusion, it is clear that you would benefit from studying Database Normalization before you try to do too much in the wrong direction - which is where you WERE headed, but maybe we've caught you in time. IF you want to do some reading, you can search THIS forum for Normalization (because this IS a database forum) or you can search the web for Database Normalization (because by itself, the word "Normalization" appears in at least 6 major disciplines including Chemistry, Math, Psychology, Diplomacy, and a few more topics).

Once you've done that, you need to study JOIN queries because when we say "Relational Database" the mechanism that expresses relationships is the JOIN query.

FYI, I'm in USA Central Time Zone, in case you are trying to figure out my presence on the forum, which is approximately random anyway.

 

[David44Coder]

This would have been a breeze in Excel and become exactly what was wanted, but I'm intrigued by Access and I like the way data is saved automatically without the whole thing needing saved. I've read bits on normalization but it's never made a lot of sense and things like NorthWind examples just don't cut it. My brain must work differently. But I can learn by doing. This may be a good test project and you've given me some table structures, that is if you're happy to add to help, which I don't want to assume. (btw I'm in the south pacific). I'll first have a go making those tables as you've outlined. I'm really flying blind and don't yet see where the actual folder names will go, or if every drive has it;s own table. Some, like drive letters I won't need as it may be the same for each, or whsatever Windows assigns as they're plugged in.

 

[NauticalGent]

matching the title: Run time error '3420': Object invalid or no longer set. .. a website worth reading

 

[The_Doc_Man]

don't yet see where the actual folder names will go, or if every drive has it;s own table. Some, like drive letters I won't need as it may be the same for each, or whsatever Windows assigns as they're plugged in.

Your point is well taken, because many drive-like devices are plugged in via any USB port that happens to be open at the time. However, there are such things as volume names that move with the memory stick or USB HDD box. I don't know which version of Windows you use, but these ideas in the link below would let you provide a name/label for a thumb drive. This would not change regardless of where the device is plugged in. If you were using the File System Object you could read the volume label easily enough to identify which stick is being copied.

6 Ways to Change a Volume Label on Windows 11

Learn how to give your drives unique names and distinguish them from one another.

www.makeuseof.com

The question I answered assumed fixed-position drives on multiple systems, not thumb drives, so my tables MIGHT be a little bit of overkill - or not. When I was with the U.S. Navy, we had multiple fixed drives to make backups so I was thinking "bigger system."

 

[GregDataReno]

A side note... when calling CurrentDB(), it's safer to assign it to a declared variable and then use that variable in subsequent code rather than calling CurrentDB again. There are circumstances where CurrentDB is not the database you intend it to be - especially when working with multiples instances. This local Forum thread may be helpful.

Also, I believe the use of DBEngine(0)(0) is not advisable in a multi-user environment according to this 2022 article at Microsoft

 

[The_Doc_Man]

Concur with GregDataReno in that using CurrentDB repeatedly, things aren't stable whether or not the DB backend is currently being actively shared. I.e. more than one user is doing things. Even for the single-user case, repeatedly referencing CurrentDB presents a moving target that gets re-evaluated each time you use it. As a simple-minded example,

CurrentDB.Execute "... some UPDATE query"
lngX = CurrentDB.RecordsAffected

lngX will be 0 no matter how many records were updated, because it is derived from a re-evaluation of CurrentDB from a time AFTER the update query was executed. Whereas if you capture that DB reference in a variable and use it for the query and the affected record count, there is continuity and you can get a count of affected records.