MS Access database support - SourceTableName truncate

Author

SourceTableName truncate

Sean Howard

2006-02-19, 1:24 pm

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

------
Sean

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

*** Sent via Developersdex http://www.droptable.com ***

Anthony England

2006-02-19, 1:24 pm

"Sean Howard" <sean.howard@TimeaIntLtd.hu> wrote in message
news:912Kf.21$z87.7373@news.uswest.net...
> 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
>
> ------
> Sean

With WinXP & AccXP I cannot replicate the problem in step 4.
Here's my immediate window:

?CurrentDb.TableDefs("precise").SourceTableName
this table name is forty characters long

What can you see if you use the Access UI to open the linked fe table in
design view, then select View>Properties? Are you saying the name is
truncated here too?

Sean Howard

2006-02-19, 1:24 pm

I I look as you suggect through the Access UI I can indeed see the
entire original table name.

This is great but I cannot find a way to replicate this method through
VB. The "Description" property I see in Access UI does not appear to be
a part of a TableDef object.
Do you know where it comes from ?

------
Sean

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

*** Sent via Developersdex http://www.droptable.com ***

Anthony England

2006-02-19, 1:24 pm

"Sean Howard" <sean.howard@TimeaIntLtd.hu> wrote in message
news:tF2Kf.26$z87.8975@news.uswest.net...
>I I look as you suggect through the Access UI I can indeed see the
> entire original table name.
>
> This is great but I cannot find a way to replicate this method through
> VB. The "Description" property I see in Access UI does not appear to be
> a part of a TableDef object.
> Do you know where it comes from ?
>
> ------
> Sean

Go to the Google Groups search page and type in these two terms:
32 sourcetablename
From the results, it looks like it is indeed a bug in A2K but I cannot find
any MS knowledge base article confirming the bug. As it works OK on my
system, I would guess this is somthing that has been fixed in A2K2. But for
all I know there may be a fix for this in A2K.
I could not find any work-around to get the full name in vba. If I had to
investigate one, I might look at using another object library - eg what
happens with ADO?

Sean Howard

2006-02-19, 1:24 pm

Thanks Anthony, I have already been through Google groups and all I
could find was a lot of questions, but no answers.

Do you understand AccessObjectProperti
es as somewhere in there I might
find the equivalent Description property from the Access UI. Either
getting to AccessObjectProperti
es is very complicated or I need some
sleep because I simply cannot get my head around it?

------
Sean

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

*** Sent via Developersdex http://www.droptable.com ***

Anthony England

2006-02-19, 8:38 pm

"Sean Howard" <sean.howard@TimeaIntLtd.hu> wrote in message
news:eq3Kf.36$z87.10653@news.uswest.net...
> Thanks Anthony, I have already been through Google groups and all I
> could find was a lot of questions, but no answers.
>
> Do you understand AccessObjectProperti
es as somewhere in there I might
> find the equivalent Description property from the Access UI. Either
> getting to AccessObjectProperti
es is very complicated or I need some
> sleep because I simply cannot get my head around it?
>
> ------
> Sean

I don't know if that might open up any possibilities, but I would look
elsewhere first. Try using ADOX. If you copy and paste this function what
does it return if you type ?GetSourceTableName("precise")

Public Function GetSourceTableName(s
trLinkedTable As String) As String

On Error GoTo Err_Handler

Dim cat As Object 'ADOX.Catalog
Dim tbl As Object 'ADOX.Table
Dim prp As Object 'ADOX.Property

Set cat = CreateObject("ADOX.Catalog")

cat.ActiveConnection = CurrentProject.Connection

Set tbl = cat. Tables(strLinkedTabl
e)

Set prp = tbl.Properties("Jet OLEDB:Remote Table Name")

GetSourceTableName = prp.Value

Exit_Handler:
Set prp = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Sean Howard

2006-02-19, 8:38 pm

OK, I tried it and it worked perfectly (thank you).

So my next question should be why did it work (or what is ADOX)

------
Sean

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

*** Sent via Developersdex http://www.droptable.com ***

Anthony England

2006-02-19, 8:38 pm

"Sean Howard" <sean.howard@TimeaIntLtd.hu> wrote in message
news:NW4Kf.42$z87.13559@news.uswest.net...
> OK, I tried it and it worked perfectly (thank you).
>
> So my next question should be why did it work (or what is ADOX)
>
> ------
> Sean

ADOX is an object library which can be used in a similar fashion to DAO.
Currently you have been working with (whether you knew it or not) DAO and
have been using the DAO.TableDef object to get to the SourceTableName
property.

There is a competing object model called ADOX which has the Table object and
this object has a number of properties - one of which is named "Jet
OLEDB:Remote Table Name" from which you get the table name you are looking
for.

Roughly speaking they both do similar tasks. ADOX is the newer technology
and if you had believed the MS documentation when A2K was released, you
might have believed that my function (or similar) would be the preferred way
to write the code. However, you can do things with DAO that you can't with
ADOX and if you are working with an 'all-Access' solution, then you are
probably better off sticking with DAO.

