update the design of the table

gkovac

New Member
Joined
Sep 4, 2007
Messages
5
Hi all

this is my first official post here, but i've been following this board for some time and it has really proven helpful).

My first question here goes: Is it possible to somehow "re-set" the design of a table? Let say i have a table:
- ID text 50
- name text 200
- price integer

and i'd like to change the darn thing to
- ID text 17
- name text 23
- price single

If this is possible please let me know how. Thanks a lot for the help!

Grega
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Sure. Just go into the Design View of the table and edit the field types & lengths to what you want. Note that it will give you a warning saying that some data may be lost (for example, if you had a ID entry that was 20 characters long, reducing the size from 50 to 17 would cut off the last three characters). If you are confident, click OK to proceed.
 

gkovac

New Member
Joined
Sep 4, 2007
Messages
5
uh... i think i should really be more clear on my wishes. I know that Design View fixing, but i'd like to "automate" the process somehow.

I have a million of tables which i have to manually fix and i'm really tired of it. So i wander... is there a way to reset the table automatically. I know that the data may be lost but that is not the worry here.

thanks,G

P.S.
jm14.. thanks on taking the time to answer this (what it looked like) n00b question.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Well, we went from "a table" to a "a million tables" between your two posts. That certainly does change the complexion of the problem (drastically, to say the least).

Let me start by saying that I have never used VBA to update field properties in a table like this, but I am pretty sure it is possible. However, you are still lacking some very important details that will be needed for a solution.

- Are all your tables set-up exactly the same, with the same field names?

If so, then it will be much easier to write VBA to do what you want. But then that begs the question why aren't all these tables combined into one table?

If not, how do you propose to automate the process? What I mean is how are you going to tell the VBA code to decide how long each field should be? If it is a manual process where you need to specify every field name and its corresponding length, I don't think writing code to do this will save you any time over opening the tables and doing it manually.
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796

ADVERTISEMENT

not sure what you mean by reset, but hopefully this will help you...
Code:
ALTER TABLE TableName ALTER COLUMN FieldName Text(17)
This will change your field size to 17

hth,
Giacomo

EDIT: didn't mean to jump in after JM's post... board is kinda slow today :oops:
 

gkovac

New Member
Joined
Sep 4, 2007
Messages
5
@jm14 and giacomo - guys thanks :LOL:
ALTER TABLE and ALTER COLUMN work perfectly!!

this is what i need with one "small" detail: Can i change several columns at once?

The "problem" i have is that i receive a lot of tables and i have to set them into specific form. So far i have been doing this manually but i figured that there has to be a faster way (not only faster but also more precise... after all i can still make a mistake while changing the design).

My tables are not all of the same design, but i will bypass this obstacle by creating several ALTER statements.

i have tried to upgrade this code but i was unsuccessful.

alter table table1
alter column field1 Text(17), field2 Text(99);

thanks a lot for your help!!

G
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796

ADVERTISEMENT

I don't believe you can modify several fields at once with Access SQL - or at least none of my attempts worked... I'd probably use some sort of vb/sql combination like this...

Code:
Sub TestAlterTable()
Dim strTable As String
Dim arrFields(2, 2) As String

strTable = "MyTable"
arrFields(1, 1) = "Field1"
arrFields(1, 2) = "text(10)"
arrFields(2, 1) = "Field2"
arrFields(2, 2) = "text(1)"

Call alterTable(strTable, arrFields)

End Sub

Code:
Sub alterTable(tableName As String, fieldList() As String)
Dim x As Integer

For x = 1 To UBound(fieldList)
DoCmd.RunSQL "alter table [" & tableName & "] alter column [" & fieldList(x, 1) & "] " & fieldList(x, 2)
Next x

End Sub

hth,
Giacomo
 

gkovac

New Member
Joined
Sep 4, 2007
Messages
5
@giacomo

thanks mate! this works like a charm!

i only have one question on this topic. when i set the field in the code as Integer the field gets set as Long Integer. Is there any chance to set it exactly as Integer?

thanks again for the help.

G
 

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
I can confirm that ALTER TABLE and ALTER COLUMN do not and likely never will allow you to modify multiple items simultaneously. Consider it a language standard, of sorts for all SQL. I suppose it's possible somebody might build a new SQL engine that could handle it one day.

My personal preference when doing stuff like this is to build new tables and copy the older data into it. At the time I opted for this design pattern, there were some specific reasons including things like how starting over with a fresh table sometimes avoids other inconvenient problems (nothing critical, just inconvenient to my needs)

Restating it - what I'd do is this given a fixed table layout (predictable):

Create this new table.
Create a SQL statement that appends all the data in the old into the new
Delete the old table
Rename the new table to the old name

Code:
Sub buildAtable(Optional ByVal tblName As String, _
               Optional ByRef rs As DAO.Recordset)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strTbl As String
Dim x, lngFld As Long

On Error GoTo HandleErr
Set dbs = CurrentDb()

If Len(tblName) > 0 Then
  strTbl = tblName
Else
  strTbl = "tblRSExport"
End If

If ObjectExists("Table", tblName) Then DoCmd.DeleteObject acTable, tblName

Set tdf = dbs.CreateTableDef(strTbl)  'Never put brackets here

'Set fld = tdf.CreateField("indexFLD", dbLong)
'fld.Attributes = fld.Attributes Or dbAutoIncrField  ' autonumber option
'tdf.Fields.Append fld 'adds it to the table

lngFld = rs.Fields.Count - 1

For x = 0 To lngFld
  Select Case rs.Fields(x).Name
    Case "dteDate"
      tdf.Fields.Append tdf.CreateField(rs.Fields(x).Name, 8) 'date
    Case Else:
      tdf.Fields.Append tdf.CreateField(rs.Fields(x).Name, 4) 'integer
  End Select
Next x
tdf.Fields.Append tdf.CreateField("unzoned", 4)

dbs.TableDefs.Append tdf        ' Create the Table after purged

Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
ExitHere:
    Exit Sub

' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at 09-16-2005 15:38:04   'ErrorHandler:$$D=09-16-2005    'ErrorHandler:$$T=15:38:04
HandleErr:
    Select Case Err.Number
        Case 3265:   ' Fields do not exist
        Case Else
            'MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "modImport.RebuildTable"  'ErrorHandler:$$N=modImport.RebuildTable
    End Select
' End Error handling block.
End Sub

Offhand, all of the field types are stashed over within the built-in access help - I forget the precise term - but read up under recordsets and/or tabledefs and you should find them.

Mike
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,948
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top