Solved

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

Posted on 2012-03-13
41
759 Views
Last Modified: 2012-04-15
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!
0
Comment
Question by:SDfatha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 11
  • 3
  • +6
41 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37715931
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
 

Author Comment

by:SDfatha
ID: 37715935
To removed from the table entirely, yes!

Thank you!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37715937
So the column Name is:  George B. Washington
?
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:SDfatha
ID: 37715963
Row: Customer
Column Name: George B. Washington
0
 

Author Comment

by:SDfatha
ID: 37715969
Something like..

If found word George B. Washington in the Customer Tab then delete that column.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 125 total points
ID: 37715973
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
 
LVL 1

Assisted Solution

by:bapef
bapef earned 125 total points
ID: 37715977
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
 
LVL 77

Expert Comment

by:peter57r
ID: 37715999
I'm not convinced that you really understand what you are asking for.
0
 

Author Comment

by:SDfatha
ID: 37716104
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
 

Author Comment

by:SDfatha
ID: 37716107
peter57r, look above.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 37716182
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37716184
<No Points wanted for my post>
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 37716610
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 37716632
Are you sure we are discussing Access tables here?  Rows and columns pertain to Excel workbooks; Access tables have fields.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37716911
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
 

Author Comment

by:SDfatha
ID: 37717138
Yes it's the field ... i am trying to see if i can get it work
0
 

Author Comment

by:SDfatha
ID: 37717143
Sorry, i meant 'VALUE' not the field. lol sorry guys
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 37717340
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
 

Author Comment

by:SDfatha
ID: 37717519
Why i get error message "User-defined type not defined" on ....
Dim db As dao.Database

Thanks both of you guys!
0
 

Author Comment

by:SDfatha
ID: 37717565
I forgot to add the refence microsoft dao 3.6 library.

Now i get error,

Item not found in this collection.
0
 

Author Comment

by:SDfatha
ID: 37717582
I have attached an image please have a look
error
0
 

Author Comment

by:SDfatha
ID: 37717636
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
 

Author Comment

by:SDfatha
ID: 37717693
I just need to delete the customer, nothing else!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37717729
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
 

Author Comment

by:SDfatha
ID: 37717761
I get error,
too few parameters, expected 1.
0
 

Author Comment

by:SDfatha
ID: 37717768
Here on this line i get the error,
db.Execute strSQL, dbFailOnError
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37717900
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
 

Author Comment

by:SDfatha
ID: 37717966
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37718083
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
 

Author Comment

by:SDfatha
ID: 37718534
What to double check?
Look at my screenshot, the names are the same.

Please someone help me, thank you!
0
 
LVL 85
ID: 37719673
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
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 37719729
Maybe the Customer field is set to not accept NULL's?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37719795
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
 
LVL 1

Expert Comment

by:MWaal
ID: 37720478
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37720540
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
 

Author Comment

by:SDfatha
ID: 37732443
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
 
LVL 85
ID: 37732694
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
 
LVL 1

Expert Comment

by:MWaal
ID: 37734917
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37740543
..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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 37848037
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question