Problem med at oprette formateret datofelt via DAO

Jeg plejer at bruge denne:

Dim db47 As Database
Dim tdfNew As TableDef
Dim VARa As String
VARa = InputBox(Prompt:="Indtast navnet på den nye tabel:", Title:="Opret ny tabel.", Default:="")
Set db47 = OpenDatabase("D:\XP\db47")
Set tdfNew = db47.CreateTableDef(VARa)
With tdfNew
.Fields.Append .CreateField("tekst", dbText, 30)
.Fields.Append .CreateField("memo", dbMemo)
.Fields.Append .CreateField("dato", dbDate)
.Fields.Append .CreateField("tal", dbInteger)
.Fields.Append .CreateField("tal1", dbLong)
.Fields.Append .CreateField("tal2", dbDouble)
db47.TableDefs.Append tdfNew

This works.
NOTE: I have altered the name of the tableDef (tdfSygePleje) and the example is using the local database.

You have to create the property in the tableDef first, and then append it to the field.



Public Function createTblSP()
Dim db As DAO.Database
Dim tdfSygePleje As DAO.TableDef
Dim idx As DAO.Index
Dim fldSkemaNo As DAO.Field
Dim fldCpr As DAO.Field
Dim fldStartDato As DAO.Field
Dim fldHenvisDato As DAO.Field
Dim prop As DAO.Property


Set tdfSygePleje = CurrentDb.CreateTableDef("tblSygepleje")

Set fldSkemaNo = tdfSygePleje.CreateField("skemaNo", dbLong)
fldSkemaNo.Attributes = dbAutoIncrField

Set fldCpr = tdfSygePleje.CreateField("CPR", dbText, 15)
Set fldStartDato = tdfSygePleje.CreateField("startDato", dbDate)
Set fldHenvisDato = tdfSygePleje.CreateField("HenvisDato", dbDate)

With tdfSygePleje
    .Fields.Append fldSkemaNo
    .Fields.Append fldCpr
    .Fields.Append fldStartDato
    .Fields.Append fldHenvisDato

End With
CurrentDb.TableDefs.Append tdfSygePleje


Set db = CurrentDb
Set tdfSygePleje = db.TableDefs("tblSygepleje")
Set prop = tdfSygePleje.CreateProperty("Format", dbText, "Short Date")

Set fldStartDato = tdfSygePleje.Fields("startDato")
fldStartDato.Properties.Append prop


End Function

and it is possible that things can be done in another order

Hi Terry

I check your answer as soon as I have the time.
As far as I can see - the only thing changed is, that you create the property on the Table level, instead of doing it on the specific field??

Will this not influence all date fields in the table?

Christian

Hi Christian
No, the user property only gets appended to the fields you specify. Also, you may find that its necessary to append the table (CurrentDb.TableDefs.Append tdfSygePleje) before you create/append the property, although I havent verified this.

May I ask whi you need to do this in code? You can do it directly in the table properties, and if you dont have the Access frontend then you can always use the format function in your code.

setting the format property to "Short Date" has NO effect on how the date is stored in the database, it is always stored in teh same format!

Hi

We have a system that is split up in 2 separate databases, a frontend and a backend.
The system is currently running on 15 to 20 different hospitals, and the update has to be made in the backend database. So when the users get the new version, we have to create the new tables in the backend database, and then link them back into the frontend.

If you have another solution that you find better - pls. let me know.

Regards.

that sounds fine :o)

Nu blander jeg mig i noget, jeg ikke rigtig har forstand på, men det forekommer mig at have set en funktion der automatisk søger efter en ny version om importerer ændringer. Men måske det bare er sommervarmen :o)

thanks, hope you got it to work.