Results 1 to 9 of 9
  1. #1
    MacAcc is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    23

    Question linking table to excel but no way to get primary key

    Hi, I am new to Access and have the following problem.

    I am creating a db for financial instruments. I have created a table in Access for each security. Now I need to feed the securities with real time information on prices. The way a tried to do this is with a linked Excel spreadsheet because my bloomberg is able to update prices in Excel. It works fine, each time the prices changes in exel the linked table in Access is automatically updated (after close and reopen of course). The problema I face though is that on the linked table (which initially was imported from Excel) I am unable to set a primary key (Access is saying I can not modify linked tables in Access). But since there is no primary key, I can not use the feature of relating the linked table to other tables and the whole thing is useless.

    Am I doing something wrong or if not is there a work around?

    Thanks for help

    Markus

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    what do you need as primary key? The ticker is unique, so you don't NEED to key it.
    You should be able to query on the ticker field and join to any internal access tables to perform queries w/o key.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have created a table in Access for each security.
    Does this mean that if you have 5 securities, you have 5 tables? If the tables have the same structure for each of the securities, I would have 1 tables with an additional field for the security. Much easier to create forms and reports. And to add new securities.

    If you truly have linked tables (tables in a different accdb - ie the BE), you need to modify the tables in the BE, not the FE.

    I am also curious as to why you can get the securities data in Excel but not in Access.


    And welcome to the forum....

  4. #4
    MacAcc is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    23
    Tks, ranmam256, good to know but how can I do this? Let's get concrete: I have 2 tables. 1. is tblSec (not linked) with fields SecName, SecCcy and SecBloomID(contains Bloomberticker) a a second table 2. with fields BloomSecID (contains Bloomberticker) and BloomSecPrice. Ok now I create a query with these 2 tables and their field. How can I join so that I get the right price for each security? Tks

  5. #5
    MacAcc is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    23
    Tks, ssanfu and sorry I was not precise. I do not have a table for each security but a table tblSec for all securities (records). Why using Excel? Because Excel I can link to Bloomberg which allows to update prices or other real time data to update. I am not aware that access could do this.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You don't provide field types, but if tblSec.SecBloomID field type is the same type as table2.SecBloomID you can link on those fields.

    For table names, I used "tblSec" and "tblBloomPrice". Change "tblBloomPrice" to your table name.
    Maybe try:
    Code:
    SELECT tblSec.SecBloomID, tblSec.SecName, tblBloomPrice.BloomSecPrice
    FROM tblSec LEFT JOIN tblBloomPrice ON tblSec.SecBloomID = tblBloomPrice.BloomSecID
    ORDER BY tblSec.SecName;
    "tblSec.SecBloomID" should be unique.
    "tblBloomPrice.BloomSecID" would NOT be unique.




    As I said before, just curious. If you can link to Excel, you should be able to link to Access and cut out the middle man.

    Good luck on your project.....

  7. #7
    MacAcc is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    23
    Ok thks, where do ai put this code? Sorry I am really new to access.
    You need an add in to connect excel to bloomberg which is provided by bloomberg. There is no plug in for access.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It is SQL of a query. Create a new query, switch to SQL view and paste it in.


    You need an add in to connect excel to bloomberg which is provided by bloomberg.
    Ah-ha. Got it.

  9. #9
    MacAcc is offline Novice
    Windows 10 Access 2013
    Join Date
    Feb 2016
    Posts
    23
    Actually I found it quite easy: Just drag and drop to join the 2 fields with the same id (bloomberg ticker) to make the join. Now it works, thanks all for the the help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 03-27-2014, 11:47 AM
  2. Linking one primary key to another?
    By Kat-ness in forum Database Design
    Replies: 1
    Last Post: 12-04-2012, 04:28 PM
  3. Replies: 6
    Last Post: 05-16-2012, 12:43 PM
  4. Linking tables to show primary key from main table
    By Mark_435 in forum Database Design
    Replies: 5
    Last Post: 01-12-2012, 09:20 PM
  5. Linking Access Table with already imported External Data (Excel)
    By izzarshah in forum Import/Export Data
    Replies: 1
    Last Post: 07-29-2010, 09:40 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums