Export Access tables to SharePoint list

Question

I have built a database using Access which has several junction tables. When I try to export those tables to SharePoint, I get the following message (see below). The data types in all of my junction tables are 'Number' relating to the primary key of it's separate related table (see image below). Do I need to do anything differently to export these to SharePoint or is it not possible?

Answer 1

No, you are close, but not quite there. Under the Index Name column, you should put something like ConsPro on the FIRST line only. Then choose ConsumerID in the Field name and set Unique to Yes. Then leave the Index Name blank on the second line and chose ProviderID for the Fieldname. The other two rows are fine.

Not sure why multiple instances are there.

Answer 2

I exported each one individually. No I did not follow your suggestion as I wasn't exactly sure what you meant. If you mean setting up relationship, I did do that before the export. It seemed that each time I exported a junction table it also exported the related tables, thus creating duplicates if the related tables had already been exported. When I created my front end relationships look like this: Is there something I can reference as to the correct way to export?

Answer 3

Like this?

Answer 4

You could have asked what I meant, I suppose, but then we'd not have had a real learning experience to exploit, so maybe that is to the good....

In SharePoint and in Access, you can create Lookup fields in tables. This is the ONLY time in Access when I would advice creating a Lookup field in a table, by the way. And only to support migrating the data to SharePoint. That's because SharePoint does not otherwise support Referential Integrity between tables. It's a work around.

I showed you an example of a Lookup Field in a SharePoint list. The same thing applies in an Access table. Consider these screen shots from Access.

You create the Lookup using the Lookup Wizard. This by the way is the result of migrating the Access table to SharePoint and then linking it back. SharePoint also adds several metadata fields like "Attachments" and "File Type" which are of no real use in Access.

Once you have used the Lookup Field Wizard to create the field in the Access table, make sure you enforce Referential Integrity on it to ensure Access and SharePoint recognize that property.

Answer 5

Yes!, Just making sure the Index properties for the row with the Index name ConsPro is set to Unique.