Using Access to create reports on a SQL database (Access 2002)

Topic

New Reply

We are developing an application using a SQL database and want to use Access for reports and queries. We have linked to the database with an ADP but find the query builder to be different than it is if we use an MDB. Is there a way to link the SQL database to an MDB so we can use expression builder and other features of access? Expression builder and the query format are different when we use an ADP.

When we use an ADP then the relationships between the files are defined but if we use an MDB it appears we need to create all the relationships.

How have other people linked a SQL database to Access. I need some help. thanks.

Reply | Quote

Replies

I link to the SQL tables using the ODBC connection.

Once the tables are linked, I create queries and reports as you normally would in Access.

My clients are only interested in reporting information that is stored in SQL, so this solution works fine.

Reply | Quote

We use ODBC links for most of what we do, as the limitations of ADPs and their forms and reports make it more flexible, even in Access 2002. If however you are looking for the best performance, then you want to take a harder look at ADPs. The query builder for them actually creates either SQL views, or SQL stored procedures, depending on what you need to do. If you use an MDB and there are referential integrity constraints in the SQL Server database, then they get enforced whether or not you define relationships in the front-end MDB.

Reply | Quote