UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Frontend, One Table Shared By Access And Website, Access 2010 Web    
 
   
BrigitG
post Feb 17 2019, 02:44 PM
Post#1



Posts: 78
Joined: 7-May 14



I'm sorry if this has been asked before, have been reading posts and not finding the answer.

My client has an existing MS Access multiple table, backend and frontend database in Office 365. It works a treat on their server.

They have an old website which manages all sorts, but in particular events. Users currently have to update the website with the event info, then export in batches and the Access database imports the data and massages it. All works fine, bit of a phaff but it does the job. The events need to be managed in Access because there are so many processes involved. Don't want to change that.

The client is going to have a new website and wanted the Events table to be accessed via the website and updated only the once. All of the other tables will stay in Access (unless MS SQL is the answer then they will all be migrated).

What websites are using data that is shared between the two applications. I have in the past had tables in MySQL but it was oh so very slow.

Things move on, so I wondered what you can recommend. They are a charity without a vast budget. What questions do they need to ask their web developer (one hasn't been appointed as yet).

Look forward to hearing from you.

Go to the top of the page
 
GroverParkGeorge
post Feb 17 2019, 02:51 PM
Post#2


UA Admin
Posts: 36,063
Joined: 20-June 02
From: Newcastle, WA


First, if you decide to move one table to a remote site using either MySQL or SQL Server as the RDBMS, then you probably should move all of them. Otherwise you risk two problems. One is that you can not enforce Referential Integrity between this remote table and any related local tables currently in your accdb. The other is that trying to join tables across two databases like that (one is SQL Server and one local in Access) is generally going to perform poorly. So, if you need to move the tables to a remote site, move the entire set.

Second, hiring a web developer to create a new web interface is going to be the most expensive part of the project, unless you can find someone to work pro bono. So don't be surprised at estimates you receive.

Good luck with the project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RobKoelmans
post Feb 17 2019, 04:59 PM
Post#3



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


Hi Brigit,
It all depends. If you just need to deploy to database application functionality to external users, that can be done very easily with Access Web Apps. You just migrate the database and you have the basic functionality already. We can make the existing access application working again by ms Flow. You can also use web development environment that can access your access backend mdb/accdb by odbc. Nowadays (since access 2010 I think) you can attach high level table macros to OnCreate table events. This way it's totally easy to redevelop most of your VBA code from the front end and thus make it available to webservices. I'd need to have a look at your application and make you an offer to deploy in AWA on SPLA license.
Rob
MetaMicro.nl
Go to the top of the page
 
nvogel
post Feb 18 2019, 01:42 AM
Post#4



Posts: 1,028
Joined: 26-January 14
From: London, UK


I suggest using SQL Server for the database. You can of course share SQL Server databases between many different applications and for a website SQL Server is a far superior option to ACE. If you prefer an open source DBMS then I suggest PostgreSQL.

Access Web Apps is obsolete and no longer recommended or supported by Microsoft.
Go to the top of the page
 
GroverParkGeorge
post Feb 18 2019, 08:43 AM
Post#5


UA Admin
Posts: 36,063
Joined: 20-June 02
From: Newcastle, WA


AWAs are supported in on-premises SharePoint Servers for at least another ~3-7 years. The Office365 option was deprecated.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RobKoelmans
post Feb 18 2019, 09:17 AM
Post#6



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


As George already said, AWA's are still supported in SharePoint 2019 and MS-Office 2019. The datamacros AWA create are quite simple to maintain outside AWA in SQL-Server Management Studio. So - if SharePoint 2019 isn't supported anymore by Microsoft (in 2031), the datamacros can stay on production and even be included in a Visual Studio Database project.
Rob
Go to the top of the page
 
BrigitG
post Feb 21 2019, 02:26 PM
Post#7



Posts: 78
Joined: 7-May 14



Many thanks for taking the time to reply. It seems like the best option, and for future proofing, going the MS SQL root will be the best option.
The front end stays in Access on the office server, the tables are upsized to SQL and the web developer can link to the Event table so it is available on the new website. It just needs to be ‘read only’ on the website.
Go to the top of the page
 
GroverParkGeorge
post Feb 21 2019, 02:50 PM
Post#8


UA Admin
Posts: 36,063
Joined: 20-June 02
From: Newcastle, WA


"The datamacros AWA create are quite simple to maintain outside AWA in SQL-Server Management Studio."

Sometimes we forget that the SQL Azure/SQL Server database for an AWA is just that, a SQL Server database. It supports data macros as triggers, for example.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th November 2019 - 09:25 PM