Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2004
    Posts
    17

    Unanswered: SourceTableName truncating

    My initial problem is that in MS Access the SourceTableName property of a TableDef is truncated to a maximum of 32 characters.

    I searched high and low and could not find a solution, trawled as many forums as I could all to no avail (I could not even a Microsoft admittence that the problem exists).

    So I gave up and simply made the names of my tables shorter.



    But I KNOW the full SourceTableName exists somewhere and I can prove it!

    1. Create a database be.mdb and one table called "this table name is forty characters long"
    2. Create a database fe.mdb and link in the table created in step 1)
    3. Change the name of this linked table to "precise"
    4. In the Immediate Window in Visual Basic type the following :-

      ? CurrentDb.TableDefs("precise").SourceTableName
      You will see "this table name is forty charact" this is the most information you can get from the SourceTableName property
    5. Now open be.mdb and change the table name from "this table name is forty characters long" to "anything"
    6. Open fe.mdb and then try to open the table "precise"
    7. As the linked table's original has changed you will get the error message :-

      The Microsoft Jet database engine cannot find the input table or query 'this table name is forty characters long'. Make sure it exists and that its name is spelt properly
    8. Notice that the error message includes the FULL AND CORRECT SourceTableName

    My question therefore is that surely the full original name for the linked table MUST be somewhere in fe.mdb and possibly accesible WITHOUT using the SourceTableName property.
    Does anyone know how to get to it, that way I could avoid the limitation of using the

    SourceTableName property.


    I am using Windows XP and Access 2000


    Thanks

    Sean

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    DELETED (was crap)
    Last edited by izyrider; 02-19-06 at 13:42.
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mmmm
    in design view, i cant rename a table to a hugely long name

    from help/Specifications
    Number of characters in a table name 64

    i can achieve and (linked table) retrieve that:

    ? currentdb.TableDefs("tblNufink with a hugely long backend name - gotta wonder why any").Name
    tblNufink with a hugely long backend name - gotta wonder why any

    ? currentdb.TableDefs("tblNufink with a hugely long backend name - gotta wonder why any").Connect
    ;DATABASE=C:\JunkDocuments\Junk\Junkdb\very long access backend database name to discover how we can uncover the name.mdb

    izy

    ADDED LATER:
    ? len(currentdb.TableDefs("tblNufink with a hugely long backend name - gotta wonder why any").name)
    64
    Last edited by izyrider; 02-19-06 at 13:47.
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ?? what are you trying to do?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jun 2004
    Posts
    17
    I think you missed the point. My issue is with the SourceTableName property, not the Name ot Connect properties

  6. #6
    Join Date
    Jun 2004
    Posts
    17
    If you look through the original post it pretty much highlights my problem.

    The actual goal is to re-link attached tables. These tables might have a different name in the FE than their original name, so any re-linking (usually to a different database) needs to have the ORIGINAL table name.

    Clear as mud!

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you could very well be right.
    but what are you trying to achieve with SourceTableName?

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Jun 2004
    Posts
    17
    Quote Originally Posted by izyrider
    you could very well be right.
    but what are you trying to achieve with SourceTableName?

    izy
    Well, I MUST have the correct SourceTableName in order to re-link a table.
    Simple as that
    ------
    Sean

    "If at first you don't succeed, go to sleep"

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    no sir - you do NOT need SourceTableName to relink - that's why i was asking.

    i'll post an e.g. in a while - meanwhile it's dinner time: sorry

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Jun 2004
    Posts
    17
    Quote Originally Posted by izyrider
    no sir - you do NOT need SourceTableName to relink - that's why i was asking.

    i'll post an e.g. in a while - meanwhile it's dinner time: sorry

    izy
    well yes and no. Generally speaking you will not need the SourceTableName when relinking but imagine this

    I have two databases dbA.mdb and dbB.mdb
    A table in dbB (called timmy) if linked into dbA and it's name changed to tommy
    I now want to replace dbB with dbC (the structure of dbB and dbC are not identical)
    In fact some tables in dbC are the same as in dbB, others have the string "NEW" appended onto them
    I now need to run through every linked table in dbA, then check if it can be linked from dbC instead of dbB (which I am about to delete)
    Remember in dbA I have a table called tommy, this does not exist in dbB OR dbC HOWEVER tommy's original name (i.e. timmy) does.
    Simply put I cannot find timmy unless I know tommy's original name.


    Easy uh
    ------
    Sean

    "If at first you don't succeed, go to sleep"

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ok
    - you don't need an e.g. of relinking from code
    - you do need SourceTableName to do something that it seems to be unwilling to do for you.
    ...sorry: can't help with that one..

    last gasp: ?? store Timmy & Tommy in your own table rather than relying on msys tables?

    happy evening, izy
    currently using SS 2008R2

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Izy
    DELETED (was crap)
    Lol

    Quote Originally Posted by Sean_F_Howard
    Does anyone know how to get to it, that way I could avoid the limitation of using the

    SourceTableName property.
    You can query the ForeignName field of the Msysobjects Table (Text (255)).

    HTH
    Last edited by pootle flump; 02-19-06 at 19:48.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just played with ADOX too. Quite happily read a 53 char source table name length with the
    Code:
    Properties("Jet OLEDB:Remote Table Name").Value
    property of the table object.

    Unless you have jolly good reason, though, I would suggest that the artificial limit of a maximum of 32 char table names you feel has been imposed on you has not been a bad thing. Table names should be a brief indication of the entity they represent rather than a long hand description. It is a bit like when you meet someone who was allowed to write their own job title and they decided to make it a full list of all their responsibilities.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmm - now I'm really confused. I followed your example in post one and got the full untruncated source table name. In fact, I got to the 64 char limit of the table name before I could get a truncated SourceTableName.

    What version of Access, JET and DAO are you using?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jun 2004
    Posts
    17
    Quote Originally Posted by pootle flump
    Hmm - now I'm really confused. I followed your example in post one and got the full untruncated source table name. In fact, I got to the 64 char limit of the table name before I could get a truncated SourceTableName.

    What version of Access, JET and DAO are you using?
    I am using Access 2000, DAO 3.6, and JET (no idea).

    However I think I will query MSysObjects directly
    ------
    Sean

    "If at first you don't succeed, go to sleep"

Posting Permissions

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