What are the first steps to using Access as a front end?

[Banfa]

It came to my attention in another thread that Access is no longer the rather crippled excuse for a relational database that it seemed to be in version 1.

It appears that it may have moved on a little and one of the things it can do is act as a front end(user interface) to another database and this is what I would like to ask about.

Now I have an MS SQL chuntering away in the background with a web-service and a bespoke program reading and writing data in it. However it would be nice to be able to easily access the data posibily with a view to even editing or inserting in some of the tables.

So here are my questions

1. What sort of new file in Access do I want? 'Blank data access page' or 'Project using existing data'? What are the differences between these 2 types of projects?
2. Will an Access project of this nature alter the the MS SQL database, i.e. if a set-up a form in Access is that solely in Access or does it get written back to the MS SQL server somehow?
3. What are the chances of me accidentally doing something wrong and obliterating the data in the MS SQL server database?
4. Is it possible to set up a Report/View that will update automatically as data is written into the MS SQL server database by the other serives?
5. Do you have any other tips for me in this matter?

Cheers
Ben

[nico5038]

Simply use the File/Get externaldata/Link tables
Select ODBC as filetype and navigate to your MS SQL database.
Now select the table(s) and Access will show them as regular (linked) tables.
All normal Access functions can be used, like for "normal" Acces tables.

Only processing (large update/select queries) might be slow.
In such a case read the F1 file for "Pass through queries" to see how to speedup those.

Nic;o)

[ADezii]

It came to my attention in another thread that Access is no longer the rather crippled excuse for a relational database that it seemed to be in version 1.

It appears that it may have moved on a little and one of the things it can do is act as a front end(user interface) to another database and this is what I would like to ask about.

Now I have an MS SQL chuntering away in the background with a web-service and a bespoke program reading and writing data in it. However it would be nice to be able to easily access the data posibily with a view to even editing or inserting in some of the tables.

So here are my questions

1. What sort of new file in Access do I want? 'Blank data access page' or 'Project using existing data'? What are the differences between these 2 types of projects?
2. Will an Access project of this nature alter the the MS SQL database, i.e. if a set-up a form in Access is that solely in Access or does it get written back to the MS SQL server somehow?
3. What are the chances of me accidentally doing something wrong and obliterating the data in the MS SQL server database?
4. Is it possible to set up a Report/View that will update automatically as data is written into the MS SQL server database by the other serives?
5. Do you have any other tips for me in this matter?

Cheers
Ben

If you wish to provide a Client/Server application that provides efficient, native mode access to SQL Server, you may want to create a Microsoft Access Project. This is accomplished through an OLE DB interface. It is very similiar to working in an Access Database in that the process of creating Forms, Reports, Data Access Pages, Macros, and Modules is virtually the same as that used within an Access Database. Once you connect to an SQL Server Database, you can View, Create, Edit, Modify, and Delete Tables, Views, Stored Procedures, User Defined Functions, and Database Diagrams by using the various Project Components. The user interface for working with Objects is different froms its equivalent counterpart in an Access Database, but it is nonetheless user friendly. Many of the Wizards contained within an Access Database are also present in an Access Project. Familiarization with SQL terminology relating to various items such as Data Types (char, varchar, tinyint, smallint, etc.), wildcards (% and _), functions (SUBSTING(), Rand()), etc. are strongly recommended. To create an Access Project is simple:

1. File ==> New ==> Project using existing data

2. Enter a filename .adp extension will automatically be added

3. The Data Link Properties Dialog Box will appear. This Dialog contains the required Parameters for connecting to an SQL Server Database. Fill in the appropriate values then click OK

.

4. Click on Test Connection to verify that you have connected to SQL Server

5. Click Create.

I hope this has answered all or at least most of your questions.

[NeoPa]

1. What sort of new file in Access do I want? 'Blank data access page' or 'Project using existing data'? What are the differences between these 2 types of projects?
2. Will an Access project of this nature alter the the MS SQL database, i.e. if I set-up a form in Access is that solely in Access or does it get written back to the MS SQL server somehow?
3. What are the chances of me accidentally doing something wrong and obliterating the data in the MS SQL server database?
4. Is it possible to set up a Report/View that will update automatically as data is written into the MS SQL server database by the other serives?
5. Do you have any other tips for me in this matter?

Firstly, ADezii's response may very well be the best way for you to go. I don't have a lot of experience of other types of Access databases. I'll try to answer your questions in order though as well as I can, for reference if nothing else.

1. I think you want the latter but I normally use the simple Blank Database option.
   1. Blank data access page is a way of working in ASP. I've not heard too many good things about the implementation.
   2. Project using existing data is described better by ADezii
2. This would be stored in the Access project.
3. This depends on your SQL Server permissions (you can, after all, restrict the data to R/O if you wish), but if you have full Write access then yes, it is possible to scramble your data just as it would be in OSQL (or the 2005 equivalent.)
4. Sort of. You can run a bound Form or Report which reflects the SQL Server data but it will not update automatically once it has been displayed unless you add some code to do this periodically. There is no Trigger type facility that would cause the object to update on data update.
5. Just one for now.
   If you want to work most efficiently with the data, then consider using Pass-Thru queries which allow you to pass the T-SQL straight through to the SQL Server Back-End. It shouldn't be necessary to use the Pass-Thrus for everything, but when you do (If a SELECT query) the results are not easily formatted. To return formatted results in this situation easily, just build a simple Access QueryDef (Saved query) based on the Pass-Thru as a RecordSet.

HTH.

[Banfa]

Sorry about the delayed reply, a number of more urgent things got put on my plate.

Anyway thank you for the help, I expect(actually I know) I will have other questions but I will create further threads for them.

[NeoPa]

Sorry about the delayed reply, a number of more urgent things got put on my plate.

That's not a problem. We can appreciate that, but anyway it's a forum web site and prompt responses are not generally important (We have enough to keep us busy with all the other threads ;))

Anyway thank you for the help, I expect(actually I know) I will have other questions but I will create further threads for them.

Following the site guidelines perfectly :D
Well, since you put them together I suppose that is to be expected.

[MMcCarthy]

Here are a few whitepapers you may find useful. The first is about upsizing form Access to SQL Server but the issues raised are also applicable in the reverse.

http://www.fmsinc.com/Upsize/docs/Ev...plications.pdf

General whitepaper on the use of Access within an organisation.

http://www.fmsinc.com/Upsize/docs/DBEvolution.pdf

Mary