Can I Upload Open Office Db to Sharepoint

Want to combine and raise both Access and SharePoint? To exercise and then, yous can import, link, or move data betwixt them. Importing creates a re-create of the SharePoint list in an Access database. Linking connects to data in another program, so that you lot can view and edit the latest data both in SharePoint and Access. Moving creates lists on the SharePoint site that remain linked to tables in your database and maintains their relationships.

Warning Although you can salve an Access database file to OneDrive or a SharePoint document library, we recommend that you lot avert opening an Access database from these locations. The file may exist downloaded locally for editing and and so uploaded again in one case you save your changes to SharePoint. If more than one person opens the Admission database from SharePoint, multiple copies of the database may get created and some unexpected behaviors may occur. This recommendation applies to all types of Access files including a single database, a split database, and the .accdb, .accdc, .accde, and .accdr file formats. For more information on deploying Admission, see Deploy an Admission application.

What do yous desire to do?

Import a SharePoint listing
Link to a SharePoint list
Move data to SharePoint
Advantages of linking data between Admission and SharePoint
Publish a database by using Access Services

Import a SharePoint list

When you import data, Access creates a tabular array and copies the columns and items from the source list (or view) into that table as fields and records. During the import performance, you tin can specify the lists that you want to copy, and, for each selected list, you can specify whether you want to import the entire listing or but a specific view. At the cease of the import operation, you tin can choose to save the details of the import functioning as a specification. An import specification helps you to repeat the import functioning in the future without having to step through the Import Wizard each time.

Here are mutual reasons for importing a SharePoint list into an Access database:

To permanently motion data, such every bit a contacts listing, to an Access database, because you no longer need the information on your SharePoint site. Y'all tin can import the list into Access, and so delete the listing from the SharePoint site.
Your department or workgroup uses Access, but yous are occasionally pointed to a SharePoint listing for additional data that must be merged into one of your databases.

Complete these steps before you import the list

Locate the SharePoint site that contains the lists that you want to re-create, and make a note of the site address.

A valid site address starts with http:// or https:// followed by the name of the server, and ends with the path to the specific site on the server.
Identify the lists that you want to copy to the database, and then decide whether you want the entire list or just a particular view. Y'all tin can import multiple lists in a single import operation, but you tin can import only one view of each list. If necessary, create a view that contains merely the columns and items that interest you.

Review the columns in the source list or view.

The following table explains some considerations to keep in mind when importing different elements:

Element	Considerations
Columns	Access imports only the first 256 columns, since it supports merely 256 fields in a table. To avoid this trouble, create a view of the listing in SharePoint and add to it just the columns that you want, ensuring that the total number of columns doesn't exceed 256. So employ the ImportSharePointList Macro Action to specify the intended View ID.
Folders	Each binder in the SharePoint list becomes a record in the Access table. Items inside a folder also announced equally records, immediately below the record respective to that folder.
Lookup columns	If a source column looks upwards values in another list, Access imports the display values as function of the field itself. Access doesn't import the looked up table. If yous want to recreate the lookup to another tabular array, see Link to a SharePoint list and Motility information to SharePoint.
Calculated columns	The results in a calculated column are copied to a field whose data type depends on the data blazon of the calculated result. The expression that performs the calculation is non copied.
Attachments	The attachment column of the listing is copied to a field named Attachments.
Multivalued columns	A column of type Selection or Lookup tin contain multiple values. When you import a cavalcade that supports multiple values, Access creates a column that supports multiple values.
Rich text formatting	Columns containing rich text formatting are imported into Access equally Long Text fields. The Text Format property of the Long Text Field is prepare to Rich Text, and the formatting is preserved.
Relationships	Access does non automatically create relationships between related tables at the terminate of an import operation. You must manually create the relationships between the various new and existing tables by using the options on the Relationships tab. To display the Relationships tab, on the Database Tools tab, in the Relationships group, click Relationships.

Identify the database into which you desire to import the lists.

Ensure that you have the necessary permissions to add together data to the database. If you don't want to shop the data in any of your existing databases, create a blank database.
Review the tables in the database.

The import functioning creates a table with the same name as the SharePoint list. If that name is already in use, Access appends "1" to the new table name — for instance, for Contacts1, if Contacts1 is besides already in use, Admission will create Contacts2, and then on.

Import the listing

