Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: access frontend sql server exrpress backend

    I would like to set up an access frontend to my sqlserverexpress db.
    How do I go about this?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    It depends on how you are going to access your data.

    You could access it all with ADO via code, or you could
    link the tables via ODBC by setting up a DSN.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jun 2006
    Posts
    66
    well how can I do it if the access and sql server are on different machines?

    i want the access frontend (with reports and queries) to pull from sql server express

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Well, if you're going to use Access to write the queries to run reports, you'll need to link tables. Assuming your SQL server database is somewhere that
    can be accessed via a network.

    I haven't set up a DSN in a while but....

    Open up Control Panel
    Goto Data Sources (ODBC)
    Click on the System DSN tab
    Click on ADD
    Select SQL Server from the list

    The wizard will help you along thru the rest

    After you have that done, go into you Access DB.
    Go to File->Get External Data->Link Tables
    Change the "Files Of Type" box to "ODBC Databases"
    The DSN you created above should show up in your "Machine Data Source" tab
    Select that one, and the wizard will guide you to your tables.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jun 2006
    Posts
    66
    the sql server is on a web server -- not the same server -- does this make a difference?

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I've never had to do this with a "web server", so I don't know.
    Maybe somebody else will jump in....
    Sorry
    Inspiration Through Fermentation

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    When you refer to the SQL Server in you connection string or a DSN you will be using the server name (the name of the computer SQL Server Express is running on)

    You can either connect using an MDB and ODBC linked tables or using an ADP. An ADP is an Access Data Project which is more integrated with SQL Server than an MDB is. The queries are really views and Stored Procedures. You can create forms and reports based on the tables/view etc. As long as your versions of SQL Server and Access play nicely together you will be able to create views in Access. I am using SQL Server 2005 and Access 2002 and they are not compatible. I have to create all my views in SQL Server. But when/if I upgrade it will be more seamless.

Posting Permissions

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