473,898 Members | 2,907 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 SourceTableName s
"<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
5 3786
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*****@cloud9 9.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****@teluspl anet.net> wrote in message
news:tb******** *************** *********@4ax.c om... Dimitri Furman <df*****@cloud9 9.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*****@cloud9 9.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****@teluspl anet.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
4404
by: John South | last post by:
I have an Access 2000 front end that I wish to work with a SQL Server 2000 database by means of Linked tables. Do I have to use an ODBC connection to SQL Server? It seems to be the only option offered in the table linking dialog. Is there a more direct connection with OLE_DB? John South Pangbourne UK
2
4510
by: Jeff Pritchard | last post by:
Some time ago I am sure I came across something that said this was possible, though it doesn't seem to work. A client wants to replace an Access back-end with SQL Server tables. We have tried linking the SQL tables to an Access back-end and then linking the linked tables in the Access back-end to the Access front-end. Doesn't work at all. Obviously, this can't be done. What is the best way to replace an Access back-end with SQL tables?
0
2252
by: gasturbtec | last post by:
please help im new at access programming and i just got this project dropped in my lap because the old programmer quit. i've been doing ok so far but now i need to add code to an existing database that is used to connect to other databases and generate reports. below is sample code of how the database does the linking i hope i give you enough info to help me but if not let me know and i will give more. Sub txtShipDataFileSub() Dim...
22
2375
by: Bradley | last post by:
Has anyone else noticed this problem? I converted the back-end to A2000 and the performance problem was fixed. We supply a 97 and 2000 version of our software so we kept the backend in A97 to make upgrading simple for users. We've done it like that for years but a new client has been having severe performance issues... solved by converting the backend to 2000. -- regards, Bradley
18
3102
by: Mark P | last post by:
I have a bit of vb code that uses Tables.Append to programatically link tables from Oracle and DB2 datasources. The problem I am having on some client machines is that the link will take anywhere from 1 to 3 minutes to link the table. When I manually link the tables,through Access, it takes no time at all. Note that only certain client have the problem, and the problem will sometimes go away. These clients will usually get an ODBC Call...
2
4146
by: Matthew Wells | last post by:
Good morning... I have an Access front end that uses SQL Server linked tables. SQL Server uses Windows authentication. I have one Windows group that all Access users are a member of. I added that group to SQL Server logins and gave it public, datareader, and datawriter rights to the one database that's used. My front end is locked down, but I want to stop users from creating a new ..mdb and linking SQL Server tables through DSNs or...
1
1469
by: Andrew Chanter | last post by:
I have taken over support of an A97 application which has presented an interesting and confounding scenario involving workgroup security. I cant figure out whether my predecessor deliberately engineered this situation or has hit on a bug in the security model and exploited this. The system is in 4 components, a database back end, an application front end (ODBC links to back end), an administration front end and a workgroup security...
1
2025
by: hmiller | last post by:
I'm sorry to populate the server with yet another question about linking multiple tables and queries, howerver I have not been able to find the right criteria. My problem. I am trying to either link 3 tables together in a select query, and maintain the editablity that I get when linking two tables and creating a form. I know there is a way to link 3 tables through multiple queries however I have yet to be able to get it to work.
3
3173
by: ARC | last post by:
I'm having trouble here with Access 2007 and connecting to a different database. The code below works great IF the previous back-end database connection is still present and you are trying to connect to a new location (ie: datapath variable below). However, if you remove or even rename the old database, then launch your program, the code fails on the mytable.refreshlink line with an error 3044. (Description of that error is: 'C:\pc
0
9993
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11265
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10858
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10484
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9662
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5882
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6077
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4706
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3306
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.