Open up the Access database in which the imported information will be stored. If you don't want to store the information in whatsoever of your existing databases, create a blank database.
The location of the import/link text magician differs slightly depending upon your version of Access. Choose the steps that match your Admission version:
- If you're using Microsoft 365 or Access 2019, on the External Data tab, in the Import & Link group, click New Data Source > From Online Services > SharePoint List.
- If you're using Access 2016, Access 2013, or Access 2010, on the External Data tab, in the Import & Link grouping, click the More button to driblet downward a list of options and then click SharePoint Listing.
Admission opens the Become External Information – SharePoint Site dialog box.
In the wizard, specify the accost of the source site.
Select the Import the source data into a new tabular array in the electric current database option, and click Side by side.
From the list that the magician displays, select the lists that y'all want to import.

Note You can link to SharePoint libraries, but you can only add together documents in SharePoint.
In the Items to Import column, select the view that you want for each selected list.
The cheque box labeled Import display values instead of IDs for fields that look up values stored in another list controls which information is imported for lookup columns in the selected lists. Exercise ane of the post-obit:
- To import the display values every bit office of the field itself, select the check box. In this case, the field volition not expect upwards another tabular array for values.
- For the destination field to expect upwards another table for values, clear the check box. Doing this volition copy the IDs of the display value rows to the destination field. The IDs are necessary for defining a lookup field in Access.
When importing IDs, you must import the lists that currently supply the values to the lookup columns (unless the destination database already has tables that could act as lookup tables).

The import operation places the IDs in the corresponding field, just it does not set all of the properties necessary to make the field piece of work similar a lookup field. for more than information on how to set the lookup properties of such a field, see Create or delete a lookup field.
Click OK.

Access imports the lists, then displays the status of the functioning on the last folio of the wizard. If you program to repeat the import operation at a later time, you can save the details as an import specification. Access does not overwrite a table in the database as function of an import operation, and y'all cannot append the contents of a listing or a view to an existing table.

What else should I know virtually importing?

For information on how to save the details of your import into a specification that you can reuse later, run across the commodity Save the details of an import or export functioning equally a specification.
For information on how to run saved import specifications, see the article Run a saved import or export operation.
For information on how to schedule specifications to run at specific times, see the commodity Schedule an import or export operation.
For information on how to change a specification name, delete specifications, or update the names of source files in specifications, meet the commodity Manage Data Tasks.

Link to a SharePoint list

When you link to a SharePoint list, Access creates a new table (often referred to as a linked table) that reflects the structure and contents of the source listing. Dissimilar importing, linking creates a link only to the listing, not to whatsoever specific views of the list.

Linking is more powerful than importing in 2 ways:

Calculation and updating data You can make changes to data either by browsing to the SharePoint site or past working in Datasheet or Class view within Access. The changes that you make in one place are reflected in the other. But, if yous desire to brand structural changes, such every bit removing or irresolute a column, you must practise and then past opening the listing on the SharePoint site. You cannot add, delete, or alter the fields in a linked table while working in Access.
Lookup tables When you lot link to a SharePoint list, Access automatically creates linked tables for all lookup lists (unless the lookup lists are already linked to the database). If the lookup lists comprise columns that look upward other lists, those lists are besides included in the linking operation, so that the lookup listing of every linked table has a respective linked table in the database. Access also creates relationships betwixt these linked tables.

Mutual scenarios for linking to a SharePoint listing

Typically, you link to a SharePoint list from an Admission database for these reasons:

Your department or workgroup uses Admission for rich reporting and querying, and uses SharePoint for team collaboration and advice. Individual teams create lists to track various things (such equally contacts and problems), only often this list information must be brought into a database for assemblage and reporting. Linking is the appropriate selection, considering it allows users of both the SharePoint site and the database to add and update information, and to ever view and work with the latest information.
You lot are an Admission user who only recently started using SharePoint. You migrated several of your databases to your team SharePoint site, and virtually of the tables in these databases are linked tables. From now on, instead of creating local tables, you will create SharePoint lists, and and so link to these lists from your databases.
You lot want to continue storing your lists on SharePoint sites, but you also want to work with the well-nigh contempo data inside of Access to run queries and print reports.

Fix to link to a SharePoint listing

Locate the SharePoint site that has the lists to which you desire to link, and brand a note of the site accost.

A valid site address starts with http:// or https:// followed by the name of the server, and ends with the path to the specific site on the server.
Identify the lists to which you lot desire to link. You tin link to multiple lists in a single linking operation, but yous cannot link to surveys, discussions, or a specific view of any list.

Review the columns in the source list. The following table explains some considerations to keep in mind when linking to dissimilar elements.

