How can i delete a column of an .MDB file using Visual Basic 6

How can i delete a column of a Microsoft Accesss Database file using Visual Basic 6 code?

Table name: Customer
Column text: George B. Washington

Thank you so much!
SDfathaAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
That is what I was trying to clarify...

You stated:

Row: Customer
Column Name: George B. Washington

Also your Q was:  How can i delete a column

Now what is the *Column Name*?


In any event the same code syntax applies:

Dim db As dao.Database
dim tdf as dao.tabledef

Set db = Workspaces(0).OpenDatabase("C:\YourFolder\YourDatabase..mdb")
db.Execute "UPDATE Customer SET [YourColumnName] ='' WHERE [YourColumnName]='George B. Washington'",dbfailonerror

(Note that the  ''  before the WHERE clause is actually two single quotes, not a double quote)
0
 
peter57rCommented:
It's not clear what you want to do..

You say you want to delete a column - so you want that column to be removed from the table entirely - so that it just doesn't exist any more?

(As distinct from just clearing the value in that field from one record?)
0
 
SDfathaAuthor Commented:
To removed from the table entirely, yes!

Thank you!
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Jeffrey CoachmanMIS LiasonCommented:
So the column Name is:  George B. Washington
?
0
 
SDfathaAuthor Commented:
Row: Customer
Column Name: George B. Washington
0
 
SDfathaAuthor Commented:
Something like..

If found word George B. Washington in the Customer Tab then delete that column.
0
 
peter57rConnect With a Mentor Commented:
How are you connecting to the database?

If it's DAO then you can do your version of ...

dim tdf as dao.tabledef
set tdf = yourdatabaseobject.tabledefs("tablename")
tdf.Fields.Delete "yourfieldname"

I suggest you take a backup of the database before trying anything.
0
 
bapefConnect With a Mentor Commented:
Conn.Execute "DELETE FROM <tablename> WHERE FIELD1 = '" & cmdField1.Text & "'" will work. After using the Connection.Execute command refresh the Recordset using the ReQuery option.
0
 
peter57rCommented:
I'm not convinced that you really understand what you are asking for.
0
 
SDfathaAuthor Commented:
Conn.Execute "DELETE FROM <tablename> WHERE FIELD1 = '" & cmdField1.Text & "'" will work.

Yes, it should work but how do i connect to the database file?

I am new to microsoft access in visual basic.

Thanks
0
 
SDfathaAuthor Commented:
peter57r, look above.
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
I'm with Pete...

You would use code like this to connect to the DB then delete the field

Dim db As dao.Database
dim tdf as dao.tabledef

Set db = Workspaces(0).OpenDatabase("C:\YourFolder\YourDatabase..mdb")
set tdf = db.tabledefs("Customer")
tdf.Fields.Delete "George B. Washington"
0
 
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted for my post>
0
 
Helen FeddemaCommented:
It is extremely unlikely that a field in an Access table would be named "George B. Washington".  Most likely that is a value in a field rather than the field name.  What is the field name?
0
 
Helen FeddemaCommented:
Are you sure we are discussing Access tables here?  Rows and columns pertain to Excel workbooks; Access tables have fields.
0
 
Jeffrey CoachmanMIS LiasonCommented:
LOL.

Helen, That is what I asked as well.
The OP confirmed, that it is a Field (37715963)

It sounds like an "un-normalized" structure to me as well, but that might be the next question...

;-)

Jeff
0
 
SDfathaAuthor Commented:
Yes it's the field ... i am trying to see if i can get it work
0
 
SDfathaAuthor Commented:
Sorry, i meant 'VALUE' not the field. lol sorry guys
0
 
SDfathaAuthor Commented:
Why i get error message "User-defined type not defined" on ....
Dim db As dao.Database

Thanks both of you guys!
0
 
SDfathaAuthor Commented:
I forgot to add the refence microsoft dao 3.6 library.

Now i get error,

Item not found in this collection.
0
 
SDfathaAuthor Commented:
I have attached an image please have a look
error
0
 
SDfathaAuthor Commented:
Oh i see that code is trying to delete the column it self ... i want to delete a value within the column.

Please advice!
0
 
SDfathaAuthor Commented:
I just need to delete the customer, nothing else!
0
 
