UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Deleting A Field, Any Version    
 
   
azizrasul
post May 23 2019, 06:34 AM
Post#1



Posts: 1,458
Joined: 18-July 00
From: Faisalabad, Pakistan


Is their any code that when deleting a single field in a MS Access table (using code), the internal count is reduced by 1?
This post has been edited by azizrasul: May 23 2019, 06:35 AM

--------------------
Aziz
Go to the top of the page
 
theDBguy
post May 23 2019, 06:44 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


Can you show us the code?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gemmathehusky
post May 23 2019, 06:54 AM
Post#3


UtterAccess VIP
Posts: 4,668
Joined: 5-June 07
From: UK


I am sure there will be some syntax to get at the field count, like.

CODE
currentdb.tabledefs("tablename").fieldcount


You can also get the ordinal position of individual fields within the table, with a slight variation

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
orange999
post May 23 2019, 07:04 AM
Post#4



Posts: 1,918
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Can you tell us more about the "internal count"?

This worked in my testing.
CODE
Sub DropAField()

    Dim dbs As Database

10  Set dbs = CurrentDb

    'Drop  any index that involves the field to be removed
20  dbs.Execute "DROP INDEX Zipcode ON tblCustomers;"

    'Drop the specified column from the Table
30  dbs.Execute "alter table tblcustomers DROP column zipcode;"

40  dbs.Close

End Sub

--------------------
Good luck with your project!
Go to the top of the page
 
azizrasul
post May 23 2019, 07:54 AM
Post#5



Posts: 1,458
Joined: 18-July 00
From: Faisalabad, Pakistan


Here is the code that I use.

CODE
Public Sub DeleteField(strTableName As String, strFieldName As String)

    Dim rst As DAO.Recordset
    Dim i As Integer
    
    On Error GoTo ErrorHandler
    
    With CurrentDb
        Set rst = .OpenRecordset(strTableName, dbOpenDynaset)
        For i = 0 To rst.Fields.Count - 1
            If rst.Fields(i).Name = strFieldName Then
                rst.Close
                Set rst = Nothing
                .Execute "ALTER TABLE [" & strTableName & "] DROP COLUMN [" & strFieldName & "];"
                Exit For
            End If
        Next i
    End With

ErrorHandler:
    If Err.Number = 3265 Then
        Exit Sub
    ElseIf Err.Number = 3008 Then
        DoCmd.Close acTable, strTableName, acSavePrompt
        Set rst = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset)
        Resume Next
    ElseIf Err.Number <> 0 Then
        DoCmd.RunMacro "mcrWarningsONHourGlassOFF"
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "basUtilities - DeleteField"
    End If
    
End Sub


If I have say 5 fields in Table1 and I create a field using code then delete it using the code above, and I keep doing that until I have created 250 fields and deleted them, the internal count will now be 255 and I can't create another field even though only 5 fields are showing in design view. If I compact and repair the db, then the internal field count will be reset to 5.

Hence my question is, whether it is possible to delete a field and the internal field count be automatically reduced accordingly so that I don't have to compact and repair the db whenever the internal count goes to 255.

--------------------
Aziz
Go to the top of the page
 
theDBguy
post May 23 2019, 10:11 AM
Post#6


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


Hi Aziz. I am not sure why you're doing this and also not sure what is the answer to your question. But if you're creating and deleting fields, you must not care about the data then. If so, then perhaps, as a workaround, when you reach the 255 limit, you could just simply create a new table and start over. Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
azizrasul
post May 23 2019, 10:24 AM
Post#7



Posts: 1,458
Joined: 18-July 00
From: Faisalabad, Pakistan


I do care about the data in the first 5 columns. I'm creating 3 new columns and then deleting them. When I delete the 3 columns and re-create them, the internal count goes up. I guess what I need is code that when the field is deleted, the internal count adjusts accordingly which at the moment I feel it won't until you compact.

To get round the issue, I'm simply giving a message to say compact the database and re-open. I guess I could make a copy of the table and delete the original and rename the new table. Not sure whether this will mess up my table relationships.

--------------------
Aziz
Go to the top of the page
 
theDBguy
post May 23 2019, 10:34 AM
Post#8


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


Hi. I still don't have any idea how to tackle the field limit but perhaps you could try the following approach in the mean time.

1. Keep the original table with the first three columns intact to keep your table relationships working
2. Use a Make-Table query to create a one-to-one relationship between your original table and a temp table
3. Use the temp table to do your field adds and deletes
4. Repeat steps 2 and 3 when you hit the limit or each time the app is opened making sure to delete the previous temp table first

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
azizrasul
post May 23 2019, 10:38 AM
Post#9



Posts: 1,458
Joined: 18-July 00
From: Faisalabad, Pakistan


Yeh, that sound's like a good plan. I'll try that. Many thanks.

--------------------
Aziz
Go to the top of the page
 
theDBguy
post May 23 2019, 10:50 AM
Post#10


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


