Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Franklin, North Carolina, USA
    Posts
    124
    Thanks
    0
    Thanked 2 Times in 1 Post

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

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    >> How do I attach the test database?

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

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Franklin, North Carolina, USA
    Posts
    124
    Thanks
    0
    Thanked 2 Times in 1 Post

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

    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?
    Attached Files Attached Files
    Kris

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    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?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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
    Attached Images Attached Images
    • File Type: png x.png (13.5 KB, 0 views)

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Franklin, North Carolina, USA
    Posts
    124
    Thanks
    0
    Thanked 2 Times in 1 Post

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

    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>
    Kris

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Franklin, North Carolina, USA
    Posts
    124
    Thanks
    0
    Thanked 2 Times in 1 Post

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

    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!
    Kris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •