Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    Harrisonburg, Virginia, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •