Uploading Access Database to Sharepoint

[chineloogbonna]

Hello,
I have a general question. I've been trying to get familiar with the process of sharing databases with additional people.

Through my research I have found that its possible to publish an Access Database to SharePoint.

I am still figuring it out, however, wondering if once its publish does it allow users to use the database who do not have Access app installed?

Thank you.

 

[theDBguy]

Hi. Access is a desktop application, so all users need some form of Access executable installed on their machine. It could be the full version or a runtime version. Also, the term "publish" to SharePoint could be misleading. It's not the same as using an Access application on the network.

 

[chineloogbonna]

Can it be used in place a LAN sort of speak. If back-end tables are published/uploaded SharePoint, will it allow a user to use the front end from their desktop while back-end runs on SharePoint?

 

[The_Doc_Man]

When asking whether Sharepoint can be used in place of a LAN, the general answer is "Not without some work, and in any case, there are some reliability issues." In this forum, you can do a SEARCH for SharePoint as a topic. Many members here have found Sharepoint to be a hit-or-miss kind of thing.

Sadly, MS is noted for making a suggestion and then finding that their suggestion was, to be blunt about it, a pile of crap. Sharepoint as a back-end host is not a big thing these days. The problem is that often, Sharepoint rides a WAN, not a LAN, and a WAN can lose its connection very quickly and easily. But Access uses SMB protocols that cannot tolerate a broken connection. That pretty much limits what you can do right there.

 

[theDBguy]

Can it be used in place a LAN sort of speak. If back-end tables are published/uploaded SharePoint, will it allow a user to use the front end from their desktop while back-end runs on SharePoint?

Hi. Yes, sort of. SharePoint can be the BE, but the data will be stored in Lists (not Access tables in Document Libraries). Access can connect to different data hosts like SQL Server and others, and SharePoint is just one of them.

 

[chineloogbonna]

Thank you for that explanation. I work for a nonprofit organization and funds are scarce to say the least. We have to purchase a LAN line so I've been trying to figure out a way to share a database without placing it on the Wide World Web.

Any suggestions?

Thanks

 

[The_Doc_Man]

Having anything of Access "truly" on the web will not work. This has to do with the protocols that you must use for Access to access its data. Connections normally have to be made using SMB (Server Message Block), which is a protocol that includes sequence numbering of successive messages and which is sensitive to port numbers. IF at any point you lose this connection, it is usually broken permanently. That break in the connection then leads to a problem with potential corruption of database files.

The world wide web uses protocols such as HTTP (and the HTTPS variant) and FTP plus its secured variant. Neither of these is suitable for Access to get to its back-end data. You COULD in theory establish a VPN end-to-end if both the client and host could support such a thing. SMB can "ride" a VPN transparently. However, over a WAN-class line, that would be slow and not immune to data loss due to dropped connections. There are serious risks involved here.

To be honest, if you have to use WAN technology, you will have limited options. The only one that I see recommended by anyone here is the use of something similar to CITRIX as a way to log in to a session on the host and run the Access app on the host. Look to articles written by our member Pat Hartman. She has a lot of experience in this matter and might be able to advise you.

 

[theDBguy]

Thank you for that explanation. I work for a nonprofit organization and funds are scarce to say the least. We have to purchase a LAN line so I've been trying to figure out a way to share a database without placing it on the Wide World Web.

Any suggestions?

Thanks

Hi. I'm not sure if this was directed at me or to Doc. Where are the people you are trying to "share" the database located? Are they all in the same building?

 

[chineloogbonna]

Both of you really...I am still learning about back end networks. This stuff still sounds foreign to me.

Its an office of about 25 people. The building is old so there prob about 2-3 hard lines that work, then everyone else is on wifi.

It is a wifi business internet service plan so there could be something set up with them already? But not sure what I'd ask?

Thanks

 

[theDBguy]

Both of you really...I am still learning about back end networks. This stuff still sounds foreign to me.

Its an office of about 25 people. The building is old so there prob about 2-3 hard lines that work, then everyone else is on wifi.

