Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 10th, 2005, 10:49 PM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Connect to SQL Server using NT Authentication (ADO

Can someone help me with accessing SQL Server from VBA using Connect to SQL Server using NT Authentication (ADO Connect object)?

Thanks

Reply With Quote
  #2 (permalink)  
Old July 11th, 2005, 07:05 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am not sure what you are after? Are you using a DSN? If you want to connect to a SQL server from Access (or any way) you need a connection and a recordset.

Do you need to code to establish the connection, or the entire process?

If you are using a DSN, you tell the DSN if you want to use NT Authentication or SQL Server authentication. I usually use SQL Server (NetWare environment) and pass the credentials in the VBA code.



mmcdonal
Reply With Quote
  #3 (permalink)  
Old July 12th, 2005, 02:21 PM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I would like to establish a connection to a SQL Server using DSN. Since my connection to the SQL Server is done through 'Windows authentication', I need to figure out how to connect this way.

Thanks for your help!

Reply With Quote
  #4 (permalink)  
Old July 13th, 2005, 09:04 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

If you are using Windows authentication, then you need to create the DSN and then select Windows authentication (the default.)

Once you have chosen this option, you need to make sure there is an account on the SQL server for each Windows user. You will also need to set rights. So you will want to create groups, set the group rights, and then make each user a member of the appropriate group.

I use SQL server authentication, and then create single users, and then I make applications and pass the credentials for that user depending on which version of the application the user has. So I make an administrative front end and the user logs in with the SQL server password, and an OLAP front end and pass the OLAP user credentials in the code (since they can only select from views.)

Anywho, once you have your DSN, you need to reference it in your code, like this:

'==========
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim stSQL As String

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "DSN=YourDSNName;"
objRS.CursorLocation = 3
stSQL = "SELECT * FROM tblYourTable WHERE ... etc"

objRS.Open stSQL, objConn, 3, 3
'==========

What specifically are you doing with the data? I have some that is loaded on the main form's On Open event to refresh master tables, and then give the user options to load data for certain reporting periods using buttons etc. Need more help?




mmcdonal
Reply With Quote
  #5 (permalink)  
Old July 14th, 2005, 06:46 PM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks a lot for your valuable advice. I would have one more question. "DSN=YourDSNName;" - is it one of names you see under ODBC Data Administrator\File DSN?

Regards

Reply With Quote
  #6 (permalink)  
Old July 15th, 2005, 06:33 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You need to create a DSN.

In Windows XP:
Go to Control Panel>Administrative Tools> Data Sources (ODBC)
Click the System DSN tab
Click Add
Select the driver (in this case, "SQL Server" at the bottom of the list)
Click Finish
Then enter a Name for your DSN (in my example, YourDSNName
Then using the pull down, select the SQL Server you want to use. If it is not in the list, type the server name in the box
Click Next
Then select whether you want to use Windows NT Authentication, or SQL Server Authentication (See discussion below) (If you use SQL Server authentication, type in the sa username and password to get the connection started.)
Click Next
Select the checkbox to change the default database if it is not the one you want
Click Next
Click Finish
Click Test Data Source to see if it works.
If you get the message "Tests Completed Successfully" then click OK
Click OK to finish.

Now you have your DSN name to use in your code.
You must install this same dsn on any computer that is going to use your application.

The beauty of using a system DSN is that it is a known connection on each machine. It is more robust, or less prone to problems, than a coded connection, but it is a tiny bit slower.

You can code the DSN in your application to make sure everyone who uses it has the connection like this:

'---------
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\YourDSNName\Database" , "YourDatabaseName"
WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\YourDSNName\Drive r", "C:\Windows\System32\\SQLSRV32.dll"
WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\YourDSNName\LastUser" , "DefaultUserName"
WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\YourDSNName\Serve r", "YourSQLServerName"
WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\ODBC Data Sources\YourDSNName", "SQL Server"
'------------

Put this on the On Open event of your main form. This may not work in sandbox mode.
The problem with using this code is that if anyone gets ahold of your application file, they will automatically see your data. And if the user has delete rights, all bets are off.


Windows NT Authentication versus SQL Server Authentication:
With Windows NT Authentication, you can let your Windows network take care of affirming the credentials of your users. The downside is that you have to have an account for each user on your SQL server, and they have to have rights (use group rights to manage this).
With SQL Server authentication, you can create a few users, and then give each user the proper login credentials and even code it in revs of your application. For example, I have one application that is used by a system administrator, and she has all rights to everything through the access front end I have given her. She has to log in to the SQL server. Everyone else in the agency gets a data viewer, and their credentials are coded into the application.

This last one works fine for my application, but if you are giving various users different levels of rights, you may want to track their activities in your log files. In my case, the log files only ever show two users. But only one can delete stuff, so it's not a big deal.

Anywho, HTH





mmcdonal
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
NT Authentication Possible? acdsky Classic ASP Basics 6 February 13th, 2008 07:36 PM
NT Authentication yeewaitu JSP Basics 4 November 18th, 2004 02:14 AM
SQL Server / Login failed for user 'NT AUTHORITY ElPato ASP.NET 1.0 and 1.1 Basics 2 November 14th, 2004 10:03 AM
NT Authentication without Active Directory reyboy SQL Server 2000 0 October 15th, 2003 03:10 AM



All times are GMT -4. The time now is 02:54 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.