Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    remote table field (Access 2000)

    Create remote table field with properties

    I have a function to create a new field but i do not know how to add the properties of this field which are:
    data type Number
    Field Size Single
    Format Percent
    Decimal places 0
    Default Value 0


    My function is the follwoing:

    Public Function FncCreateNewField()
    Dim wsp As DAO.Workspace
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Set wsp = DAO.DBEngine.Workspaces(0)
    Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb")
    Set tdf = dbs.TableDefs("Products")
    Set fld = tdf.CreateField("Fees", dbNumber)
    tdf.Fields.Append fld
    tdf.Fields.Refresh
    dbs.Close
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Set wsp = Nothing
    End Function

    Can somebody help me with expanding my code with the following properties:
    data type Number
    Field Size Single
    Format Percent
    Decimal places 0
    Default Value 0
    All the above properties refer to the field Fees.I do not know how and where to put them on.

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: remote table field (Access 2000)

    It looks like you are making some significat changes to a BE db.

    You may want to have a look at something called the BackEnd Upgrader.

    BEU gives you, the developer, the means to create a database of BE changes, testing them in your development environment then shipping them with your code so that they get applied in the live environment without any manual intervention.

    It allows adding, deleting and modifying of tables, fields, relationships, indexes and you can run SQL statements.

    You can find more info about it at http://www.databaseadvisors.com/downloads/...EndUpgrader.doc and it can be downloaded from [urlhttp://www.databaseadvisors.com/downloads/index.htm[/url]

    If you do use it, the developers (of which I am one) would LOVE feedback on it.

    If you don't want to go that route then

    change the following line:

    <pre>Set fld = tdf.CreateField("Fees", dbNumber)
    To
    Set fld = tdf.CreateField("Fees", dbSingle) 'Single Type</pre>


    to make the field a Single Type Number

    After you append the field to the tdf, you can do the rest:

    you will need to do the following to set the Format, Decimal places and Default Value. They are all properties of the field

    <pre> Set prp = fld.CreateProperty("Format")
    prp.Type = dbText
    prp.Value = strFldFormat
    fld.Properties.Append prp
    </pre>


    "DecimalPlace" and "DefaultValue" are the other two properties you need to create.

    I really think you should really investigate the BEU though. All the hard work has been done.

    All you will need to do is fill in a form.

    HTH
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: remote table field (Access 2000)

    Dear Sir

    Thank you very much for your kind and wonderful reply.I have 2 question.First could you help me further? I have stalled on the follwoig problem.I have to

    define the field
    Discount, which i can, then i have to define the property Format, and then i must set the Format to percent.I cannot do it.My command prp.Type = db.percent
    seems to be false and Acces doesnt accept it.Could you tell me where i am wrong?
    Secondly somehow i could set the BEU into motion, but the BEU seems very interesting and useful to me..Is it possible to send to you just one table called "order details" to see how it works?

    here is mine not working function:

    Public Function CreateTheFieldDiscount()
    Dim wsp As DAO.Workspace
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Set wsp = DAO.DBEngine.Workspaces(0)
    Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb")
    Set tdf = dbs.TableDefs("order details")
    Set fld = tdf.CreateField("order details", dbSingle) 'Single Type
    Set prp = fld.CreateProperty("Format")
    prp.Type = dbpercent
    tdf.Fields.Append fld
    tdf.Fields.Refresh
    dbs.Close
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Set wsp = Nothing
    End Function

    Best regards and i wish you a nice day

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: remote table field (Access 2000)

    <hr>must set the Format to percent.I cannot do it.My command prp.Type = db.percent<hr>
    Well The property Type needs to be set to dbText

    prp.Type = dbText
    prp.Value = "Percent" 'This sets the format property to Percent

    <hr>Secondly somehow i could set the BEU into motion, but the BEU seems very interesting and useful to me..Is it possible to send to you just one table called "order details" to see how it works?<hr>
    If you want to use the BEU, then it will have to be done at YOUR location. So you are better off by downloading it yourself from http://www.databaseadvisors.com/downloads/index.htm

    It comes with a full pdf instruction manual. The developers will help you in any way that we can (Yes I am one of the developers of the BEU)

    The best part about it IMHO, is it's free!!

    I hope this doesn't sound like a brush off, it's not. It's just that you are better off to do it yourself in this case.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: remote table field (Access 2000)

    Thank you very much for your reply.I have followed your advice , the code works this time,it creates the field discount,but somehow it fails to create
    the property percent.May be something with the appendment is missing?Please forgive my dullness
    I will repeat my function:

    Public Function CreateField()
    Dim wsp As DAO.Workspace
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Set wsp = DAO.DBEngine.Workspaces(0)
    Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb")
    Set tdf = dbs.TableDefs("order details")
    Set fld = tdf.CreateField("discount", dbSingle)
    Set prp = fld.CreateProperty("Format")
    prp.Type = dbText
    prp.Value = "Percent" 'This sets the format property to Percent
    tdf.Fields.Append fld
    tdf.Fields.Refresh
    dbs.Close
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    Set wsp = Nothing
    End Function

  6. #6
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: remote table field (Access 2000)

    <hr>
    <pre>Set prp = fld.CreateProperty("Format")
    prp.Type = dbText
    prp.Value = "Percent" 'This sets the format property to Percent
    tdf.Fields.Append fld
    </pre>

    <hr>
    It looks like I may have missed telling you an important point. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> You need to append the field to the TableDef BEFORE you can create any Field Properties. Once you have created the property, you need to append the property to the field.

    So your code should be more like this:
    <pre>Set prp = fld.CreateProperty("Format")
    prp.Type = dbText
    tdf.Fields.Append fld
    prp.Value = "Percent" 'This sets the format property to Percent
    fld.Properties.Append prp</pre>

    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •