Creating ODBC Connection to SQL Server

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • brosenblum@gmail.com
    #1

    Creating ODBC Connection to SQL Server

    Hi,
    I've been using Access 2000 for a while but this is my first time
    trying to connect an Access Front End to a SQL Server back-end. I've
    created a few tables in SQL Server (2005 Standard) in a database file
    I call CustInfo.mdf. I go into into MS Access and create the ODBC
    connection to this file. When I click on the new ODBC connection I
    see a load of tables I can link to, but they are all from the SQL
    Master Database (tables and Views). None of my CustInfo tables are
    there. I think I'm probably doing something fairly stupid, but I
    can't quite put my finger on it. Any thoughts?
    Tags: None
    • Roger
      #2
      Re: Creating ODBC Connection to SQL Server

      On May 14, 1:02 pm, brosenb...@gmai l.com wrote:
      Hi,
      I've been using Access 2000 for a while but this is my first time
      trying to connect an Access Front End to a SQL Server back-end.  I've
      created a few tables in SQL Server (2005 Standard) in a database file
      I call CustInfo.mdf.  I go into into MS Access and create the ODBC
      connection to this file.  When I click on the new ODBC connection I
      see a load of tables I can link to, but they are all from the SQL
      Master Database (tables and Views).  None of my CustInfo tables are
      there.  I think I'm probably doing something fairly stupid, but I
      can't quite put my finger on it.  Any thoughts?
      are you using a DNS, if so, is the dsn set up to point to the correct
      database (default is master)

      if above is ok, in sql server did you grant your sql user select
      permission to the table

        Comment

        • brosenblum@gmail.com
          #3
          Re: Creating ODBC Connection to SQL Server

          I was using DNS. I have figured it out now. I was using File Data
          Source. When I switched to Machine Data Source it lets me select
          which Database. So far I'm just testing this all on one machine. In
          the future I'll have an Access Front end on one end and SQL server on
          a shared network drive. Will I need to install SQL Server in all of
          the machines or just the one with the SQL back.
          Thanks for your help,
          Ben



          On May 14, 3:37 pm, Roger <lesperan...@na tpro.comwrote:
          On May 14, 1:02 pm, brosenb...@gmai l.com wrote:
          >
          Hi,
          I've been using Access 2000 for a while but this is my first time
          trying to connect an Access Front End to a SQL Server back-end.  I've
          created a few tables in SQL Server (2005 Standard) in a database file
          I call CustInfo.mdf.  I go into into MS Access and create the ODBC
          connection to this file.  When I click on the new ODBC connection I
          see a load of tables I can link to, but they are all from the SQL
          Master Database (tables and Views).  None of my CustInfo tables are
          there.  I think I'm probably doing something fairly stupid, but I
          can't quite put my finger on it.  Any thoughts?
          >
          are you using a DNS, if so, is the dsn set up to point to the correct
          database (default is master)
          >
          if above is ok, in sql server did you grant your sql user select
          permission to the table

            Comment

            • Roger
              #4
              Re: Creating ODBC Connection to SQL Server

              On May 14, 2:38 pm, brosenb...@gmai l.com wrote:
              I was using DNS.  I have figured it out now.  I was using File Data
              Source.  When I switched to Machine Data Source it lets me select
              which Database.  So far I'm just testing this all on one machine.  In
              the future I'll have an Access Front end on one end and SQL server on
              a shared network drive.  Will I need to install SQL Server in all of
              the machines or just the one with the SQL back.
              Thanks for your help,
              Ben
              >
              On May 14, 3:37 pm, Roger <lesperan...@na tpro.comwrote:
              >
              >
              >
              On May 14, 1:02 pm, brosenb...@gmai l.com wrote:
              >
              Hi,
              I've been using Access 2000 for a while but this is my first time
              trying to connect an Access Front End to a SQL Server back-end.  I've
              created a few tables in SQL Server (2005 Standard) in a database file
              I call CustInfo.mdf.  I go into into MS Access and create the ODBC
              connection to this file.  When I click on the new ODBC connection I
              see a load of tables I can link to, but they are all from the SQL
              Master Database (tables and Views).  None of my CustInfo tables are
              there.  I think I'm probably doing something fairly stupid, but I
              can't quite put my finger on it.  Any thoughts?
              >
              are you using a DNS, if so, is the dsn set up to point to the correct
              database (default is master)
              >
              if above is ok, in sql server did you grant your sql user select
              permission to the table- Hide quoted text -
              >
              - Show quoted text -
              I'm assuming you mean "...and SQL server on a different server..."

              no you don't need sql server installed on all machines that will use
              the front end
              you just need to create the DSN ODBC data source on all such machines
              when you create the DSN, part of the information is the name or ip
              address of the machine running sql server

                Comment

                Previous template Next
                Working...