Need help on using SQL Server tables linked to Access as a front-end

[DBServices]

I have been working with Access for quite sometime and have a good knowledge of VBA as well. I have never used SQL Server as a back-end but I'm planning on building a database that would definitely require SQL Server. Can anyone please direct me to a step by step on how to link Access as a front-end to SQL Server as a back-end with all the do's and don'ts? I want the SQL Server engine to do all the sorting, modifying, adding, etc... instead of Access due to many records. Any and all help would greatly be appreciated, thank you. Dannie.

 

[FTPMmike]

Access can do it, but I have to ask .. why? SQL is a back-end database program. Access is also a back-end database program, albeit not as powerful, and it does come with a loose "front-end" package. But to use a back-end program (Access) in order to access another back-end program (SQL) seems counter-intuitive. Have you considered accessing the SQL database through another means, such as a web page or winforms interface? (Both require programming knowledge, so I might've answered my own question..! )

 

[Olaf Doschke]

I disagree,

An Access frontend can be totally sufficient to a MSSQL backend. And since MSSQL has no frontend components at all, it's not contradictory to use a bigger backend for a small Access frontend. In fact this wouldnt be the first time this happens. You can server much more users in a company via a MSSQL backend, either connected just programmatically via VBA for Access in code only, or via linking tables. What you surely don't want to use is importing tables, as that imports the data, you're not really using a backend.

Access can be sinlge client, only local dta included in the same access file, you can split frontend and backend db and thereby serve a small number of users or you split the backend into MSSQL (or other ODBC backends) and can serve even more users. Any of these architectures is normal for Access. You may think along the capabilities you have with a web frontend. But Access frontends also can be installed in home offices "only" having VPN access to the company database.

Bye, Olaf.

 

[Darrylles]

Olaf,

Agreed. In fact, is this not a standard progression?

The fact that SQL Server is client / server whereas the 'Access' db engine (JET /a ACE Engine) is not, is the biggest advantage.
Imagine a query being run on the client machine which joins 4 tables.
The client sends the query to the back-end engine sat on a server, which then sends the whole content of all 4 tables to the client, in order for the client to process the query.
So, if there are 100,000 records, and the query should result in 3 records being returned - the client still receives 100,000 records across the network and processes 3 out of them.

With SQL Server, the front-end sends the query to the server, it is processed on the server, and it returns 3 records back to the client.

Now think about 50 users, running that query concurrently: 5,000,000 records of data being sent across the network, rather than the 150 that SQL Server would send.

MS Access is always thought of as a 'database', when it is not.
It is a software development tool which is database focused and happens to come with a free, low-end database attached (Jet / Ace) AND the option to link to a large number of proprietary databases (Oracle etc).
I believe that MS intended this for prototyping work only, hoping that people would naturally progress to SQL Server.

DBServices: there is an 'upsizing wizard' which professes to switch you to SQL Server in a button-click. Depending on the complexity of your application, it will do this (I have done this with a large application), but there will be issues such as data-types which are different (e.g. dates and Boolean) and some SQL key word mismatches.
To maximise the upgrade, you may decide to replace most of your current VBA (data-related) code with Transact-SQL (stored procedures in Oracle) which will greatly improve performance (although, you can chip away at this at will - no need to do it at all).
I would grab the opportunity to upgrade to SQL Server Express - no licensing issues with Express, truly scalable from thereon in, and you're building skills with a 'corporate-level' database.

ATB,

Darrylle