Anyway, this sort of debate rages here often and I don't really want to get
into it. It was just a guess that if a bug affected your first approach,
then using another approach with a different object library would not give
rise to the same bug.

david epsom dot com dot au

2006-02-19, 8:38 pm

I have a table called
t23456789_123456789_
123456789_123456789_

I have linked it as t23456789_123456789_
123456789_123456789_

when I do
?codedb.TableDefs(" t23456789_123456789_
123456789_123456789_
").SourceTableName
I get
t23456789_123456789_
123456789_123456789_

A2K/Jet 4.0

On my count, that is 40 characters.

The A97 specification says that the number of characters in a table name is
64.
If you are seeing only 32, I suspect some odd interaction with double_width
characters. What language is Windows, Office, and the database?

(david)

"Sean Howard" <sean.howard@TimeaIntLtd.hu> wrote in message
news:912Kf.21$z87.7373@news.uswest.net...
> 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
>
> ------
> Sean
>
> "If at first you don't succeed, go to sleep"
>
> *** Sent via Developersdex http://www.droptable.com ***

Sean Howard

2006-02-20, 7:24 am

David,

The language of Windows/Office/Access is English.

I have checked the problem with the different versions of Access and
(not suprisingly) get different results. Access '97 works fine but
Access 2000 & XP do not, currently I am using Access 2000

I think I will use the MSysObjects system table directly instead, all
the necessary information seems to be there

------
Sean

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

*** Sent via Developersdex http://www.droptable.com ***

Anthony England

2006-02-20, 7:24 am

"Sean Howard" <sean.howard@TimeaIntLtd.hu> wrote in message
news:dkgKf.562$z87.31140@news.uswest.net...
> David,
>
> The language of Windows/Office/Access is English.
>
> I have checked the problem with the different versions of Access and
> (not suprisingly) get different results. Access '97 works fine but
> Access 2000 & XP do not, currently I am using Access 2000
>
> I think I will use the MSysObjects system table directly instead, all
> the necessary information seems to be there
>
>
> ------
> Sean

This is always a possibilty but in general you are advised against using
MSysObjects directly. While the DAO and ADO object libraries are provided
for programmers to use, MSysObjects is not. You have no guarantee that code
which currently works will continue to work - changes may be made to this
system table which are beyond your control.
Having said that, I think you would be fairly safe with this one, especially
if you put in appropriate error handling. However, I'm not sure what
advantage you hope to gain over the first function I proposed.

David W. Fenton

2006-02-20, 8:25 pm

"Anthony England" <aengland@oops.co.uk> wrote in
news:dtc4k8$mdb$1@nw
rdmz01.dmz.ncs.ea.ibs-infra.bt.com:

> This is always a possibilty but in general you are advised against
> using MSysObjects directly. While the DAO and ADO object
> libraries are provided for programmers to use, MSysObjects is not.
> You have no guarantee that code which currently works will
> continue to work - changes may be made to this system table which
> are beyond your control. Having said that, I think you would be
> fairly safe with this one, especially if you put in appropriate
> error handling. However, I'm not sure what advantage you hope to
> gain over the first function I proposed.

MichKa actually disagrees with this position somewhat in regard to
Jet:

http://trigeminal.com/usenet/usenet017.asp?1033

He describes Microsoft's policy on how these things work, that
nothing that any feature in any past version of Access depends on
will ever be changed, and outlines the kinds of things in system
tables that you can depend on.

By MichKa's formulation, this is one case where you'd be fine
relying on looking in MSysObjects directly, as by MS policy,
anything that's been implemented there is not going to be altered in
future versions of Access.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Anthony England

2006-02-20, 8:25 pm

"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns9770A1DF063A
Ff99a49ed1d0c49c5bbb
2@127.0.0.1...
> "Anthony England" <aengland@oops.co.uk> wrote in
> news:dtc4k8$mdb$1@nw
rdmz01.dmz.ncs.ea.ibs-infra.bt.com:
>
>
> MichKa actually disagrees with this position somewhat in regard to
> Jet:
>
> http://trigeminal.com/usenet/usenet017.asp?1033
>
> He describes Microsoft's policy on how these things work, that
> nothing that any feature in any past version of Access depends on
> will ever be changed, and outlines the kinds of things in system
> tables that you can depend on.
>
> By MichKa's formulation, this is one case where you'd be fine
> relying on looking in MSysObjects directly, as by MS policy,
> anything that's been implemented there is not going to be altered in
> future versions of Access.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/

I thought that was what I said - you should be OK with this one especially
with error handling. However, as a general principal, I prefer to use the
documented object libraries to write my code and I don't understand what
advantage could be gained in turning one's back on the documented approaches
in favour of MSysObjects.
Of course, ideally one would get to the source of the trouble -
tdf.SourceTableName returning a truncated string - and sort out this
problem. However I could not replicate the problem nor find any MS
knowledgebase article on it (although I found others complaining of it).

Sponsored Links