INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Changing a table field's property

Changing a table field's property

(OP)
In a Microsoft Access (2000 format) database, I have a table that contains a field that currently has the following characteristics:

Data Type: Number
Field Properties: Field Size = Decimal, Precision = 2


Is there a way in VBA to change the precision from 2 to 9?

Thanks!

RE: Changing a table field's property

You can try this, or you can read some information about it here

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Changing a table field's property

(OP)
Andy:

What if the table name/column name has spaces in it? How would the ALTER statement look in that scenario?

Thanks!

RE: Changing a table field's property

First, I would avoid spaces in table of field names. Just because Access allows you to do so, I would not do that. Some other data bases (like Oracle) does not allow that. I use underscore instead of a space (for example: First_Name)

Anyway, to use the name with the spaces, use [], like [First Name]

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Changing a table field's property

(OP)
It didn't like it. I received a '3293' run-time error.

I am using the following references:
  • Microsoft Access 16.0 Object Library
  • Microsoft Office 16.0 Access database engine Object Library
  • Microsoft Visual Basic for Application Extensibility 5.3
Thanks!

RE: Changing a table field's property

Could you share your code where you use ALTER statement?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Changing a table field's property

(OP)
Here is the code:

Public Sub Change_Field_Precision()

Dim dbsApplications As Database
Dim strSQL As String

Set dbsApplications = CurrentDb

strSQL = "ALTER TABLE [Applications Now] ALTER COLUMN [ADM_APPL_NBR] DECIMAL(9)"
dbsApplications.Execute (strSQL)
dbsApplications.Close

End Sub


Thanks!

RE: Changing a table field's property

Use ADO instead (add reference) and modify your code:

CODE -->

'dbsApplications.Execute strSQL
CurrentProject.Connection.Execute strSQL 

combo

RE: Changing a table field's property

Complete code

CODE --> vba

Public Sub Change_Field_Precision()
    Dim adoConn As ADODB.Connection
    Set adoConn = CurrentProject.Connection
    Dim strSQL As String
    strSQL = "ALTER TABLE [Applications Now] ALTER COLUMN [ADM_APPL_NBR] DECIMAL(9)"
    adoConn.Execute strSQL
    Set adoConn = Nothing
End Sub 

Duane
Hook'D on Access
MS Access MVP

RE: Changing a table field's property

(OP)
Thanks, dhookom and combo. That did the trick. Adding the Microsoft ActiveX to my reference list solved the problem.

Thanks, everyone, for your help on this.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close