Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CreateTable (XP)

    Hi,

    I am trying to create a Table in VBA. I just want a Table called 'IP' with 1 field called 'IP' to be a list of integers from 1 to 255.

    I have done this code

    Sub temp()
    Dim t As DAO.TableDef, i As Integer, f As DAO.Field, rs As DAO.Recordset
    Set t = DBEngine(0)(0).CreateTableDef("IP")
    Set f = t.CreateField("IP", dbInteger)
    t.Fields.Append f
    Set rs = t.OpenRecordset("IP")
    rs.MoveFirst
    For i = 1 To 255
    rs.AddNew
    rs.Fields(0).Value = i
    rs.Update
    Next i
    Set t = Nothing
    Set f = Nothing
    Set rs = Nothing
    End Sub


    but the openrecordset command results in an error. I must be missing something obvious.

    I have a query which lists all current static IP addresses on lour subnet, and I want to be able to flag up those that are usused (not there) so I thought if I had a table with ALL IP ranges, then I could do a query to flag up those addresses which were not allocated yet.
    Thanks,

    pmatz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CreateTable (XP)

    You must append the table to the TableDefs collection, just as you append the field to the Fields collection.
    You must open the recordset on the current database, not on t itself - t.OpenRecordSet("IP") doesn't make sense.
    You shouldn't use rs.MoveFirst since the recordset is empty when you start, so there is no first record to move to.
    Here is a modified version:

    Sub temp()
    Dim t As DAO.TableDef, i As Integer, f As DAO.Field, rs As DAO.Recordset
    Set t = CurrentDb.CreateTableDef("IP")
    Set f = t.CreateField("IP", dbInteger)
    t.Fields.Append f
    CurrentDb.TableDefs.Append t
    Set rs = CurrentDb.OpenRecordset("IP")
    For i = 1 To 255
    rs.AddNew
    rs.Fields(0).Value = i
    rs.Update
    Next i
    Set t = Nothing
    Set f = Nothing
    Set rs = Nothing
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CreateTable (XP)

    Thanks Hans. The Append TableDefs was really what I was missing - apart from the other blatant errors!!

    Cool, I now know how to create and manipulate tables through code [img]/forums/images/smilies/smile.gif[/img]

    Now I've got my table of IP's, and another table with some IP's in it - how do I set up a query to compare the two and list only those IP's that are not in table2? I can list all those IN table2, but having probs inverting that!
    Thanks,

    pmatz

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CreateTable (XP)

    How are the real IPs stored in the table? As a full IP such as 123.124.125.126, or just the last part 126, or...?

  5. #5
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CreateTable (XP)

    I have the IP's stored as w.x.y.z, and the IP table (with all IP's) with a field w.x.y.Z also.

    I have a query

    SELECT DISTINCT qry_IP.IPaddress
    FROM qry_IP, qry_StaticIP
    WHERE (((qry_IP.IPaddress) NOT Like [qry_StaticIP]![IP Address]));

    which gives me all IP's that are in both tables - but when I put NOT in then I get mad results!
    Thanks,

    pmatz

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CreateTable (XP)

    Try

    SELECT IPaddress
    FROM qry_IP
    WHERE IPaddress Not In (SELECT [IP Address] FROM qry_StaticIP)

  7. #7
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CreateTable (XP)

    Thanks Hans - that works.

    Whats going on in there then - I have not used IN before I dont think...
    Thanks,

    pmatz

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CreateTable (XP)

    IN is a way to use a subquery as selection criteria. The WHERE part

    WHERE Field in (SELECT ...)

    will return all records for which the value of Field occurs among the values returned by the subquery.

    WHERE Field Not In (SELECT ...)

    will return all records for which the value of Field does NOT occur among the values returned by the subquery. (The subquery must select a single field, obviously)

    The same can be accomplished with a query joining two tables/queries, but then the result is often not updatable, while the query with In or Not In will be updatable.

    Warning: Not In is notoriously slow!

  9. #9
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CreateTable (XP)

    Got it! Thanks again.

    Just to pick your brain/s [img]/forums/images/smilies/wink.gif[/img]

    I have a list of users in a table, with yesNo boxes for each drive mapped letter. I am using this to manage and create batch files quickly.
    Currently I have another table with a list of Drive Letters and their respective Drive Paths to be mapped.

    I can't see a way of making a query that will bring up each user with their resepctive drive letters, and also bring up the drive path for that letter from the other table...

    although I am thinking - UNION query... Hmmmmm
    Thanks,

    pmatz

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CreateTable (XP)

    Do you have a separate field for each drive letter, or is each user/drive letter combination a separate record?

  11. #11
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CreateTable (XP)

    Nope - thinking on the wrong lines there. I can't see how I could do it with a joined / linked table. Maybe have to use VBA.
    Thanks,

    pmatz

  12. #12
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CreateTable (XP)

    Separate field for each drive letter.

    eg in a record i have

    username,T:,G:,J:,W:

    the drive fields are YesNo.
    Then I have a table with :

    drive letter, Drive path
    Thanks,

    pmatz

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CreateTable (XP)

    It would be more convenient to have a table with a separate record for each username/drive letter combination:

    <table border=1><td>Username</td><td>Driveletter</td><td>matz</td><td>F</td><td>matz</td><td>J</td><td>matz</td><td>S</td><td>hans</td><td>J</td><td>hans</td><td>K</td></table>
    You could then link the tables on the drive letter field.

  14. #14
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CreateTable (XP)

    Cool - will try this 2morrow - i think it IS the right way. For some reason couldn't get my head aroun it earlier [img]/forums/images/smilies/sad.gif[/img]

    Thanks Hans.

    Matz
    Thanks,

    pmatz

Posting Permissions

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