Front End Connected to Database Back End -- Requirements?

[WWGD]

Hi All,
Say I have a SQL Server ( or more general, if possible) back end, i.e., stored at the server level. Now, I want to create a front-end, say Access fron- end ( again, if possible, more general ). I know one thing I need : to create a physical connection between the two, most likely TCP/IP, maybe OCDB, etc. , to have data processed in the back and sent to the front.
What other type of compatibility " constraints" (general software, hardware, etc.) are there in order to connect the front with the back? For example, how would I set up an Access front to connect with a SQL server back?

 

[fresh_42]

I once had a similar problem. It's been a while since, so maybe the transfer rates nowadays are on an acceptable level. However, the architecture first had been that FE queries were sent to the BE, where the calculations and DB I/O had been performed and then sent back to the FE. At the time some of the FE PCs had been connected by ISDN lines which created horrible answering times. Thus it had been decided to upload many DB entries at system start, such that standard requests could have been performed on the FE. The disadvantage then had been a horrible start-up time. It was an Oracle/C++ BE, a Java FE and XML transfers. And as usual, the query build-ups (and executions), the I/Os and the transfers had been the critical parts.

 

[WWGD]

I once had a similar problem. It's been a while since, so maybe the transfer rates nowadays are on an acceptable level. However, the architecture first had been that FE queries were sent to the BE, where the calculations and DB I/O had been performed and then sent back to the FE. At the time some of the FE PCs had been connected by ISDN lines which created horrible answering times. Thus it had been decided to upload many DB entries at system start, such that standard requests could have been performed on the FE. The disadvantage then had been a horrible start-up time. It was an Oracle/C++ BE, a Java FE and XML transfers. And as usual, the query build-ups (and executions), the I/Os and the transfers had been the critical parts.

Thanks, Fresh, but, if I am given a back end, how do I know what types of front ends will work with it? I happen to know Access can be a FE for SQL Server, but how would I know if a given option for FE would work with a given fixed choice for BE, as, e.g., SQL Server ? What specs do I need for the two to work together EDIT other than being able to connect one with the other across the web (or within the same PC if that is the case)?

 

[fresh_42]

My example was an intranet partly with "abacuses" at the FE. To be on the safe side with an unknown FE, then html and browsers might be the only choice. This still allows XML transfers. Although we had some trouble until we'd found an appropriate parser (and right version). I only know that AJAX pages are a real bad choice. A horror to update.

 

[fresh_42]

But I know a professional IDE that allows a fast development and an all-in-one solution (BE / DB / FE on all platforms). I'm not really convinced from the FE, and it costs a few bucks, but it works and is constantly developed. They have a test version to download ("test" marks included on the pages and valid some weeks or so).

 

[stoomart]

Thanks, Fresh, but, if I am given a back end, how do I know what types of front ends will work with it? I happen to know Access can be a FE for SQL Server, but how would I know if a given option for FE would work with a given fixed choice for BE, as, e.g., SQL Server ? What specs do I need for the two to work together EDIT other than being able to connect one with the other across the web (or within the same PC if that is the case)?

With a simple setup of Access frontend connecting to SQL backend, you should just need to create an ODBC connection (install additional drivers if required) to use as the data source in Access. Other FE clients would typically need an ODBC connection (in Windows), or the applicable driver (like jdbc on *nix); it really just depends on your client OS and what the application supports.

If connecting over the internet, be sure to use a VPN, or at least restrict access to the database to your FE's IP address in your BE's router/firewall.

 

[WWGD]

With a simple setup of Access frontend connecting to SQL backend, you should just need to create an ODBC connection (install additional drivers if required) to use as the data source in Access. Other FE clients would typically need an ODBC connection (in Windows), or the applicable driver (like jdbc on *nix); it really just depends on your client OS and what the application supports.

If connecting over the internet, be sure to use a VPN, or at least restrict access to the database to your FE's IP address in your BE's router/firewall.

Thanks, Stoomart, in a general sense, is it correct that all I need is a connection to allow for the back-and-forth transfer of data and an API to allow the two environments to "gel" , i.e., to reconcile different formats?

 

