Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2012
    Posts
    3

    Unanswered: Length of a field in a table in Access

    I would like to use Excel VBA to find this data.

    I need to find the length of a field in a table in Access.

    It is important that I setup a Recordset, "rs.Open" to be able to find the data and I think you need a "rs.MoveLast" and a "rs.RecordCount" but I just can not put it all together.

    This is what I have thus far:

    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    conn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12 .0;Data Source=c:\Users\Philosophaie\Documents\a High Priority\a SQL\a Chase.accdb;"


    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by Philosophaie View Post
    I would like to use Excel VBA to find this data.

    I need to find the length of a field in a table in Access.

    It is important that I setup a Recordset, "rs.Open" to be able to find the data and I think you need a "rs.MoveLast" and a "rs.RecordCount" but I just can not put it all together.
    so, the first question is why is it so important to you that you setup a recordset?

    If you want to know the size of the field simply open the table in design view and look

    If you want to know the size of the values in the field, then create a new query off that table and use something like as one of the query fields:

    fieldLength: Len([MyFieldName])

    where MyFieldName is the actual name of the field.

    Unless, of course your original Excel VBA comment was meant to say that you are trying to do this from Excel, in which case I don't have any advice!

    Steve

  3. #3
    Join Date
    Aug 2012
    Posts
    3
    I have an Excel VBA program that needs the length programatically to use in loops and if...then statements.

    Although any method is ok as long a you can program it into Excel.
    Last edited by Philosophaie; 08-24-12 at 18:31.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    by 'length' do you mean the number of rows in the recordset?

    you can use movefirst, movelast methods then recordcount property
    MoveFirst Method
    How to: Count the Number of Records in a DAO Recordset
    however the recordcount property means you have to iterate through the full recordset.
    a smarter way might be to issue another SQL statement to find the number fo rows that match the criteria
    eg
    Select count(mycolumn) as norecords from mytable
    where astringcolumn = 'blah' and anumericcolumn = 666

    providing the where clause is the same it will return the required rows which you can carry forward elsewhere.

    if length of field is required then
    select my,column,list, len(astringcolumn) as acolumnlength from mytable


    or if you mean the column size then you'd need to iterate through the mysysobjectgs to find the field definition.

    or you could be a bit devious add a new row, then edit it and and keep adding characters to a string column updating the row, when it fails you know the column size.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I have an Excel VBA program that needs the length programatically to use in loops and if...then statements.
    well in which case why don't you use the len() function or its equivalent in Excel VBA?
    https://www.google.co.uk/#hl=en&scli...w=1366&bih=681
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post

    ...well in which case why don't you use the len() function or its equivalent in Excel VBA?
    And...why don't you post this in an Excel forum, since the app you're proposing using it in is an Excel app! The fact that the data resides in an Access Table has nothing to do with Access or how Excel VBA would be implemented to retrieve it!

    Although all VBAs arise out of the same 'mother language' they are each very distinct in both Functions and syntax. You might stumble across someone, here, who is very familiar with Excel VBA, but you'd stand a much better chance of finding appropriate help from an appropriate forum.

    http://www.excelforum.com/

    http://www.exceltip.com/

    http://social.msdn.microsoft.com/For...celdev/threads



    Linq ;0)>
    Last edited by Missinglinq; 08-25-12 at 16:27.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not forgetting of course dbForums
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Who knew a 'database forum' had a 'non-database' section?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Going back to the origional question, if by 'length of a field in a table' you want the number of charaters set at design time, then this will provide the value

    Code:
    Dim SQL as string
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    conn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12 .0;Data Source=c:\Users\Philosophaie\Documents\a High Priority\a SQL\a Chase.accdb;"
    
    sql = "SELECT TableName.* FROM TableName"
    rs.Open sql,conn
    
    MsgBox rs.Fields("FieldName").DefinedSize
    
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    HTH !?


    MTB

Posting Permissions

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