?
Solved

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

Posted on 2008-11-03
7
Medium Priority
?
299 Views
Last Modified: 2012-05-05
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?

0
Comment
Question by:msprygada
  • 4
  • 3
7 Comments
 
LVL 7

Expert Comment

by:vincem1099
ID: 22871629
Are you leaving the SQL username argument blank or are you using SQL authentication?
0
 
LVL 7

Expert Comment

by:vincem1099
ID: 22871646
I suppose you could create a general use SQL credentials that are in your code for the SQLloginaccount and sqlloginpassword
0
 

Author Comment

by:msprygada
ID: 22871776
No, SQL user name is filled in and watching the code, it drops into the SQL authentication if statement.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Expert Comment

by:vincem1099
ID: 22871815
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
 

Author Comment

by:msprygada
ID: 22876317
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
 

Author Comment

by:msprygada
ID: 22876337
Sorry, please disregard the last two sentences. Forgot to delete them.
0
 
LVL 7

Accepted Solution

by:
vincem1099 earned 500 total points
ID: 22878080
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question