Tilføje et felt til en tabel via VBA

Alter TABLE AAA ADD COLUMN ProductQuantity Single;

Docmd.RunSQL "Alter TABLE AAA ADD COLUMN ProductQuantity Single;"

terry,

How do I check that the Field ProductQuantity exits ?

If the Field ProductQuantity is a text field, how do I tell the length ?

http://office.microsoft.com/en-us/access/HP010322071033.aspx

"How do I check that the Field ProductQuantity exits ?"

Well I would just try and create the field and if it exists you get an error which you can catch and react on

terry,

I have read the document. I will try it.
My program exits in 97, 2000, XP and 2003.

When I have tested it I will return.

terry;

ALTER TABLE T_Projdata ADD COLUMN RealKurs Single;

When I write this line I get -
Compie Error:
Expected: End of Statement

where are you writing it?

Function Version302()
On Error GoTo Fejl_Version302
  ALTER TABLE T_Projdata ADD COLUMN RealKurs Single;
Exit Sub


Fejl_Version302:
'Her kommer du hvis feltet eksisterer i forvejen, dvs kunne ikke oprettes igen
Resume Next



End Function

Function Version302()
On Error GoTo Fejl_Version302
  Docmd.Runsql "ALTER TABLE T_Projdata ADD COLUMN RealKurs Single;"
Exit Sub


Fejl_Version302:
'Her kommer du hvis feltet eksisterer i forvejen, dvs kunne ikke oprettes igen
Resume Next



End Function

Now I get the Message:
Compile Error:
Exit Sub not allowed in Function or Property

The Access version is Access 97

I change the Function to Sub

Terry

It Don't works.

Faillure: Can't execute data definition statements on linked data source.

Do you now how to do on linked tables?

You will need to make a connectin to the dB, but one problem is going to be if the loaction of the backend changes. I'll let you figure that proble out yourself


This code isnt tested


Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.mdb;Persist Security Info=False"
Set rec = New ADODB.Recordset
cnn.Open

cnn.Execute "ALTER TABLE T_Projdata ADD COLUMN RealKurs Single;"

I have 2 backends and they can change.
So I have added a table with versionnumber to my Backend.
Then I can check for the version and make an update on the specific table and refrsh the link.

I use DAO, so I have found this code, which is working on linked tables.


Sub Tilføjfelt_Restmandetimer()
'Tilføjer feltet Restmandetimer til tabellen T_Kapacitet
On Error Resume Next
Dim WdsData As DAO.Database
Dim MyTabel As DAO.TableDef
Dim fld As DAO.Field
Set WdsData = CurrentDb()
Set MyTabel = WdsData.TableDefs!T_Kapacitet
Set fld = MyTabel.CreateField("Restmandetimer", dbSingle) 'Feltnavn = Restmandetimer, og egenskab = Single decimaltal
MyTabel.Fields.Append fld
WdsData.TableDefs.Refresh
If MyTabel.Updatable = True Then
  With MyTabel
      .Fields.Append .CreateField("Restmandetimer", dbSingle)
  End With
End If

End Sub

Terry

Do I owe you any points?

No, if you've found your own solution then you should give yourself them.

I'm actually surprised that your code works on linked tables if the code i previously gave doesnt. (DoCdm.RunSQL ...)

But if it does then great.

Have a good Christmas

OK, lukker