Element	Considerations
Columns	Access links simply the first 256 columns, since it supports just 256 fields in a table. To avoid this problem, create a view of the list in SharePoint and add to it only the columns that you want, ensuring that the total number of columns doesn't exceed 256. And so use the ImportSharePointList Macro Activeness to create the linked tabular array specifying the intended View ID.
Folders	Each folder in the SharePoint listing appears equally a record in the Admission table. Items inside a binder too announced as records, immediately below the record corresponding to that folder.
Lookup columns	If a source column looks up values in another list and the related list isn't already in the database, Admission automatically creates linked tables for the related lists. Notation Admission also creates a UserInfo table that corresponds to the SharePoint User Information Listing. SharePoint uses this list to expect up user account information, such every bit email, picture, user proper name, for SharePoint columns, such as CreatedBy, ModifiedBy, and Person or Group. This SharePoint User Information List is only visible to site administrators.
Calculated columns	The results in a calculated column are displayed in the corresponding field, but you lot are non able to view or modify the formula in Access.
Attachments	The attachment column of the list is displayed every bit a field named Attachments.
Read-simply columns	The columns that are read-merely in a SharePoint list volition go on to be read-but in Access. In addition, you might not exist able to add, delete, or alter columns in Admission.
Multivalued columns	A column of blazon Choice or Lookup can comprise multiple values. For such columns, the linking operation creates fields that support multiple values. Multivalued lookup columns are created in the linked tabular array if the source column is of blazon Lookup.

Identify the database in which you desire to create the linked tables. Ensure that you have the necessary permissions to add information to the database. If you lot don't want to store the data in any of your existing databases, create a new, blank database.
Review the tables in the database. When you lot link to a SharePoint list, a table having the same name as the source listing will be created. If that proper name is already in employ, Access will append "ane" to the new table name — for example, Contacts1. (If Contacts1 is also already in use, Access will create Contacts2, and then on.) The same rules apply for related lists.

Link to the data

Open the destination database.
The location of the import/link text wizard differs slightly depending upon your version of Access. Choose the steps that match your Access version:
- If you're using Microsoft 365 or Access 2019, on the External Information tab, in the Import & Link grouping, click New Data Source > From Online Services > SharePoint List.
- If you're using Access 2016, Admission 2013, or Access 2010, on the External Information tab, in the Import & Link group, click the More than button to drib downward a listing of options and and then click SharePoint List.
Access opens the Get External Information – SharePoint Site dialog box.
In the wizard, specify the address of the source site.
Select Link to the data source by creating a linked table, and then click Adjacent

The magician displays the lists that are available for linking.
Select the lists that you lot want to link to, and so click OK.

Annotation If some lists are already linked to the current database, the check boxes respective to those lists will be selected. If you want to remove any links, clear the check boxes of the links you lot want to remove.

Access attempts to create linked tables, both for the lists that you selected during this operation and for each of the related lists. Also, Access attempts to refresh the linked tables corresponding to the lists that were selected in the wizard. Access besides creates the relationships between the tables. Unlike an import performance, a linking operation maintains the lookup belongings settings between a lookup field and the related table. Y'all demand not manually set the properties of the lookup field in table Blueprint view.
Review the new linked tables in Datasheet view. Ensure that all of the fields and records are displayed correctly.

Access selects the right data type for each field that corresponds to a source cavalcade. It is important to note that each time you open either a linked table or the source listing, yous would run across the latest data displayed in it. But, structural changes fabricated to a list are not automatically reflected in a linked table. To update a linked table by applying the latest list structure, right-click the table in the Navigation Pane, indicate to More Options, and and so click Refresh List.

Note Admission never overwrites a tabular array in the database as part of a link functioning. Also, you cannot suspend the contents of a SharePoint list to an existing tabular array.

Top of Page

Move data to SharePoint

Moving information to SharePoint is a way to effectively create a backend database, but in this example, the information is contained in SharePoint lists. The front-terminate is even so an Admission database, and you can deploy information technology in a similar fashion every bit a carve up database. When possible, the Consign Tables to SharePoint Wizard moves data to lists that are based on list templates on the SharePoint site, such every bit a Contacts list. If a table tin can't be matched to a list template, the tabular array becomes a custom list on the SharePoint site. Depending on the size of the database, its number of objects, and organization performance, the operation can take some time. If y'all modify your mind during the process, you can click Finish to cancel it.

The wizard creates a backup copy of the database on your computer. In Admission, it creates links to the lists from the tables, and then that it is easy to locate the data on the SharePoint site when you are working in Access. If any issues arise, the Export Tables to SharePoint Sorcerer reports the bug and saves them in the Access database equally a log tabular array that you lot tin use to help troubleshoot.

Tip Consider creating a separate SharePoint site to go on the lists in a self-contained location.