It is a wifi business internet service plan so there could be something set up with them already? But not sure what I'd ask?

Thanks

Well, whatever you do, try to avoid using WiFi with Access, especially if your database is critical to your business. As Doc mentioned earlier, Access needs consistent connection with the data store, so using interruptible wireless connections could be disastrous in that scenario.

 

[sonic8]

The problem is that often, Sharepoint rides a WAN, not a LAN, and a WAN can lose its connection very quickly and easily. But Access uses SMB protocols that cannot tolerate a broken connection.

Maybe just a misunderstanding, but for clarification: When using Sharepoint-Lists as linked tables Access will connect to them via HTTP(S) and not SMB. This connection should be pretty resilient against any interruptions.

 

[The_Doc_Man]

Sonic8, from the articles I've seen, that type of connection is not being played up as much because it still isn't very reliable. And my comments about WANs vs. LANs are not incompatible with the difference. If you had a reliable hard-wired LAN, you would use SMB. If you had a WAN, as you pointed out, other protocols would be more likely, and you named one.

But I agree with theDBguy that wi-fi connections are examples of corruption waiting to strike hard.

 

[chineloogbonna]

I will def aim for the hardwired option. We don't have the man power to deal with corruption.

Thanks to everyone who chimed in, really appreciate your knowledge.

 

[theDBguy]

I will def aim for the hardwired option. We don't have the man power to deal with corruption.

Thanks to everyone who chimed in, really appreciate your knowledge.

Hi. Good luck!

 

[The_Doc_Man]

Hardwired...

Not the cheapest option, but CERTAINLY the most reliable one.

 

[sonic8]

If you had a reliable hard-wired LAN, you would use SMB. If you had a WAN, as you pointed out, other protocols would be more likely, and you named one.

But I agree with theDBguy that wi-fi connections are examples of corruption waiting to strike hard.

I get the impression we are talking about different things here.


I'm not talking about putting the backend database file into a SharePoint document library! Then only option to share this backend data would be via OneDrive-Business, which will corrupt the database.


I'm talking about using Sharepoint Lists as backend tables. The data will be synced via HTTP with the local Access frontend file. All further operations on the data will then happen locally only. I don't have enough own experience on the probability of corruption, but it is a completely different approach than directly accessing the backend file over a WAN connection.

 

[The_Doc_Man]

Ah, I see your point, Sonic8. And that better explains the articles I read about delays in synchronization leading to temporarily divergent data between the master list and local replicas.

 

[Pat Hartman]

Let me clarify how to use SharePoint. You would convert ALL the tables to SharePoint lists. Then each user has his own personal copy of the FE which LINKS to the SharePoint lists as if they were tables in any other ODBC database. HOWEVER, SharePoint is not robust and if you have more than a few thousand records in your tables, you will be unhappy with performance or actually reach hard limits on row count. No one who has any other options would willingly use SharePoint lists as their BE database. You CANNOT just put the Access database in a SharePoint shared folder and expect to share it. Simultaneous users will overlay each other's data and you reallllllly don't want to have to try to recover from that.

A better solution as long as you have a LAN (wireless is OK in this case) is to convert the BE to SQL Server. Jet/ACE is very flaky over wireless LANs so wired LANS are MUCH safer when the Be is JetACE. With a SQL Server BE, you will still have problems if your connection drops but you are unlikely to corrupt the database or lose data. You will just have to close Access and work your way back to where you were. In no case can Access pick up a lost connection smoothly. You have to shut down and reopen.

Given that you are a non-profit, you might be able to get a cheap license for SQL Server. Or, you might be able to use SQL Server Express which is free but not as feature rich as the full version. But nothing is easy. Converting an old Access app to SQL Server might result in undesirable slowness depending on how the app was designed. The good thing is that this is easy to determine and free (except for your time) to try since you can download and install SQL Server express. So, that is my suggestion. Download the free version and use the upsizing wizard or SSMA (a free conversion tool that you can download from Microsoft) to convert the data. Then just try it and let us know how it works. Maybe you'll get lucky.