473,320 Members | 2,080 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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?
Jun 27 '08 #1
3 4233
On May 14, 1:02*pm, brosenb...@gmail.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
Jun 27 '08 #2
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...@natpro.comwrote:
On May 14, 1:02*pm, brosenb...@gmail.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
Jun 27 '08 #3
On May 14, 2:38*pm, brosenb...@gmail.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...@natpro.comwrote:
On May 14, 1:02*pm, brosenb...@gmail.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
Jun 27 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: DJJ | last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data from the MySQL tables takes a hell of a long time...
4
by: Andreas Lauffer | last post by:
Can anyone tell me advantages / disadvantages of DataDirect Server Wire ODBC-driver? Any experiences? What about redistribution? Andreas Lauffer, easySoft. GmbH, Germany
5
by: Alec | last post by:
Hi All, I am currently trying to link in Access 97 to a table in a MSSQL 7 server. Initially the link is fine, however, when I close the access database and re-open it from the same network...
9
by: mcbill20 | last post by:
Hello all. I just installed Oracle 10g developer tools on a machine running XP Pro and Office XP. Before this I had just the Oracle 9 client installed. I the previous configuration, I was able to...
5
by: Mike | last post by:
I am writing a .NET application in C# that uses Crystal Reports. I want the crystal reports to grab information from a database no matter where the database is located. To do this, I want to...
8
by: Greg Strong | last post by:
Hello All, The short questions are 1 Do you know how to make DSN connection close in Access to Oracle 10g Express Edition? &/or 2 Do you know how to make a DSN-less pass-through query...
3
by: nark | last post by:
I just downloaded ODBC drivers from the mySQL web site but whenever I try to create a DSN I get a system 1157 error The driver does show up ok in the windows control panel I cant find any...
2
by: 111mike | last post by:
Hello, Here's my problem. I cannot connect to mysql database using odbc string connections or dns. I keep getting a "cannot connect to mysql server localhost." I'm running windows XP Pro and...
2
by: Rico | last post by:
Hello, Can anyone tell me, is there an easy way to 1) Check to see if a DSN already exists and if not, create a system DSN for the back end? Also, is there an easy way to use the SQL Server...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.