DSN-Less connection still needs user to have SQL access to run Access app????

I have an Access 2007 application that uses SQL as the back end. I created a DSN-Less connection using these instructions from MS (http://support.microsoft.com/kb/892490). I have this app using SQL login connection string so all the users would have to do us run the application and I would not have to give them access to the database to run the app. But the only way to get this to work was to grant them access to the database otherwise they would get an cannot access the database error.

I am putting in all the parameters so the code uses the SQL login

AttachDSNLessTable("LocalTableName", "RemoteTableName", "ServerName", "DatabaseName", "SQLLoginAccount", "SQLLoginPassword")

I have stepped thru the code and it is running the correct code.

Should I be able to have the users connect to this database without adding them to the SQL security?

msprygadaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vincem1099Commented:
Are you leaving the SQL username argument blank or are you using SQL authentication?
0
vincem1099Commented:
I suppose you could create a general use SQL credentials that are in your code for the SQLloginaccount and sqlloginpassword
0
msprygadaAuthor Commented:
No, SQL user name is filled in and watching the code, it drops into the SQL authentication if statement.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

vincem1099Commented:
Does the SQL name you are using in your code exist on the SQL server?  The credentials passed from the code must exist on the SQL server.  On the other hand if you leave the SQL username blank integrated credentials from the user's domain login will be passed tot he SQL server.  Either way the SQL server needs to know what can be accessed using the credentials.
0
msprygadaAuthor Commented:
OK, more digging is finding strange stuff. I delete the linked table and delete the table from datasource of the form. I run the app and it steps thru the code to use the sql login and the table appears under the all tables view. If I hold the cursor over that table I see (ODBC;DRIVER=SQL Server;SERVER=MYSQLSERVERNAME;UID=msprygada;APP=2007 Microsoft Office System;DATABASE=MyDatabaseName;Trusted_Connection=Yes;TABLE=MyTableName). This looks like it is using my credentials to make this link.

Let me ask this, this should work for the user without having to add thier AD account into the SQL security correct? Or does this only eliminate the need for DSN?

The reason I ask is this code looks like it creates the table on the fly. So I start out with the form with no table. I open the form and the linked table gets created. I can open it and view the table contents. No if I try to open the form, the form will have #Name? instead of data. If I go to the form Record source and select the table, the form works. So how are you supposed to add data fields to the form when the table is linked at the time it is opened because the code to create the DSN-Less link runs "On Open" of the form? This is like the chicken and the egg thing. There must be something else I am missing here. So far what I see that this just eliminates the DSN but you still have to provide SQL security for the users AD account to access the database unless like I stated before, I am missing something or I am doing something wrong?

Thanks in advance.

 and if I do not have the table listed, the form will not function. So if I do not have a table (this is a one table one form app) for the form, I get a
0
msprygadaAuthor Commented:
Sorry, please disregard the last two sentences. Forgot to delete them.
0
vincem1099Commented:
Are you calling the function providing a valid SQL username and Password(One that you have set up on the SQL server)?  If you do not want to Use AD credentials you need to pass the SQL username and password.

Try calling the attachDSNLess table function from an AutoExec macro when the access database opens so that the table will be present and save the form with  table as the record source.  If you deleted the table and leave the record source on the form the next time you open the database the AutoExec macro will run and create the table so when you open the form the data will be there.

The purpose of the DSN Less connection is to that each machine does not need to have the DSN configured on it in order to use your database.   You will still need some sort of credentials to access the SQL server whether it be AD or SQL.  If you use SQL credentials you could have one generic login that all instances of the database will use.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.