Results 1 to 5 of 5
  1. #1
    xkateman is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    3

    MSAcess linked table SQLServer(2014) "is not a valid name" error in Primary Keys

    Hi all: i am trying to connect to a (networked) third party SQLServer db with multiple PK that produce said error.


    Initially i thought i could 'workaround' with SYNONYM, but i believe that it is (for example) the "PK_dbo.atrule" syntax that is causing the
    "Linked table" routine to fail.
    Frankly i don't know how to use SYNONYM in the LINK TABLES wizard even in tables without the offending PKs.

    any enlightenment would be most appreciated
    - if necessary i would just as soon create a duplicate database (with re-named keys) if i could be sure i could keep it transactionally synchronized.

    many thanks

    sample table

    table: dbo.atrule
    columns: id, id_user, pattern_match, etc.
    Keys: PK_dbo.atrule

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Link the table based on the UNIQUE key not the PRIMARY key

    The UNIQUE key is likely your ID field (On the SQL side in the IDENTITY SPECIFICATION section it should say YES on the UNIQUE KEY field).

  3. #3
    xkateman is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    3
    thanks, rpeare, for your instruction

    the (MSAccess) link table wizard will not complete the linking process - the "is not a valid name" error dis-allows the table link to be created.
    Regarding the column, the Primary key is the [id] column, thus in the Column properties for [id] >>> Identity Specification: YES, and Is Identity: Yes, and Identity Increment: 1 .

    From my limited understanding it seems the format (syntax) of the KEY: PK_dbo.atrule is causing the Link Table Wizard to error. Columns with Keys of the format PK_dbo_atrule do not error out, and the table link completes properly. Thus, the upshot is that less than half of the SQL database tables get linked in my Access application.

    maybe i don't understand your statement - the only way i can figure to "LINK" all my tables is to manually edit the MsysObjects table, with its manifest complications.

    thanks

    Quote Originally Posted by rpeare View Post
    Link the table based on the UNIQUE key not the PRIMARY key

    The UNIQUE key is likely your ID field (On the SQL side in the IDENTITY SPECIFICATION section it should say YES on the UNIQUE KEY field).

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Is there a period (.) in the actual field name on the SQL table?

    If there is (I can't tell from your post) that's your problem. I don't think there are any innate things in Access to handle periods in field names, however you could export the data from SQL and either link to, or import that data, or, if you have the option, change your data structure on the SQL side to accommodate it.

    Another option would be to create a view, in the view just use the table you're interested in but alias all the fields with periods in them

    i.e.

    SELECT [pk.field] as pk_Field FROM MyTable

    Then link the view into your Access database instead of the original table.

  5. #5
    xkateman is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    3

    Exclamation

    Click image for larger version. 

Name:	Key_Link_error.png 
Views:	10 
Size:	6.1 KB 
ID:	24149

    Not the field, but the Key/Index of the primary key - in this case [id], that ACCESS tries to 'LINK TO'.
    - i would have to edit the relevant Stored Procedures if i changed the Key/Index naming, and that would be a headache when the next database upgrade came out.

    I didn't have any idea that a VIEW was as good as a TABLE (better even) for linking, so that definitely is "the ticket"

    - thanks for sticking with me here!!!

    many thanks, again

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

Similar Threads

  1. Replies: 2
    Last Post: 01-20-2016, 12:41 PM
  2. Replies: 4
    Last Post: 05-22-2015, 02:29 AM
  3. Replies: 3
    Last Post: 01-27-2014, 02:45 PM
  4. Replies: 1
    Last Post: 04-30-2012, 10:09 AM
  5. Replies: 8
    Last Post: 11-12-2010, 10:55 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