Hi Aziz. You're welcome. However, there is one more thing I'd like to say. No doubt you probably have tried to research a solution to the field limit problem you're getting. I am not sure what you found, if any at all, but I would say I don't notice this type of question often, probably because not a lot of people are doing anything like you're doing. So, with that in mind, if you describe exactly what you're doing, maybe somebody have already run into the same situation before and can tell you what they did to avoid the problem (instead of fixing it, because they avoided it).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
azizrasul
post May 23 2019, 11:04 AM
Post#11



Posts: 1,458
Joined: 18-July 00
From: Faisalabad, Pakistan


Well what I'm doing is to encrypt data in any of the 5 columns of data. The way I'm doing this may not be the best way, but the new fields hold the necessary data that enables the encryption to take place. By running the code over various times, I encrypt the data even more. This causes the 255 limit to be reached. The only reason I got to 255 was in the testing stage where I had to delete fields many times so it not be a situation that will arise, but could. Hence the belts and braces approach. I hate things falling over and then going back to fix them.

The data that holds the key to decrypting the data can then be stored as a spreadsheet etc.

--------------------
Aziz
Go to the top of the page
 
gemmathehusky
post May 23 2019, 12:18 PM
Post#12


UtterAccess VIP
Posts: 4,668
Joined: 5-June 07
From: UK


While we are on the subject of delete methods, I would personally stay with DAO

CODE
currentdb.tabledefs("mytable").fields.delete "fieldname"


note also, that this works with the current database, not linked tables. Not sure about the other examples.


incidentally, I think the syntax for the field count is

CODE
currentdb.tabledefs("mytable").fields.count


--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
kfield7
post May 23 2019, 03:53 PM
Post#13



Posts: 961
Joined: 12-November 03
From: Iowa Lot


Interesting, so you're encrypting encrypted data, and it appears you don't need to retain the previous data set.
Why not:
3 fields for the key
5 fields for "new" encrypted data
5 fields for "old" data

1. if data exists in "old" set, delete data in "old" set.
2. copy data from "new" set to "old" set.
3. encrypt "old" set to "new" set.
4. optionally, repeat (1).

So you never have more than 13 fields, you don't add or delete fields, only data.
Go to the top of the page
 
azizrasul
post May 24 2019, 03:20 AM
Post#14



Posts: 1,458
Joined: 18-July 00
From: Faisalabad, Pakistan


I understand what you are saying kfield7, but the main purpose of my thread was that I didn't realise that the internal field count didn't decrease when a table field was deleted. So I was curious to see whether there WAS a way to delete a table field and decrease the internal count at the same time.

--------------------
Aziz
Go to the top of the page
 
gemmathehusky
post May 24 2019, 06:54 AM
Post#15


UtterAccess VIP
Posts: 4,668
Joined: 5-June 07
From: UK


why do you think the field count doesn't reduce when you delete a field?


I just created a table called table1, with 5 fields, one on which was called "field2"


CODE
Sub testfields()

    MsgBox CurrentDb.TableDefs("table1").Fields.Count
     'reports 5
    
    CurrentDb.TableDefs("table1").Fields.Delete "field2"
    MsgBox "field 2 deleted"

    MsgBox CurrentDb.TableDefs("table1").Fields.Count
    'reports 4

End Sub

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
cheekybuddha
post May 24 2019, 07:32 AM
Post#16


UtterAccess VIP
Posts: 11,270
Joined: 6-December 03
From: Telegraph Hill


@Dave,

I think the issue is with creating and deleting fields. Access maintains an internal counter where you can add a maximum of 255 fields to a table (much like a form with controls). With a table it can be reset with a Compact and Repair, but until then, even if you add and delete fields, once you have added 255 you won't be able to add any more.

Of course, in any normal circumstance this is a non-issue!

d

--------------------


Regards,

David Marten
Go to the top of the page
 
DanielPineault
post May 24 2019, 07:55 AM
Post#17


UtterAccess VIP
Posts: 6,635
Joined: 30-June 11



Continuously creating and deleting objects, fields, controls all increase internal counters and such techniques, will always lead to hitting some Access limit, but can always be avoided with redesign.

Do you really need to create/delete fields? Why not simply blank the values, but keep the fields?
We'd need to know more about your process to be able to offer a better approach.

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
azizrasul
post May 24 2019, 08:19 AM
Post#18



Posts: 1,458
Joined: 18-July 00
From: Faisalabad, Pakistan


I have done a redesign which no longer causes me an issue.

--------------------
Aziz
Go to the top of the page
 
theDBguy
post May 24 2019, 09:37 AM
Post#19


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
I have done a redesign which no longer causes me an issue.
That's probably what most people do, and that's why I don't see a whole lot of people asking this question and probably why we haven't see a fix for it, other than manually doing a C&R. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gemmathehusky
post May 24 2019, 01:24 PM
Post#20


UtterAccess VIP
Posts: 4,668
Joined: 5-June 07
From: UK


@cheekybuddha

I never realised that. I just tried adding and deleting a field for a cycle of 1000 reps, and it failed when 255 fields had been used.

Error 3190, "Too many fields defined."

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th June 2019 - 06:16 PM