Change a field size using a modual 1

[rickkillebrew]

Several times a month we receive data from an outside source. The origin of the data is from a FoxPro database. The database is exported to us in Access (our in-house program).

Sequence Number (SEQ_NUM) is a field in one of the tables (L1080ax) that we receive. SEQ_NUM is our principle record identifier (not a Primary Key). SEQ_NUM is Data Type = Text, with a Field Size = 5. The field size is too small. The source of the data resets the sequence number to 'Zero' every January 1. The field size must be 8 because I must add a 2-digit year identifier and a hyphen in front of the 5-digit sequence number to prevent duplicates in our database.

As we receive new data, I manually open the table in design view, modify the field size from 5 to 8, exit from design view, save the changes then run my update query to append the '03-' to the sequence number. If I am not available, our staff has to wait. I

I need to create a modual that can be activated by clicking a command button, that will modify the field size of SEQ_NUM from 5 to 8 then run my update query (qryUpdateQryL1080ax). My 'Access' and 'Intro to VB-6' books haven't been much help. Can you help?

DATABASE INFO:
db = 1080onCDrive
table = L1080ax
field = SEQ_NUM
data type = text
field size = 5 (change to 8)

 

[BillPower]

This should do what you want as long as the Table "L1080ax" is in the Current DB:

Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "ALTER TABLE L1080ax ALTER COLUMN SEQ_NUM TEXT(8);"
DoCmd.RunSQL (strSQL)
DoCmd.OpenQuery "qryUpdateQryL1080ax"
DoCmd.SetWarnings True


I would test it first on a Backup Copy of your DB to make sure it does what you want.

Regards

Bill

 

[Aqif]

Hi

If you have MS Access 97 than the above query will not work. You have to use following code

Sub AlterFieldType(TblName As String, FieldName As String, _
NewDataType As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()

' Create a dummy QueryDef object.
Set qdf = db.CreateQueryDef("", "Select * from PROD1&quot

' Add a temporary field to the table.
qdf.SQL = "ALTER TABLE [" & TblName & "] ADD COLUMN AlterTempField " & NewDataType
qdf.Execute

' Copy the data from old field into the new field.
qdf.SQL = "UPDATE DISTINCTROW [" & TblName & "] SET AlterTempField = [" & FieldName & "]"
qdf.Execute

' Delete the old field.
qdf.SQL = "ALTER TABLE [" & TblName & "] DROP COLUMN [" & FieldName & "]"
qdf.Execute

' Rename the temporary field to the old field's name.
db.TableDefs("[" & TblName & "]&quot.Fields("AlterTempField&quot.Name = FieldName
' Clean up.
End Sub


The usage of this function will be like

AlterFieldType "TableName","FieldName","DataType"

eg:

AlterFieldType "Path", "DXMX_PRIM", "Text(2)"



Cheers!
ÙÇãá