Jeffrey CoachmanMIS LiasonCommented:
No, the code is trying to delete the value...
I would be clearer if you did not give the column that same name as the table...

This code is fully tested:

Dim db As dao.Database
Dim strSQL As String
strSQL = "UPDATE Customer SET [Customer] ='' WHERE [Customer]='George B. Washington'"

Set db = Workspaces(0).OpenDatabase("C:\YourFolder\YourDatabase.mdb")
db.Execute strSQL, dbFailOnError


Obviously you have to substitute your path and db.
0
 
SDfathaAuthor Commented:
I get error,
too few parameters, expected 1.
0
 
SDfathaAuthor Commented:
Here on this line i get the error,
db.Execute strSQL, dbFailOnError
0
 
Jeffrey CoachmanMIS LiasonCommented:
Post the *entire* code you are using please...

Again, check your spelling carefully...

You will get this error if you spell the name of the field incorrectly...
0
 
SDfathaAuthor Commented:
Private Sub Command7_Click()
Dim db As DAO.Database
Dim strSQL As String
strSQL = "UPDATE Customer SET [Customer] ='' WHERE [Customer]='George B. Washington'"

Set db = Workspaces(0).OpenDatabase("C:\Users\domo\Desktop\soft\database\database3.mdb")
db.Execute strSQL, dbFailOnError
End Sub

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
Double-check the exact name of the table, and double-check the exact name of the field.(make sure that you look at the name of the field in Design view of the table, as there may be a Caption set for this field.)
0
 
SDfathaAuthor Commented:
What to double check?
Look at my screenshot, the names are the same.

Please someone help me, thank you!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Does your Customer table allow you to insert empty values? In some cases, you cannot do this depending on the values/constraints set at the table level.

Otherwise, you might consider fully qualifying your UPDATE statement:

UPDATE Customer SET Customer.Customer='' WHERE Customer.Customer='George B. Washington'
0
 
VBClassicGuyCommented:
Maybe the Customer field is set to not accept NULL's?
0
 
Jeffrey CoachmanMIS LiasonCommented:
The error was:
"too few parameters, expected 1."

I noted that this could be caused (among other things) by the field name being spelled incorrectly...

I am a bit stuck on this one two...

Once the Column/Field value issue was resolved, all of us posted fairly basic solutions that should have all worked...?
0
 
MWaalCommented:
It seems the question is not asking what is needed. I have the impression the word 'Table' is used where a record should be used, The Column already appeared to be a value.

So:
A database consist of mutible tables (compare Excel having sheets),
A table has Fields (compare Excelsheets having colums, where the first row defines the Column content),
A table is filled with record (compare Excel with rows containing values related to the top row).

Now would the question be: can I delete a customer from this table (Excel: delete a row)?
Yes, using SQL:
DELETE FROM {Tablename} WHERE [CustomerName] = 'George B. Washington'.
Remember to change {Tablename} to the Name of the table, and CustomerName to the Field-name (probably Customer).
0
 
Jeffrey CoachmanMIS LiasonCommented:
SDfatha

<I just need to delete the customer, nothing else!>

Again, let's be clear...
Do you want to delete the entire *Record* for George Washington, (The Customer) or just delete the Name (The Customer Name)?

I also think LSM is more correct using this syntax: [Customer].[Customer]


If your goal here is to delete the entire record, then use:

Dim db As dao.Database
Dim strSQL As String
strSQL = "DELETE * FROM Customer WHERE [Customer].[Customer]='George B. Washington'"

Set db = Workspaces(0).OpenDatabase("C:\YourFolder\YourDatabase.mdb")
db.Execute strSQL, dbFailOnErro
0
 
SDfathaAuthor Commented:
Now is working but i don't want to delete only the record .. also and the hole line (ALL THE RAW FOR THAT CUSTOMER)

Thank you!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Did you try the comment from boag2000 above, with the DELETE * statement? That will remove the entire row.

If you did and that did not work, then let us know what happened - did you get an error, for example?
0
 
MWaalCommented:
SDfatha, it would be good for you to have a look to SQL syntax.
A link would be: http://www.firstsql.com/tutor.htm
Here, you quickly see a link to the DELETE statement.
0
 
Jeffrey CoachmanMIS LiasonCommented:
..At this point I don't care who gets the points....

Every possible interpretation of the Q has been answered correctly...

take your pick...

Jeff
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.