You are correct , for some reason Windows only authentication was selected.
you are half way, could you give me an answer for the rest of the question?
thanks
Main Topics
Browse All Topics
Loading Advertisement...
Question
Access and SQL server
I have 02 different procedures I pasted below. My questions are:
1 - In which circumstances should I use
cn.ConnectionString = "DSN=MyDataSource;Uid=sa;P
OR
cn.ConnectionString = "Provider=sqloledb;" & _
"Data Source=computer1;" & _
"Initial Catalog=pubs;" & _
"User Id=sa;" & _
"Password="
2 - when I use :cn.ConnectionString = "DSN=MyDataSource;Uid=sa;P
It works fine.
But When I use:
cn.ConnectionString = "Provider=sqloledb;" & _
"Data Source=computer1;" & _
"Initial Catalog=pubs;" & _
"User Id=sa;" & _
"Password="
It gives me an error logon failed for a user "sa" Reason not associated with SQL server Connection.
Even when I changed User Id= administrator it gives me the same error:logon failed for a user "administrator" Reason not associated with SQL server Connection.
Though I am logging as a domain administrator to the machine where MSAccess is installed and it is the same account I logged on to the domain controller where sql server is installed.
thanks
Private Sub Command5_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set cn = New ADODB.Connection
cn.ConnectionString = "DSN=MyDataSource;Uid=sa;P
cn.Open
strSQL = "Select * from authors where au_lname = 'Ringer' "
Set rs = cn.Execute(strSQL)
Do While Not rs.EOF
MsgBox (rs!au_lname & " " & rs!au_ID)
rs.MoveNext
Loop
MsgBox ("END OF FILE NOW")
End Sub
**************************
Private Sub Command6_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=sqloledb;" & _
"Data Source=computer1;" & _
"Initial Catalog=pubs;" & _
"User Id=sa;" & _
"Password="
cn.Open
strSQL = "Select * from authors"
Set rs = cn.Execute(strSQL)
MsgBox (rs!au_lname)
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Subscribe now for full access to Experts Exchange and get
Instant Access to this Solution
- Plus...
- 30 Day FREE access, no risk, no obligation
- Collaborate with the world's top tech experts
- Unlimited access to our exclusive solution database
- Never be left without tech help again
Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.
- "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
- "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
- "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.
See what Experts Exchange can do for you.
Got a question?
We've got the answer.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
Need individual assistance?
Our experts are ready to help.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Want to learn from the best?
Read articles from industry experts.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Working on a long term project?
Store your work and research.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
What Makes Experts Exchange Unique?
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Trusted by the world's most respected brands.
Faithfully serving IT professionals since 1996.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Free Tech Articles
-
WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
-
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
-
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
-
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
-
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
-
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...
Cloud Class Webinars
- Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
- Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
- IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
- Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
- Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
- How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...
Join the Community
Give a Little. Get a Lot.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Answers
Do you mean when to use ODBC or native data providers..?
My answer to that is simple. Never use a DSN when connecting to SQL Server. There are a couple of reasons:
1. You can't pass the Database and Server name to your connection string using ODBC. These parameters are important if you plan to make use of a login screen and have a multi-server / database environment.
2. Using ODBC means that you need the DSN setup on EVERY client machine you intend to install your application on.
3. Native data providers will generally return faster results than ODBC.
Hope that answers your question.
Jack
from what I have understood (Please,Correct me if I am wrong)
1- when we use : cn.ConnectionString = "DSN=MyDataSource;Uid=sa;P
that means we have to use the wizard first to set up the datasource name on each machine that has Access on it in order to connect to sql server
2- when we use : cn.ConnectionString = "Provider=sqloledb;" & _
"Data Source=computer1;" & _
"Initial Catalog=pubs;" & _
"User Id=sa;" & _
"Password="
that means we don't have to set up DSN with the wizard, and this works with ActiveX component set as reference in Access.
3- Well I have noticed the usage of
cn.ConnectionString = "Provider=sqloledb;" & _
"Data Source=computer1;" & _
"Initial Catalog=pubs;" & _
"User Id=sa;" & _
"Password="
AND SOMETIMES
con.ConnectionString = "Provider=MSDASQL.1;Persis
(In this case, we still have to set up the DSN through the wizard. Correct ? (as you mentioned ISKPatel) )
what's the difference between the 02 methods?
some explanation tha clears up the confusion will be very much appreciated.
thanks
1. Yes, when you specify a DSN in the connection string it must exist on the client machine.
2. That is also correct (no DSN is required where using the OLEDB provider) but this looks to me like a connection to SQL Server. What is the relation to Access..?
3. Basically you have two different Data Providers in these examples:
* SQLOLEDB is one of the two Microsoft standards for generic data access (the other being ODBC)
* MSDASQL is basically the OLE DB wrapper for ODBC. What this means is that it exposes ODBC functionality using the OLE DB API's. For this to work it includes a translation layer and therefore some extra overheads.
Are you trying to connect to Access and to SQL Server in your application..? If you want a recommendation here it is:
Access: "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\somepath\m
SQL Server: "Provider=SQLOLEDB.1;Data Source=SERVER_NAME;Initial
Using these two connection strings you avoid having to create a DSN for either data source.
Jack
To TDSnet :
I use MSAccess to connect to SQL server 2000. I have noticed in some examples I looked on the internet that there are different ways to connect to SQL server from MSAccess, let me mention some of them to you, and correct me if I am wrong in defining their roles:
1 -We can Install ODBC DSN on the Machine where MSAccess is running, and write a VBA code to query SQL server, we use the following connection.
cn.ConnectionString = "DSN=MyDataSource;Uid=sa;P
*** I don't know if we can query sql server with just a query NO VBA ***, example please.
2- we can connect from MSAccess to SQL server using the following Connection:
cn.ConnectionString = "Provider=sqloledb;" & _
"Data Source=computer1;" & _
"Initial Catalog=pubs;" & _
"User Id=sa;" & _
"Password="
In this case, we don't have to install ODBC in MSAccess Machine. Correct?
3- we can also use the following connection
con.ConnectionString = "Provider=MSDASQL.1;Persis
I guess here we need to install ODBC in MSACCess Machine. If so , what's the difference betwen method 1 and method 3. if no( we don't have to install ODBC in method 3), so please tell me the difference between method 2 and method 3.
thanks
3 Ways to Join
Business Accounts
- Perfect for organizations
- Multiple users
- Save over 36%
- Create a Business Account
Answer for Membership
- Earn free access
- Showcase your knowledge
- No credit card required
- Sign Up to Answer
Loading Advertisement...
by: TDSnetPosted on 2005-05-18 at 18:11:10ID: 14032694
Hi,
What is your authentication mode set to on your SQL server..? In Enterprise Manager, right click on the server name, click on Properties, go to Security tab. Select the radio box "SQL Server and Windows" under Authentication.
Cheers,
Jack