Display data from linked spreadsheet in Form (2000 (9.0.3821 SR-1))

[kris love]

Brought this problem to you a week or so ago and have moved along a bit following your suggestions, but I

[HansV]

>> How do I attach the test database?

Create a .zip file containing the database, and attach the .zip file.

[kris love]

Sorry, Winzip is not loaded for us to use at work. This is zipped with the old DOS PKZIP. All I have. Had to delete all of the fields, data, and tables except the 3 fields we are working with to get the file under 100. It makes the form really mad, but it is the "tag" field we need to see working. Can you get it open?

[patt]

I had a similar problem where I could not (was not allowed to) link to the Live Systems JDE Oracle table. What we ended up doing was for JDE to generate a CSV file at 4.30 am each day. From there my Access program would start trying to import the CSV file at 6.00 am each morning. After this we had an Access table which was the reference and not the Oracle table. The same can be done with your spreadsheet.

What time of the day is it created from the Oracle table?

[HansV]

Hi Kris,

1. I don't understand what vdn is, and how you can link it to the excel table, because it is empty. I don't think you need it.
2. The reason that you can't create a one-to-one relationship is that you would need a unique index on both sides for that, but you can't create an index on a linked Excel table.
3. To update the TAG_DESCRIPTION field in the dnis table, you can create an update query that selects records for which the dnis field is equal to the last four digits of the dnis field in view_xls. The SQL for this query is

UPDATE view_xls, dnis SET dnis.TAG_DESCRIPTION = [view_xls].[tag_description]
WHERE dnis.dnis=Right([view_xls].[DNIS],4)

I have attached a screenshot of the query in design view; the captions are in Dutch and there is a semicolon in the Right function because of my Regional Settings, but I hope that the basic idea will be clear.

If you don't want to update, you can use the same pseudo-link in the form of criteria in a selection query. You can also turn it into a real join, but then, you can't view the query in design view any more, because Access can't display a join on an expression in design view. SQL view and datasheet view will still work.

SELECT dnis.dnis, view_xls.TAG_DESCRIPTION
FROM view_xls RIGHT JOIN dnis ON Right(view_xls.dnis,4) = dnis.dnis

[kris love]

Hi HansV,
Eureka!
1. Whoops. Of course the TABLE is empty. I put ="2" & [dnis] in the FORM property ControlSource for that field. I can see now that vdn is not needed for the update query to work, but the users still need it. And now that I try to run merges into Word, there's nothing there because there's no data in the table to merge. No matter how often we tell them that "vdn" is "dnis" with a 2 in front of it, they don't get it. How do I get ="2" & [dnis] (or it's equivalent) into the table? Can it be done in Link Master Fields in Table Properties?
2. I was afraid of that. But there will never be more than one entry for each item in either the table or the spreadsheet.
3. Oh, my Zeus, it works. I even had nerve enough to put it in the real database and voila! But I made three (3!) backup copies first. I remember Right and Left from the old Atari Basic days when you could use Right, Middle, and Left String.

Now, am I right that this query only updates from the source when it is run. It does not automatically update when changes are made to the source. So I would open the database, run the update query to capture all current entries (in case they have changed from the last query run), and go on with the day. Or is this a run-it-once update, and if I try to run it a second time I will foul things up? And I have to manually enter any new changes into the database when they are entered into the source?

I'm going to wait for that answer before testing it out on a user system. I'm wondering if the query will still work after copying the database from it's original location to a shared drive. The location of the linked file doesn't change, so it oughta. . . .hopefully. Super Duper Thanks! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

Patt,
Now there's a thought. We'd have to ask the owners of that Oracle database to create a special .csv run for us but it could be done. Right now, the Oracle doesn't create a file like that. The export to Excel is a manual process done by whoever has just made a change. What the Oracle does is create a real-time feed. The instant I hit enter after making a change, the new data appears on the screens of 1000 or so phone agents. The .csv possibility is good knowledge to have for the future. Thanks!
<img src=/S/joy.gif border=0 alt=joy width=23 height=23>

[HansV]

The update query I posted will only do something when it is run explicitly. If you run it after the Excel file has been updated, it will write the new values into the table, overwriting the older values, but that's what you want, I assume. What the update query won't do is add new values of dnis from the Excel table to the dnis table; you'd need an append query for that.

You could also use an update query to populate the vdn field; just create a query based on the dnis table, add the vdn field, select Query/Update Query, and enter "2"&[dnis] in the Update To: row.

If you alway want up-to-date values, you can use a select query, as suggested at the end of my previous reply; you can add the vdn field as a calculated field too:

SELECT dnis.dnis, "2"&dnis.dnis AS vdn, view_xls.TAG_DESCRIPTION
FROM view_xls RIGHT JOIN dnis ON Right(view_xls.dnis,4) = dnis.dnis

You would then use this query as record source for forms and reports in the database, and as data source for mail merges in Word.

Oh, and if you move the database to another location, but leave the Excel file where it is now, the link will still work. It would be different if it was the other way round, for the path to the Excel file is hard-coded. You'd have to use the linked table manager (Tools | database Utilities) to update the path to the Excel file then.

[kris love]

Great! That's exactly what I wanted. Running the query replaces old data with new data from the tag_description field. And I did NOT want it to try to replace new dnis data from the spreadsheet into the database. Dnis is controlled from the database.

Got the second update query created which populates the vdn field in the table. Slick. I'll tell you, queries are not for sissies. No matter how much you read, sometimes it is just not clear until you have your own issue to solve. Even then, it was not clear until my own fields were sitting in there. Then I could see the logic. Hopefully most of this will stick in case I ever have to query again.

Thanks, thanks!