Note In Admission 2007, the Consign Tables to SharePoint Magician is called the Move to SharePoint Site Wizard and information technology doesn't create referential integrity on the SharePoint lists.

Utilise the Export Tables to SharePoint Wizard

On the Database Tools tab, in the Movement Data group, click SharePoint. This option is simply available if your database is saved in the .accdb file format.
Follow the steps in the Export Tables to SharePoint Wizard, including specifying the location of your SharePoint site.

To abolish the procedure, click Stop.
On the last page of the wizard, select the Show Details check box to see more details almost the migration.

This magician folio describes which tables have been linked to lists and provides information about a backup location and the URL for your database. It besides provides a warning if some migration issues were encountered and provides the location of a log table where you tin can see more details about the issues.
Click Cease when the wizard completes its actions.

If the wizard displays a warning, you should review the log table and take any actions necessary to ensure that your data was migrated successfully. For example, sure fields may not be moved or may be converted to another information type that is compatible with a SharePoint list.

Note Admission also creates a UserInfo table that corresponds to the SharePoint User Information List. SharePoint uses this list to look up user account information, such equally email, picture, user proper name, for SharePoint columns, such every bit CreatedBy, ModifiedBy, and Person or Group. This SharePoint User Information List is only visible to site administrators.

Limitations yous may see

When the Export Tables to SharePoint Wizard finishes, you meet a message if Access encountered any issues with the data. Access creates a log table called Move to SharePoint Site Issues and adds the table to the database. The Move to SharePoint Site Issues table is stored in the database but is not published as a list on the SharePoint site.

The following table lists the limitations in how data is migrated, typically when Access and SharePoint don't share the identical feature or, in some cases, don't share a data type. For example, if your Admission table supports referential integrity, information technology is enforced in the listing on the SharePoint site. The information in the post-obit table may help you to decide whether to drift your information, and it may exist helpful if you are reviewing whatsoever issues reported in the Movement to SharePoint Site Issues table.

Type of information or outcome	Issue	Result
COM object information type	SharePoint sites practice not back up the COM Object data type.	Field is not moved.
Binary data blazon	SharePoint sites do not support the Binary data type.	Field is not moved.
Date	SharePoint sites practice not support dates prior to 1900.	Data with dates prior to 1900 is not moved.
New line characters in text fields	SharePoint sites do non back up new line characters in a Single Line of Text field.	Field is converted to a Multiple Lines of Text field or Memo field.
Decimal information type	SharePoint sites do not support the Decimal data type.	The Number field or Double Integer field is used instead.
Replication ID data type	SharePoint sites do not support the Replication ID data type.	A Single Line of Text data type is used instead, depending on the blazon of information.
Default values that are not supported in a SharePoint list	SharePoint sites have default values that are static, such equally text or a number, as well every bit standard dates. Default values from Access that are dynamic are non migrated.	Certain default value properties are non moved.
Unique index fields	SharePoint sites use 1 unique alphabetize field for its ID column in a listing.	Other unique index fields or sets of fields are not moved.
Fields that enumerate automatically (other than the ID field)	SharePoint sites support simply automatic numbering for the field used for the ID column in a list.	Automatic numbering is not practical to columns other than the ID column.
Relationships in which lookups cannot be created	Some relationships are not supported in SharePoint sites, such every bit when the primary fundamental is not related to the ID column or is not an integer.	The human relationship is not moved.

Top of Folio

Advantages of linking information between Admission and SharePoint

Afterward linked SharePoint lists are created, people can work with the lists either on the SharePoint site or in the linked tables in Access. Yous can enter the data past using a table or a form in Access or by editing the list on the SharePoint site. The following guidelines and tips tin can help you lot accept advantage of linked lists between Access and SharePoint and leverage the combination of the ii.

Result Tracking Access has an Issue tracking template that interacts directly with the Issue Tracking list on a SharePoint site. The schemas are the same, and the Access solution tin can be used equally a front stop — for instance, with forms and queries — against data from a SharePoint site.

Retrieve information from the Recycle Bin You can use the Recycle Bin on a SharePoint site to easily view deleted records and recover information that was accidentally deleted.

Quick Launch To view your lists on the SharePoint site, click View All Site Content on the Quick Launch. You may need to refresh the page in your Web browser. To make your lists appear on the Quick Launch on the SharePoint site, you lot tin can change the list settings on the SharePoint site. For more information, see Customize the navigation on your SharePoint site.

Track change history In Access, yous can set the Append property of a Long Text field to Aye and so that Admission retains a history of changes to that field. Similarly in SharePoint, you can view version history of a column. For example, you tin can recover a previous version of a cavalcade or track when the change occurred. If you link to a SharePoint listing with version history enabled, Access creates a Long Text field with the Append property ready to Yeah. If y'all move an Access table that has a Long Text field with its Append property fix to Yep, a SharePoint list with version history is created.

