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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
ASKER
Thanks