Linking Tables in Enterprise Manager

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have changed the character length of a field in Enterprise Manager, I then
ran a deletescript in SQL Analyzer. When I create a query in Access, it does
not seem to recognize the changed character length and cuts off the data
mid-sentence. A couple of suggestions to remedy this problem is to unlink
the table and then re-link it to Access. Unfortunately, I know how to link
tables from Enterprise Manager, but I don'T know how to unlink tables! Can
anyone help me with this?
 
You want to do the un-linking/re-linking in Access, *not* Enterprise
Manager. To un-link in Access, simply highlight the table in question and
delete it. To re-link it, from the menu choose File | Get External Data |
Link Tables. Under Type choose ODBC - this will open the ODBC DSN picker.
Choose the DSN for your SQL Server adn click OK. You'll be prompted for a
username and password, then a list of tables/views will come up. Be sure to
check the Save Password box! Select the table in question and click OK.
Note: the table will be added as "dbo_" + whatever the table name is.
Highlight it and press F2 to rename it, if you wish.
 
I think the suggestion was meant to be done from the Access end.

Open the Access end and use the Menu Tools / Link Table Manager to re-link
the ODBC-linked Tables.
 
--
S

I tried both your suggestion and Ron Hinds suggestion and both were great
but they didn't solve the problem. I have tried everything that I can think
of, but I can't seem to get past this one. Any other suggestions?
 
I tried both your suggestion and Van T. Dinh's suggestion and both were great
but they didn't solve the problem. I have tried everything that I can think
of, but I can't seem to get past this one. Any other suggestions?
 
You say you changed the length of the character field. What was the previous
length and what did you change it to? Is it a varchar? Or nvarchar?
 
The character length was 30 and I changed it to 255. I also tried to change
it from varchar to memo, but went back to varchar because all of the other
lengthy text fields were varchar. For background information, this is a
docketing database that was already in place. I customized the unused fields
that were already in the database to fields that could be utilized by my
firm. That is why the drastic change in character length. I was successful
in changing other fields to different lengths, that is why I am stumped.
Thanks for your help and patience.
 
What are the old length and new length?

Delete Script? Can you post the SQL of your Delete Script.
 
Then I, too, am stumped. Deleting and manually relinking has always solved
that problem for me (so far). Only one other remote possiblity comes to
mind - a single row in SQL Server may not be more than ~8000 bytes. If you
add up the length of all columns does it total more than 8000? The reason I
say this is remote is that IIRC SQL Server will warn you if you create such
a situation.
 
Sorry, that you are having so much trouble with something that should be
quite simple.

When you open Access and look at the tables you should see an icon which
resembles a tiny earth, with a small black arrow pointing to it. If that's
what you see, simply select the table and delete it. Assuming you don't have
warnings turned off, you should get a warning about deleting only the link.

Then use File ... Get External Data to link the table once again. Assuming
your permissions are OK and you have saved your password with the
connection, you can choose ODBC from the database type list and be able to
find your database. Click "Open" and select your table. Rename it by
removing the "dbo.", if necessary. That's it!
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top