To summarize, Access can view historical changes fabricated in SharePoint, and SharePoint can view historical changes made in Access. For more information, see Create or delete a Long Text field and View the version history of an particular or file in a list or library.

Work offline You can use Access to piece of work offline with data that is linked to SharePoint lists. This tin be helpful if you demand to proceed working when SharePoint is not available. One time SharePoint becomes available, you tin synchronize your changes and hands resolve any conflicts. For more than information, see Piece of work offline with tables that are linked to SharePoint lists.

Subscribe to alerts You can subscribe to alerts and then that you know when changes are made to list items. You lot can receive the alerts from email or text messages (SMS). For more data, run across Create an warning to get notified when a file or folder changes in SharePoint.

Manage Sharepoint list permissions Information technology's a good thought to check SharePoint permissions on linked lists to make sure yous don't inadvertently give admission to confidential or individual data. In Sharepoint, yous tin assign varying levels of permission and you can selectively allow or deny access to certain users. If you demand to limit access to merely a few sensitive items in a database, you can even gear up permissions on specific list items on a SharePoint site. For more information, see Customize permissions for a SharePoint list or library.

Bulk editing Sometimes you demand to brand a lot of changes to list data, such as catching upward on status fields, adding lots of comments, and bringing data upward-to-engagement. This is called bulk-editing, and it is ordinarily more efficient for you lot to use Access to make these changes.

Report distribution If you want to employ Access to create reports from linked list data, you can broadly distribute these reports as PDF files by exporting them to a SharePoint library. This library can effectively act as a report center because PDF files open up in Adobe Acrobat reader for easy reading, paging, and searching. It's always a good idea to time-stamp a report and so people empathise when the data was captured.

Employ SharePoint to create an Access table Y'all can create an Access table based on a SharePoint list. Y'all might find this a convenient and quick mode to create a table with a like purpose and fields to the Contacts, Tasks, Issues, and Events lists. Access volition also create a respective UserInfo table. For more data, see the section "Use a SharePoint site to create a table" in Create a tabular array and add fields.

Microsoft Power Platform There are several ways you can tap the functionality in Microsoft Power Platform past using a linked SharePoint list in Access to present data on many devices:

Ready upwardly Microsoft Power Automate in SharePoint Online, such as approving a request or pushing a notification. For more data, come across Create a flow for a list or library in SharePoint Online.
Use Microsoft PowerApps to customize a list form and build business apps, such as Line of Concern portals and Business organization Intelligence dashboards. For more information, see Create a PowerApp for a list in SharePoint Online.

Mobile apps You can work with Access data linked to SharePoint lists past using a mobile device. You can view listing items and practice light editing. For more information, see SharePoint mobile app for Android and SharePoint mobile app for iOS.

Top of Folio

Publish a database past using Admission Services

Important Access Services 2010 and Access Services 2013 are being removed from the next release of SharePoint. We recommend you do not create new web apps, and migrate your existing apps to an culling platform, such as Microsoft Power Apps.

When you publish a web database, Access Services creates a SharePoint site that contains the database. All of the database objects and data move to SharePoint lists in that site.

When you publish a database, you movement it to the Web. You tin create Web forms and reports that run in a browser window, and tin can too create standard Admission objects (sometimes called "client" objects to distinguish them from Spider web objects). You would need Access installed on your computer to use customer Access objects, but all database objects on SharePoint are shared.

Note:When you have Admission installed in your reckoner, y'all can use the client objects from a Spider web database otherwise you can only use the Web database objects.

Access Services provides a platform for y'all to create databases that you can use on the Web. You pattern and publish a spider web database by using Access 2010 and SharePoint, and people utilise the web database in a Web browser.

Notation:Yous would need Designer permissions on the SharePoint site where yous want to publish the database.

Forms, reports, and UI macros run inside the browser.

Data is stored in SharePoint lists if you lot're using a spider web database: All tables go SharePoint lists, and records get list items and y'all can use SharePoint permissions to control access to your web database.

Queries and data macros run on the server: All SQL processing happens on the server. This helps improve network performance past limiting traffic to effect sets.

Top of Page

Run into Also

Export a table or query to a SharePoint site

Ways to share an Admission desktop database

Deploy an Admission application

gilbertsithered.blogspot.com

Source: https://support.microsoft.com/en-us/office/import-link-or-move-data-to-sharepoint-65bf7b03-74bf-445c-959a-24b7a401ddee