Solved

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

Posted on 2012-03-13
41
713 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
  • 17
  • 11
  • 3
  • +6
41 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
Comment Utility
To removed from the table entirely, yes!

Thank you!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
So the column Name is:  George B. Washington
?
0
 

Author Comment

by:SDfatha
Comment Utility
Row: Customer
Column Name: George B. Washington
0
 

Author Comment

by:SDfatha
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I'm not convinced that you really understand what you are asking for.
0
 

Author Comment

by:SDfatha
Comment Utility
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
Comment Utility
peter57r, look above.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
Comment Utility
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
Comment Utility
<No Points wanted for my post>
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes it's the field ... i am trying to see if i can get it work
0
 

Author Comment

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

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I forgot to add the refence microsoft dao 3.6 library.

Now i get error,

Item not found in this collection.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:SDfatha
Comment Utility
I have attached an image please have a look
error
0
 

Author Comment

by:SDfatha
Comment Utility
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
Comment Utility
I just need to delete the customer, nothing else!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
I get error,
too few parameters, expected 1.
0
 

Author Comment

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

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What to double check?
Look at my screenshot, the names are the same.

Please someone help me, thank you!
0
 
LVL 84
Comment Utility
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
Comment Utility
Maybe the Customer field is set to not accept NULL's?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
..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 45

Expert Comment

by:Martin Liss
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now