Solved

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

Posted on 2012-03-13
41
753 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 84
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 84
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 47

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

752 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