A97: Linking tables to SQL Server 2K

[Christoph Sticksel]

Hi,

I'm having problems with attaching two tables stored in an SQL Server
2000 to an Access 97 database. It worked well for all other tables
except those two.

This is what I did: Choose the data source from the ODBC dialog, choose
the database on SQL Server login, select all tables in the SQL Server
database. All tables were attached except the two, it says something[1]
like "database module can't find 'dbo_<tablename>'".

The name is not exceptionally long, a table with a 33-char name was
attached, the name of first table that was not attached was 26 chars.

I also tried it in code via CreateTableDef using SourceTableNames
"<database>.dbo.<table>", "dbo.<table>" and "<table>". All thre gave the
same error message as above, runtime error 3011 for the record.

Funny thing is that using MS Query or a pass-through query works.
(SELECT * FROM <database>.dbo.<table>).

I did check permissions on the tables in SQL Server.

I there something I missed or a workaround?

Some figures:
Access 97, SR-2 (German),
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
ODBC driver SQLSRV32.DLL version 2000.81.9042.00

TIA,
Chris

[1] Sorry if I didn't quite get the right words, I'm using german
versions here.

[Dimitri Furman]

On May 16 2004, 06:40 pm, Christoph Sticksel <sp***************@gmx.net>
wrote in news:c8**********@news2.rz.uni-karlsruhe.de:

I'm having problems with attaching two tables stored in an SQL Server
2000 to an Access 97 database. It worked well for all other tables
except those two.

This is what I did: Choose the data source from the ODBC dialog, choose
the database on SQL Server login, select all tables in the SQL Server
database. All tables were attached except the two, it says something[1]
like "database module can't find 'dbo_<tablename>'".

The name is not exceptionally long, a table with a 33-char name was
attached, the name of first table that was not attached was 26 chars.

Are there any indexes on these tables with the names that are longer than
64 characters? If there are, this would cause a problem with linking,
although I don't recall the particular error message you would get in that
case.

--
remove a 9 to reply by email

[Tony Toews]

Dimitri Furman <df*****@cloud99.net> wrote:

I'm having problems with attaching two tables stored in an SQL Server
2000 to an Access 97 database. It worked well for all other tables
except those two.

This is what I did: Choose the data source from the ODBC dialog, choose
the database on SQL Server login, select all tables in the SQL Server
database. All tables were attached except the two, it says something[1]
like "database module can't find 'dbo_<tablename>'".

The name is not exceptionally long, a table with a 33-char name was
attached, the name of first table that was not attached was 26 chars.

Are there any indexes on these tables with the names that are longer than
64 characters? If there are, this would cause a problem with linking,
although I don't recall the particular error message you would get in that
case.

Yeah, that's the problem. I knew I'd seen it before but couldn't recall the details.
(And it's at the bottom of my Random Thoughts on SQL Server Upsizing from Microsoft
Access Tips page at http://www.granite.ab.ca/access/sqlserverupsizing.htm.)

Error 3125 "The database engine can't find <name>. Make sure it is a valid parameter
or alias name, that it doesn't include invalid characters or punctuation, and that
the name isn't too long."

In particular the problem is likely the GUID based index names create when you setup
relationships.

To the original poster. Generate a SQL script via SQL Server Enterprise Manager.
Look for those long index names. Rename them. Recreate the SQL Server database and
reload the data.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[david epsom dot com dot au]

> In particular the problem is likely the GUID based index names create when
you

setup relationships.
I think:

when you create relationships, you get relationship names based on
concatenating the two table names - so two long table names give you
a long long index name.

GUID index names come from importing relationships (?? or something
like that ??), and are ?? 38 characters long ??

so perhaps a solution is to first import all from the original
MDB into a new MDB. NOTE: make sure you have Jet 3.51 SR3
before importing relationships in A97: SR2 duplicated relationships
when importing.

(david)
"Tony Toews" <tt****@telusplanet.net> wrote in message
news:tb********************************@4ax.com... Dimitri Furman <df*****@cloud99.net> wrote:

I'm having problems with attaching two tables stored in an SQL Server
2000 to an Access 97 database. It worked well for all other tables
except those two.

This is what I did: Choose the data source from the ODBC dialog, choose
the database on SQL Server login, select all tables in the SQL Server
database. All tables were attached except the two, it says something[1]
like "database module can't find 'dbo_<tablename>'".

The name is not exceptionally long, a table with a 33-char name was
attached, the name of first table that was not attached was 26 chars.

Are there any indexes on these tables with the names that are longer than
64 characters? If there are, this would cause a problem with linking,
although I don't recall the particular error message you would get in thatcase.

Yeah, that's the problem. I knew I'd seen it before but couldn't recall

the details. (And it's at the bottom of my Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page at http://www.granite.ab.ca/access/sqlserverupsizing.htm.)
Error 3125 "The database engine can't find <name>. Make sure it is a valid parameter or alias name, that it doesn't include invalid characters or punctuation, and that the name isn't too long."

In particular the problem is likely the GUID based index names create when you setup relationships.

To the original poster. Generate a SQL script via SQL Server Enterprise Manager. Look for those long index names. Rename them. Recreate the SQL Server database and reload the data.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Christoph Sticksel]

Dimitri Furman <df*****@cloud99.net> wrote:

On May 16 2004, 06:40 pm, Christoph Sticksel <sp***************@gmx.net>
wrote in news:c8**********@news2.rz.uni-karlsruhe.de:

I'm having problems with attaching two tables stored in an SQL Server
2000 to an Access 97 database. It worked well for all other tables
except those two.

Are there any indexes on these tables with the names that are longer than
64 characters? If there are, this would cause a problem with linking,
although I don't recall the particular error message you would get in that
case.

That's it thanks. As on stated on Tony's page, the table's name plus the
longest index name was longer than 64 characters. I changed that and the
tables were attached without problems.

Thanks again,
Chris

[Tony Toews]

Tony Toews <tt****@telusplanet.net> wrote:

To the original poster. Generate a SQL script via SQL Server Enterprise Manager.
Look for those long index names. Rename them. Recreate the SQL Server database and
reload the data.

This advice was way too complex. Should've been delete and recreate the index using
a short name.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm