Add field to Access DB Table Help with vb6 code

Posted on 2005-05-09
Medium Priority
Last Modified: 2010-04-17
I need to add some fields to an Access DB in vb6. The fields have spaces in the names one looks like this:

Field Name: Plate Library
Field Size: Integer
Decimal Places: Auto
Default Value: 0
Required: No
Indexed: No

Here is what I have so far this works great for text fields as long as there is not a space in the name.
I need to solve that problem and also add the ability to set the other properties of the field.
Anyone have ideas on how to solve either of these problems?

Public Function CreateField(DatabaseName As String, _
   ByVal TableName As String, ByVal FieldName As String, ByVal FieldType As String) As Boolean

'DataBaseName is the file/path name of the database
'TableName is the name of the table
'FieldName is the name of the Field you want to create
'FieldSize is the size in characters of the Field you want to create
'Returns true if successful, false otherwise
If Dir(DatabaseName) = "" Then Exit Function
On Error GoTo errorhandler
Dim db As DAO.Database
Set db = Workspaces(0).OpenDatabase(DatabaseName, False, False, ";pwd=rabbit")
If Not TableExists(db, TableName) Then GoTo errorhandler
db.Execute "ALTER TABLE " & TableName & " ADD COLUMN " & FieldName & " " & FieldType
CreateField = True
Exit Function
If Not db Is Nothing Then db.Close
End Function
Question by:BadOscar
LVL 28

Expert Comment

ID: 13960634
Have you tried:

db.Execute "ALTER TABLE " & TableName & " ADD COLUMN " & Chr$(34) & FieldName & Chr$(34) &  " " & FieldType
LVL 28

Accepted Solution

vinnyd79 earned 500 total points
ID: 13960643
Or maybe:

db.Execute "ALTER TABLE " & TableName & " ADD COLUMN [" & Chr$(34) & FieldName & Chr$(34) &  "] " & FieldType
LVL 44

Assisted Solution

Arthur_Wood earned 500 total points
ID: 13961277
when you have embedded blanks in field names, or you are using field names that just happen to be Access RESERVED words, you can force Access to accept the 'offending' field name by enclosiing the name in [...], as suggected by vinnyd79's last post.  

LVL 24

Expert Comment

ID: 13962341

Author Comment

ID: 14188558
you two were helpfull, I was just looking for a little more, but thanks

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

807 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