A few years ago I took my employers spreadsheets tracking inventory and RMA data and converted them into a normalized database using access 2007. There were two main tables that were used and a number of tables that held lookup data that constrained the users to only input values that were found in those tables. This was achieved through the lookup wizard. So for a model number on the main table, it had to be in a list of available models that was in another table that only authorized individuals could edit. This saved the headache of people spelling model numbers wrong and aided in efficient searching algorithms.



Fast forward to the present day my employer has subscribed to Office365 and we now have access to Sharepoint. I would like to update this database to something I can move to the Sharepoint site so that our technicians can see priority RMA numbers listed on the main site page. Theoretically I could export the old tables from the existing back end, import them into a web db file and re-link them on the front end file. So I decided that I would create a new accdb file in 2010 as a new web database. Right off the bat the design view is gone and replaced by an extremely limited set of options on the ribbon. I was able to export all of my old tables and re-import them but the relationships between them are gone. Supposedly, through my browsing Google, I have come to understand that relationships are now achieved through the lookup tables. However you have to create a NEW field to make it a lookup field. You cannot convert an existing field to a lookup field. This means I had to create a new field, make it a lookup, link it to the proper field on the related table, and copy the data from the original field to the new field. Then delete the old field.

Once I finished linking everything the way it was on the old database, I saved the file, opened the old front end, and attempted to relink the pages. However none of my relationships were working correctly. Instead of the Model field on the main table linking to the ModelNumber field on the tblModel it was linking to the ID field on tblModel, and it was the same case for all the rest of the linked fields. So my forms were not working correctly, and my custom search engine for the DB was crippled.

Does anyone have any advice or, better yet, resources for converting an access 2007 database to a web compatible Access 2010 database. Specifically I need help with setting up relationships. Any help is appreciated. Thank you.