Solved

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

Posted on 2012-03-13
41
734 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
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 46

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

772 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