msprygada
asked on
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("LocalT
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?
I am putting in all the parameters so the code uses the SQL login
AttachDSNLessTable("LocalT
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?
vincem1099
Are you leaving the SQL username argument blank or are you using SQL authentication?
I suppose you could create a general use SQL credentials that are in your code for the SQLloginaccount and sqlloginpassword
ASKER
No, SQL user name is filled in and watching the code, it drops into the SQL authentication if statement.
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.
ASKER
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=MYSQLSERVERN
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
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
ASKER
Sorry, please disregard the last two sentences. Forgot to delete them.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.