Results 1 to 7 of 7
  1. #1
    Travb81 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2014
    Posts
    18

    Composite Key in MySQL Backend, gitching in Access Linked Table

    This might not be the correct Forum, it's crosses over a few. So apologies...

    Backend is a MariaDB server (similar to MySQL).
    I have a table with a composite key.

    The MySQL Workbench shows index:
    Key: Primary
    Type: BTREE
    Unique: YES
    Columns: InstallerID, InductTypeID


    The Frond End Access 2016 has a linked table.
    When I view the table in design mode, the only Key is shown against InductTypeID. It doesn't show as a composite key using multiple columns.

    This is causing all sorts of issues with my query results.

    Any ideas how to get Access to accept the composite key?

    Thanks,
    Trav

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So far as I am aware, you cannot create a relationship between a local table and a linked table and indexes are not actually stored in a table but a separate 'entity' anyway. The Access table designer can only design access tables. Relationships and things like composite keys are about how the data is managed (usually composite keys are about preventing duplicates) so providing your linked table is indexed on both fields in the external db that db will handle it. So just handle the situation in a query.

    It is generally better to have a (autonumber) uniqueID in the external db for linking purposes and just create a composite index to prevent duplicates.

    And finally, when you created the linked table, did you identify the primary key and both fields?

    This is causing all sorts of issues with my query results.
    insufficient information to advise, provide examples of the data, what the query is required to do and what the outcomes are

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Trav,
    You can actually force Access to accept your composite primary key using VBA. Add the code to some opening event of you main switchboard or call it from an autoexec macro:

    Code:
     
     On error resume next 
     CurrentDb.Execute "CREATE INDEX PrimaryKeyIdx ON [" & "YOURTABLENAME" & "] (InstallerID, InductTypeID) WITH PRIMARY"
    Cheers,
    Vlad

  4. #4
    Travb81 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Aug 2014
    Posts
    18
    Vlad, you're a legend!
    Had to fiddle around - dropped the old PK first.
    That line of code created the new PK in Access, but it wasn't showing in MariaDB - so had to add it back there too.
    All is good, thanks heaps!

  5. #5
    Travb81 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Aug 2014
    Posts
    18
    Hi Ajax,

    I was using a composite key, so that for every installer, there could only be one of each induction type.
    If I use an autonumber/unique ID as the primary key, is it possible to create another composite key to avoid duplicates? I haven't done a lot with indexes, so didnt know that was possible! Will explore more, thanks for the idea.

    Quote Originally Posted by Ajax View Post
    It is generally better to have a (autonumber) uniqueID in the external db for linking purposes and just create a composite index to prevent duplicates.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Quote Originally Posted by Travb81 View Post
    Vlad, you're a legend!
    Had to fiddle around - dropped the old PK first.
    That line of code created the new PK in Access, but it wasn't showing in MariaDB - so had to add it back there too.
    All is good, thanks heaps!
    Glad to hear it worked. I call those "pseudo" primary keys as they only apply to your linked tables in your Access instance, not the source back-end (I usually try to keep them the same).

    Cheers,
    Vlad

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is it possible to create another composite key to avoid duplicates?
    yes. But be clear about the difference between indexes, relationships and joins. Indexes just do that, they index a table and control duplication, relationships are between two tables on a common field. Unless you set a relationship property such a maintaining referential integrity (for which the one side must be a primary key), there is little point in having them - although in Access the relationship is identified as a join in the query gui which saves you creating a join each time.

    In Access, you would code similarly to Vlads code, but drop 'WITH PRIMARY' (I presume that will work on linked tables as well), or for an Access table in design view you can use the indexes option and use the GUI. Give the index any name you like - and you can have more than two fields if you want.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	9 
Size:	37.6 KB 
ID:	32390

    See this link to this site for lots of SQL coding examples https://www.w3schools.com/sql/sql_create_index.asp

    Note that a composite index will be slower, in part because of size. When you create an index, the field value(s) are stored in an index object with a pointer back to the record. So if you are using a long datatype (8 bits) then your db is storing 8 bytes for the data, 8 bytes for the index and 4 bytes for the pointer - 12 bytes for the index.

    Very simplistically, if you use a composite index of 2 longs then your index is 20 bytes. The way db's work is they read a block of index data from the disk and analyse. Although modern disks are fast this is a mechanical exercise and so comparatively slow. Can't remember how big the block is (maybe 4096 bytes?). Either way, by having an index 60 percent bigger, everything else being equal, the record retrieval will be 60% slower. It won't be apparent for small datasets - in this example, a table with less than 200 records will all be in one block whether you are using 1 field or 2.

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

Similar Threads

  1. Replies: 4
    Last Post: 03-31-2015, 01:20 PM
  2. Replies: 3
    Last Post: 07-30-2014, 05:04 PM
  3. Replies: 3
    Last Post: 05-14-2014, 01:30 PM
  4. Replies: 1
    Last Post: 08-24-2012, 07:11 AM
  5. access split DB to mySQL (backend only)
    By rnbwkat in forum Access
    Replies: 1
    Last Post: 05-31-2010, 07:19 PM

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