linked tables between sharepoint and access

Can i link an access database to sharepoint lists and still use Access and it's forms that I created for data entry while the sharepoint list automatically gets updated??

Seems like i have to first export the Access tables into sharepoint lists, and then i have to import the list into a new Access database to create the link where both get updated with new data entry.

The problem is that I have necessary back ends scripts associated with the Access data entry form that does some house keeping during form events.

Can I export the forms from the original Access database into this new Access/Sharepoint linked database and bind them to the appropriate tables import from Sharepoint?

Is this impossible because the scripts are VBA and will only work with an Access database and not an SQL one.

The Sharepoint list created from the Access table is in SQL format. If I import this to Access as a linked table, is this in Access format or SQL?
LVL 9
BobHavertyComhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
I work a lot with SharePoint lists and Access.

I generally create my tables in Access, make sure they work the way I want them to (forms, queries, ...) and then export them to SharePoint.  Make sure that the table contains a primary key field, or the Export To SharePoint wizard will create a new one for you.  Then I delete the original tables from the Access application, and link them from SharePoint back into the Access application.  Once you have done this, you treat the linked table just like any other Access table.  

The major down side of SharePoint lists as your data tables is that SharePoint doesn't understand Referential Integrity, so you have to write your code to ensure that you don't end up with lots of orphaned records.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BobHavertyComhAuthor Commented:
I don't think orphans will be a problem as all data will be entered in Access and merely viewed in Sharepoint

Thanks
Dale FyeOwner, Developing Solutions LLCCommented:
Bob,

Just to make sure you understand.  If you have any one-to-many relationships, or many-to-many relationships, you have the potential of creating orphans.

In Access, when you create the relationships between tables and define the referential integrity criteria, it prevents you from writing a record to the "many" side of a one-to-many relationship until after the "one" side has been created.  Not so in Sharepoint.

Likewise, if you delete a record from the "one" side in Access, and have "Cascading Deletes" checked in the relationship to the "many" table, then all of those records in the "many" table which relate to the record in the "one" table that is being deleted will also be deleted.  Not so in Sharepoint.

Because of this, you have to make sure that when you delete a record from the "one" table, you delete the associated records from the "many" table.

Dale
fatalblitzCommented:
I had a question that sort of branches off of the original poster's question. After the links are created between the Access table and the SharePoint list, is there any way possible to keep an Access --> Access link as well? (Two-way linking so to speak) so that any changes made from an external Access database will reflect to this SharePoint list.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.