467,219 Members | 1,296 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,219 developers. It's quick & easy.

A97: Linking tables to SQL Server 2K

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.

Nov 12 '05 #1
  • viewed: 3398
Share:
5 Replies
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
Nov 12 '05 #2
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
Nov 12 '05 #3
> 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

Nov 12 '05 #4
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

Nov 12 '05 #5
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
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by John South | last post: by
2 posts views Thread by Jeff Pritchard | last post: by
2 posts views Thread by Matthew Wells | last post: by
1 post views Thread by Andrew Chanter | last post: by
1 post views Thread by hmiller@hartford.edu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.