[QuantumQuest]

Hi All,
Say I have a SQL Server ( or more general, if possible) back end, i.e., stored at the server level. Now, I want to create a front-end, say Access fron- end ( again, if possible, more general ). I know one thing I need : to create a physical connection between the two, most likely TCP/IP, maybe OCDB, etc. , to have data processed in the back and sent to the front.
What other type of compatibility " constraints" (general software, hardware, etc.) are there in order to connect the front with the back?

In order to answer in a general way, you can think of the "blocks" that are needed: a database server (and usually a web / application server "on top" of that - literally between database server and client) in the role of back end, which can be SQL Server, Oracle, MySQL or any other technology for that matter, the protocol(s) that will be used (like HTTP(S), WSDL, SOAP, JSON, REST, XML - RPC etc.) and a client program in the role of front end, that will be built of whatever "coarse" or finer software components you may need / find fit, using whatever programming language(s) you may find fit. Now, the two ends communicate via a language that is neutral i.e can fit / serve multiple platforms and technologies and this is no other than some flavor (or vocabulary if you will) of XML. This language uses a protocol as vehicle, in order to get communicated. In a simple case you can have some HTML to do the job (along with other needed client technologies like JavaScript and CSS) using HTTP(S) but even in this case we still talk about an XML vocabulary for the communicating language. Now, the reason I mentioned the other protocols besides HTTP(S) is that the communication may take place in the form of web services in various forms and in various modes of synchronization. Also, you can have a non - relational database server (i.e. file structure) and communicate with some client (web or not) via the above mentioned protocols and languages. Now the client program, can take any specific form (web app, desktop app, mobile app etc.) as per needs. Hybrid web - mobile apps is the preferred way as HTTP is simple enough to do the job. The hardware can vary according to the application(s) at hand. Servers must be strong enough to be capable to handle requests efficiently (memory - HDD speed - capacity, processor(s) speed) but in practice, for any big enough application / project, arrays of load - balanced servers or server farms are used, potentially spread all over a big geographical region. Client programs need less resources with this varying according to the specific application.

Another interesting thing is that the problem of proprietary technologies / protocols, that posed many difficulties regarding the format of transmitted / received data in the past, is dramatically reduced as all platforms proprietary or not try to be compliant with the open standards.

For example, how would I set up an Access front to connect with a SQL server back?

It's best to start with an empty Access database. You can create all the database objects you need in SQL Server and link them to Access via ODBC connection. This way you can edit / modify your tables and do all sorts of necessary things using Access and the modifications will be stored in SQL Server. Also you can create all the other necessary objects in Access (like forms for instance) in the same way that you would do for local tables.

 

[harborsparrow]

With a simple setup of Access frontend connecting to SQL backend, you should just need to create an ODBC connection (install additional drivers if required) to use as the data source in Access. Other FE clients would typically need an ODBC connection (in Windows), or the applicable driver (like jdbc on *nix); it really just depends on your client OS and what the application supports.

If connecting over the internet, be sure to use a VPN, or at least restrict access to the database to your FE's IP address in your BE's router/firewall.

This used to be true. Versions of Microsoft Office 2010 or earlier along with odbc do work. For reasons I do not understand, Microsoft seems to have dropped support for odbc in recent versions of office.

If you have office 2010 or earlier, you can connect an access database directly to tables or views in sql server. This means you can use the access database as a user interface to view the data that is in sql server. I work in a group that has done this for years, but I am not personally fond of the method.

One reason I don't like doing this is that the versions of sequel in Microsoft Access and sql server are quite different.

 

[stoomart]

This used to be true. Versions of Microsoft Office 2010 or earlier along with odbc do work. For reasons I do not understand, Microsoft seems to have dropped support for odbc in recent versions of office.

ODBC datasources are still supported in Office 2013-2016 as linked tables:

https://support.office.com/en-us/ar...ver-data-0474c16d-a473-4458-9cf7-f369b78d3db8