Add field to Access DB Table Help with vb6 code

Posted on 2005-05-09
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

    Have you tried:

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

    Accepted Solution

    Or maybe:

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

    Assisted Solution

    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

    LVL 2

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    A short article about problems I had with the new location API and permissions in Marshmallow
    Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    759 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

    13 Experts available now in Live!

    Get 1:1 Help Now