Access Frontend to SQL Server Backend 2

[SQLBill]

Background: I am a SQL Server 2000 DBA and have recently taken a class on MS Access. I have been assigned to create a database (which I have done). However, I need to create forms to allow my users to input the data into the database and reports to allow them to retrieve the data. I'm limited to the front-end I can use and would prefer to use MS Access (it's already available on all our systems).

Now my problem. From what I read in the Access help, Forms are used on Access databases and Data Access Pages are used for external databases (such as my SQL Server database). Is that correct? Or is there a way that I can use Access Forms to input data to my SQL Server database? One issue is that the Data Access Page 'tools' are not installed as part of our Access install and I would have to get permission or a waiver to get it installed on all the computers.

-SQLBill

 

[FancyPrairie]

All you have to do is link to the SQL tables via ODBC (File|Get External Data|Link Tables. When you create your DSN, use the File DSN rather than User or System. And use a share name to reference the File DSN rather than hardcoding a drive letter. And make sure your File DSN resides on a server that everyone has access to.

You could also do it by create and Access Project. I believe access to the data would be a little quicker, but, I think you loose some functionality with your forms and reports.

 

[Terpsfan]

I think your best bet is to create an Access Project, which enables you to create an Access front end to a SQL Server backend.

You need Access 2000 or higher.

Open Access
File --> New--> Project (Existing Data)

As long as you have your DSN defined with a connection to the database, you'll see the tables and stored procedures in your Access Project. You can then base your forms and reports off of the backend tables/stored procedures.

As a reference, a good book is the Access 2000 Developers Guide to SQL Server.

 

[SQLBill]

Thank you both for your help. I do have Access 2000, so I will give Access Project a try and see if it will provide what I need. If it doesn't work for me, I'll try FancyPrairie's other option.


-SQLBill