Changing a table field's property 2

[KeyserSoze]

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!

 

[Andrzejek]

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.

 

[KeyserSoze]

Andy:

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

Thanks!

 

[Andrzejek]

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.

 

[KeyserSoze]

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

I am using the following references:
[ul]
[li]Microsoft Access 16.0 Object Library[/li]
[li]Microsoft Office 16.0 Access database engine Object Library[/li]
[li]Microsoft Visual Basic for Application Extensibility 5.3[/li]

[/ul]

Thanks!

 

[Andrzejek]

Could you share your code where you use ALTER statement?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[KeyserSoze]

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!

 

[dhookom]

I have always had issues with data definition queries with Decimal types. Check out Allen Browne's page regarding this issue.

Duane
Hook'D on Access
MS Access MVP

 

[combo]

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

[COLOR=#4E9A06]'dbsApplications.Execute strSQL[/color]
CurrentProject.Connection.Execute strSQL

combo

 

[dhookom]

Complete code

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

 

[KeyserSoze]

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.