Change Field Type for Linked Table FE/BE

[Monsora83]

Now I know you cant directly change the data-type for the BE table from the FE sections.

However is it possible to use the FE to open the BE DB, access the BE Table with the field type I want to change, and then change it? Even if I were to have some code in the BE that I would send data to or something.

Would something like this be possible?

OpenDatabase ("C:\AccessDatabase_be.mdb")

(WhateverThingToPointToBEDatabase).Execute "ALTER TABLE [" & strDatabase_Table_ID & "] ALTER [" & strField_ID & "] [" & strField_Type & "];"

 

[boblarson]

Not really because you would have to remove the table from the relationships, delete the table from the frontend and then do the work and then relink and then add back into the relationships.

So, it would be much better to just do it manually in the backend. Isn't this a RARE occurrence? It should be if it isn't. This should not have to be done over and over again.

 

[smd7m]

Please use this code

Dim db As Database
Dim tb As TableDef
Dim pat As String
pat = fGetLinkPath("table1")
Set db = OpenDatabase(pat)
Set tb = db.TableDefs("Table1")
With tb
.Fields.Append .CreateField("fild2", dbText, 50)
.Fields.Append .CreateField("fild1", dbBoolean)
End With
Set db = Nothing
Set tb = Nothing

*********************************************
Function fGetLinkPath(strTable As String) As String
Dim dbs As Database
Dim stPath As String
Set dbs = CurrentDb()
On Error Resume Next
stPath = dbs.TableDefs(strTable).Connect
If stPath = "" Then
fGetLinkPath = vbNullString
Else
fGetLinkPath = Right(stPath, Len(stPath) _
- (InStr(1, stPath, "DATABASE=") + 8))
End If

Set dbs = Nothing
End Function
Sub sListPath()
Dim loTd As TableDef
CurrentDb.TableDefs.Refresh
For Each loTd In CurrentDb.TableDefs
Debug.Print fGetLinkPath(loTd.name)
Next loTd

Set loTd = Nothing
End Sub

 

[boblarson]

Please use this code

smd7m - we don't just blindly answer when the reason for some request may be detrimental to the overall health of the OP's database. This is one of those cases. Until we had received some indication as to why this was wanted (and the possibility exists that there is a BETTER solution to this issue), one should not be so quick to just throw out code which will work, but again could be setting the poster up for failure down the road.

So, just wanted to let you know that this is one of those cases and please be a little more thorough in seeking that out and not just "Please use this code" etc.