RE: SQL Backend w/Access FE using ODBC and Windows Auth Mode

ADO is the ActiveX Data Object, is a DLL library not used by default in
Access, access uses DAO, Data Access Object. So I just wanted to know if it
was dynamic linking or fixed.

Fixed linking, being you have the tables in the access table linked to SQL
Server via the File based ODBC DSN.

To answer your question, about file odbc, you don't need to copy it on every
computer or centeral location. But the annoying part about the file based
dsn is (to SQL DBA), all connection to database look like they are comming
from same computer LOL. But to user it will work fine ;-).

About checking for users, you can connect to the database right? Do you by
anychance have SQL Enterprise Manager installed? If so, goto the database in
question, then go to security, then users, and see if database user account
exist for each user trying to access the system.

When the users try to connect to the database do they get message "Login
Failed" or "Access Denied". If Login Failed, then you need to check the SQL
Server Logins, if Access Denied then Database Users.

Hope this helps.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005


"Archidrb" wrote:

Ah... I don't know the answer to any of your questions except one - I have
set up a file dsn. However, that does not seem to matter, none of the other
users can access yet, but that is because their names have not been added to
the Users list in the SQL backend. have asked for dbOwner so that I can do
that. Using File, does that "file" need to be saved in a central (networked)
location? Right now, the ODBC tool in the Control Panels sets up the ODBC
onto the local hard drive by default. I will have to test to see if I can
use the tool to set it up anywhere else.

As for the ADO and connection object, they are not options I am familiar
with nor can I find in them in the Access FE or using the Windows (Data
Sources (ODBC)" control panel tool. I used to think I knew a lot about
Access...

I did not get to set up the SQL backend, so unless I can get the manager to
pitch a fit, getting dbOwner may be a real bugger. You may know how those
corporate lines get drawn... I'm just a BA II, not a SQL DBA. Its like
letting the pickup driver into the hybrid meeting or something.

How can I check for and set these other options you have mentioned?



"Mohit K. Gupta" wrote:

In your access database are you using linked tables, are using ADO and
connection object to talk to SQL server?

Your SQL Server does have account for each user trying to access it correct?

What type of ODBC are you using? System or File? If it's System it has to
be created on every users computer. If File you can get away with just one
computer.

If you are using connection string, can you post that here please?
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005


"Archidrb" wrote:

I am attempting to set up a SQL 2005 backend with an Access 2003 front end
using an ODBC connection. We wasted a month on this to learn that none of
the users had the correct "SQL Native Client" driver required to run an ODBC
into SQL 2005. Once we got that straight, we still can't get anyone
connected.

Here's the breakdown: In the ODBC setup at the user end, at about step 5 or
so, the user must select which database within the instance of SQL they are
linking into from a drop-down menu. No other end user can connect into the
SQL BE other than me within the entire pool of users.

We (well, not me) supposedly set up the SQL backend using the Windows
Authentication Mode. The Access front end is already located in a secured
network folder that only those that need access can access and then there is
a password built into the FE. I do not want (or need) another password to
open the connection to the SQL BE.

I suspect the wonderful DBAs at my company need a course in SQL 2005 or
something. Not one of the 2 dozen of them have a clue how to make this work.
I know it is going to be something rediculously simple again (i.e.: the
driver), but I do not have another month to waste on this.

To set up Windows Authentication Mode for multiple users through an ODBC, is
there something besides simply clicking the button/bubble that is required?


.

Quantcast