“link” greyed out in paste special

Topic

New Reply

In Access 97, when I attempt to creat a link from access, the “link” box in paste special is greyed out. Any suggestions will be appreciated.

Sam W.

Reply | Quote

Replies

Sam: Where are you trying to paste the link from/to?

Reply | Quote

Dear Dreamboat,

Thanks for responding.
Basically, I do not seem to be able to link anything from access to anything. The link button in paste special is always grayed out, whether to excel, word or within access.

Your reply will be appreciated
Thanks,

Sam W.

Reply | Quote

Well, that’s because there are very few, if any, “links” that you can paste from Access. If you are trying to link to an Excel spreadsheet, what you do is create the file using the normal paste. Save it, close it. Open Access and hit File-Get external data-Select the Excel spreadsheet and make sure you choose LINKED! Now, anything you do to the Excel file will be seen in the Access table. You cannot, however, change (most or all) the design of a linked Excel table. You can enter data through Access, though.

I’ve never heard of linking Word data in the manner that you’re thinking of. Perhaps someone else has.

Reply | Quote

Dreamboat,

I’ll give it a shot. I’m a little puzzled, though. The books I’ve got say it’s supposed to be simple to link stuff from access, but apparently no one told anyone at Microsoft? Is this corrected in access 2000?

Thanks again,

Sam W.

Reply | Quote

Sam: You’ll need to give me specific instances as to what and how you want to link Access with other programs. I can only give you examples, ’cause there’s a million ways to do just about anything. Here’s another example:

You can “link” Word to Access by using an Access table as the data source for a mailmerge document in Word. You can then use Word OR Access or Either to “activate” that mailmerge process. This does not infer that you can use the “paste link” feature between Word and Access. I’m sure you can understand the difference. I do want you to know, however, that the paste link feature is much more available in and between the other three Office applications.

Reply | Quote

If you want to paste link from Access to Word, it should not be grayed out. I’ve paste linked tables on occasion. I don’t know why the option isn’t available to you. However, a much better method of getting the contents of a table or query into a Word document, with a refreshable link, is this:

1) In Word get to the View menu, click on Toolbars, then click Database.
2) Click the toolbar button called Insert Database. Found on the right of the two sort buttons. You will get a dialog box that has three choices. Get Data, Query Options, and Insert Data.
3) Using get data, choose the appropriate options to get to your Access database files.
4) Set your query options if any. NOTE: you’re better off having a query built in Access as opposed to using the query options.
5) Choose Insert Data when you have everything selected that you want. If you check the box labeled Insert Data as Field, you have a field that can be “recalculated” basically it will reread the Access data, when you recalculate the field. Thus it is linked, but it works much better than Paste Link.

NOTE: As always with doing this with Access databases, be careful if the database has stuff automatically startup when the database file is opened. This can interfer with this process.

TIP: When bringing dates and/or numeric fields over, you may want to use a query with the format function to make dates and numbers display the way you like. If you don’t you may get raw data formatting. For example, if you insert a table Yes/No fields, you may end up with a list of records with -1 for Yes and 0 for No, as opposed to Yes and No coming over.

It is not uncommon to insert databases in this format, and makes for handy reporting to have the formatting capabilities in Word, with the data in Access. For example, in the inserted database I can italicize and bold part of the data in a field. Something Access can’t do is multiple formatting in the same field. I can’t make some stuff in a field bold and some not, it’s either all bold or not all bold.

HTH

Reply | Quote

Tom,
Thanks for your detailed reply.
Here’s what I am trying to do and here’s what I am accomplishing.
1) I’ve got a simple table that I made two queries from. Both queries share a common field, “Name”, which is the name of a student that is generated from a calculated field [FirstName] plus [LastName]. I then made a report from each of these queries and then tried to add one as a subreport to the other. I’ve used the report wizard and also tried it “manually” by dragging
the subreport’s icon onto the parent report’s design view. What I get is the parent report followed by a page and a half or so of blank paper. Can a calculated field be used as a parent/child field, or do I have to use the ID field?
Also, to Tom G. As an alternative to doing this I tried your suggestion of sending the two reports to Word, and then reformating as I pleased in a table format. It worked great. I also used it to generate our attendance list which needs boxes after the names for handwritten signatures and comments. The only problem was when I updated it it erased all my formatting, making it next to useless. I want to generate this stuff every week. Is there a way to import/export queries to Word, format them and make the formating stick after updating so I can use it as an alternative to a report? Also, is there a way to make the update automatic?

Thanks in advance,

Sam W.

Reply | Quote

The following is helful if the number of records doesn’t change.

If you insert the database as a field, (so it can be updated) you can add * MERGEFORMAT to the field’s code to preserve the formatting of the inserted database. You can do this manually, or if you simply choose Field, from the Insert menu, then choose (ALL) in the field categories, you can pick Database from the list of Field Names. There will be a checkbox that states: “Preserve formatting during updates”

For example, I have a Word document that pulls a query into it. The query is a simple list of Full names (calculated on a concatenation of First and Last) with their corresponding phone numbers. In Word the column headings are bold and the table is centered. When I update the field, the table is still centered and the column headings are still bold. Without preserving the formatting, I would have to redo that each time I updated the data.

* You could also create a macro that applies the formatting or even make a macro that inserts the database and formats altogether in one operation each time you need it.

HTH and I hope I’m Clear

Reply | Quote

Tom,

Can you give a sample of the field code for that link? I’ve never had any luck with inserting a database link in Word (well, I haven’t tried all that hard, but then I’m lazy … sigh).

Reply | Quote

OK, better late than never:

{ DATABASE d “C:DataDatabasesAcc97LearningAddresses.mdb” c “QUERY qryPhones” s “SELECT * FROM [qryPhones]” h * MERGEFORMAT }

The formatting remains intact if I change someone’s Phone number in Access. Depending on how you’ve set your input mask, you may want to use a Format(WhateverField,”AppropriateFormat”) in a query that supplies the “table” in word. This way you don’t lose the phone number formatting etc. of the raw data in the actual access table.

I like this method of inserting data, as I can use various sources in the same Word Document. (You will have to do some reformatting in word, should say you add records in the source table). But, I still think this is OK for my purposes, we’re not talking thousands of records here.

-TomG

Reply | Quote

Thanks. I’ll add that trick to my bag.

Reply | Quote