Change remote table structure

[Zorba]

I have inherited an Access 97 database that is being used in several
locations. It is not networked.
The database is split in two: data and code with the data tables being
linked to the code application. The code is distributed as an MDE file.
The code is universal whilst the data is only pertinent to the local
offices.

I have the task of changing the width of key field in one of the data
tables. Currently it is a text field 12 characters wide which has to be
increase to 20 characters.

Any suggestions on how I might go about this?

--
Cheers from your chum
Zorba Eisenhower
www.Zorbas.org

PS: I suggested that I go on a trip to Tokyo and San Francisco offices, then
back to the UK but, for some reason, no one would approve the budget.

 

[Kevin3NF]

If it were my db, I would create a new column (myfield_New) with the
appropriate properties (text, 20). Run an update query to update the value
of the new column with the existing value of the old column. Rename myfield
to myfield_Old. Rename Myfield_new to Myfield and test. After testing,
delete Myfield_Old.

Rinse, lather, repeat for each remote back-end.

This assumes you have access to the back-ends.

There maybe simpler ways, but this is what I do.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Zorba]

Can I do that using VBA?

 

[Kevin3NF]

No idea. never occured to me to try. I'm picky enough to do this stuff by
hand so I see errors immediately, and I can do it faster that way than I can
write VBA code. The update statement can be, but as far as manipulating the
table definitions in code, I wouldn't be the guy to advise you.

Anyone else want to jump in?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Zorba]

Yes, I'd like to do it by hand as well, but no one will authorise my trips
to Japan and San Francisco :-(

Thank you for your input

 

[Kevin3NF]

VNC, Terminal Server, Remote Desktop....all significantly cheaper than a
plane ticket....

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Bas Cost Budde]

Can I do that using VBA?

This could get you going:

Function changeFieldType(cTabel As String, cVeld As String, nType As
Long, Optional nSize = 50) As Boolean
'returns True on success
Dim db As Database, td As TableDef, fd As Field
Dim cFile As String
Dim cLocalTable As String
Dim nCol As Long
changeFieldType = True
set db = CurrentDb
cLocalTable = cTabel
Set td = db.TableDefs(cLocalTable)
'it is forbidden to directly change type. So we do:
'1 insert new field
Set fd = td.CreateField(cVeld & "1", nType, nSize)
td.Fields.Append fd
td.Fields.Refresh
'2 copy data (convert, you may hope)
db.Execute "update [" & td.Name & "] set [" & fd.Name & "]=" & cVeld
'3 remove old field but store its position
nCol = td.Fields(cVeld).OrdinalPosition
td.Fields.Delete cVeld
'4 rename new field
td.Fields(cVeld & "1").Name = cVeld
'5 put field on former position
td.Fields(cVeld).OrdinalPosition = nCol
On Error Resume Next
Set fd = Nothing
Set td = Nothing
db.Close
Set db = Nothing
end function

 

[Zorba]

Thank you


--
Cheers from your chum
Zorba Eisenhower
www.Zorbas.org

Can I do that using VBA?

This could get you going:

Function changeFieldType(cTabel As String, cVeld As String, nType As
Long, Optional nSize = 50) As Boolean
'returns True on success
Dim db As Database, td As TableDef, fd As Field
Dim cFile As String
Dim cLocalTable As String
Dim nCol As Long
changeFieldType = True
set db = CurrentDb
cLocalTable = cTabel
Set td = db.TableDefs(cLocalTable)
'it is forbidden to directly change type. So we do:
'1 insert new field
Set fd = td.CreateField(cVeld & "1", nType, nSize)
td.Fields.Append fd
td.Fields.Refresh
'2 copy data (convert, you may hope)
db.Execute "update [" & td.Name & "] set [" & fd.Name & "]=" & cVeld
'3 remove old field but store its position
nCol = td.Fields(cVeld).OrdinalPosition
td.Fields.Delete cVeld
'4 rename new field
td.Fields(cVeld & "1").Name = cVeld
'5 put field on former position
td.Fields(cVeld).OrdinalPosition = nCol
On Error Resume Next
Set fd = Nothing
Set td = Nothing
db.Close
Set db = Nothing
end function