Solved

Error: Run-time error-217467259 (80004005)’:  (Data source Name Not Found)

Posted on 2004-08-24
28
22,084 Views
Last Modified: 2008-02-26
Hi Every Body
I am using Access in order to develop my Forms and Reports and my database is a SQL Server, I am trying to insert Data from a form that I have made to a Table in my database which is not linked to my Form.
Following are the Codes that I am using.

Private Sub Command75_Click()
  Dim demoPath As String
  Dim demoConn As ADODB.Connection
  Dim r2 As ADODB.Recordset

 Set demoConn = Server.CreateObject("ADODB.Connection")
 demoPath = "DRIVER={SQLServer};" & "SERVER=Bijan;UID=sa;" & "PWD=ba;DATABASE=Northwind"
demoConn.Open demoPath
Set r2 = CurrentDb.OpenRecordset("Table1")
    r2.AddNew
    r2("OrderID") = Me.OrderID
    r2("QoteID") = Me.QoteID
   
    r2.Update
    r2.Close

End Sub
But I get the Following error:
Error: Run-time error-217467259 (80004005)’:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
And the curser gets focused on demoConn.Open demoPath in VB Editor

What is wrong with my Coding?

Theses are the Info from my Mycomputer>Control Panel>Performance and maintenance>Administrative Tools>ODBC>
I am copying the Exact Info:
Microsoft SQL Server ODBC Driver Version 03.81.9030

Data Source Name: Northwind
Data Source Description:
Server: Bijan
Database: Northwind
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: Yes
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No

Microsoft SQL Server ODBC Driver Version 03.81.9030

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!


Best regards
Bijan







0
Comment
Question by:Bijanbijan
  • 12
  • 8
  • 4
  • +2
28 Comments
 
LVL 7

Expert Comment

by:suramsureshbabu
ID: 11889615
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnString  As String
Dim iCtr As Integer

'MAKE SURE YOU HAVE LATEST VERSION OF
'OLE DB PROVIDERS, WHICH YOU CAN GET AT
'http://www.microsoft.com/data

'BE SURE TO INCLUDE A REFERENCE TO MICROSOFT
'ACTIVE X DATA OBJECTS IN YOUR PROJECT

' IF SQL SERVER USE SOMETHING LIKE THIS
'Data Source = Server Name
'Initial Catalog = Database
'Use your own user names and password

sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=mypassword;Initial Catalog=MyDatabase;Data Source = MySQLServer;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"

'IN ACCESS USE SOMETHING LIKE THIS:
'Change Data Source to full path of database
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDatabase.mdb"


conn.Open sConnString
Set cmd.ActiveConnection = conn

'REPLACE MYTABLE WITH YOUR OWN TABLE
cmd.CommandText = "SELECT * FROM MYTABLE"
cmd.CommandType = adCmdText
Set rs = cmd.execute

Do While Not rs.EOF
   For iCtr = 0 To rs.fields.Count - 1
      'OutPuts Name and Value of each field
      Debug.Print rs.Fields(iCtr).Name & ": " & _
         rs.Fields(iCtr).Value
    Next
rs.MoveNext
Loop
Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11890349
"Use Integrated Security: Yes"

"demoPath = "DRIVER={SQLServer};" & "SERVER=Bijan;UID=sa;" & "PWD=ba;DATABASE=Northwind""

Your serveris not set up to use SQL Acounts.    Change this in enterprise manager or change your connection string to

demoPath = "DRIVER={SQLServer};" & "SERVER=Bijan;Integrated Security=SSPI;DATABASE=Northwind"
0
 
LVL 1

Expert Comment

by:amulya_333
ID: 11899903
You need to have a dsn.Dont you? create a dsn and in your connection string say" DSN=your DSN name" along with other parameters in "demoPath = "DRIVER={SQLServer};" & "SERVER=Bijan;UID=sa;" & "PWD=ba;DATABASE=Northwind""
 and then try to connect. I donno about access. How ever With my knowledge of SQL and VB this is what I think.
Try and let me know in case you need any clarifications.
0
 

Author Comment

by:Bijanbijan
ID: 11899981
Hi ShogunWade
Thank you for your reply.
I changed the coding as you suggested and I added the following Line:

demoPath = "DRIVER={SQLServer};" & "SERVER=Bijan;Integrated Security=SSPI;DATABASE=Northwind"
And I am getting this Error:
Runtime error’-2147217887 (80040e21)’:
Multiple-stepOLE DB Operation generated errors. Checkeach OLE DB status value, If available, No work was done.
And the curser gets focused in VB Editor on democonn.open demopath

Best regards
biajn
0
 

Author Comment

by:Bijanbijan
ID: 11899984
Dear suramsureshbabu
I made the following coding from your Codes  But I am still getting the Exact same Error, Please Take a look at my Codes, and let me know if I am doing O.K.
(I am not particularly sure about coding of Naming the Fields nad their values)
Please be noted that I am very new at SQL server, and SQL in generall.

Private Sub Command75_Click()
 Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnString  As String
Dim iCtr As Integer

'MAKE SURE YOU HAVE LATEST VERSION OF
'OLE DB PROVIDERS, WHICH YOU CAN GET AT
'http://www.microsoft.com/data

'BE SURE TO INCLUDE A REFERENCE TO MICROSOFT
'ACTIVE X DATA OBJECTS IN YOUR PROJECT

' IF SQL SERVER USE SOMETHING LIKE THIS
'Data Source = Server Name
'Initial Catalog = Database
'Use your own user names and password

sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=ba;Initial Catalog=Northwind;Data Source = Bijan;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"

'IN ACCESS USE SOMETHING LIKE THIS:
'Change Data Source to full path of database
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb"


conn.Open sConnString
Set cmd.ActiveConnection = conn

'REPLACE MYTABLE WITH YOUR OWN TABLE
cmd.CommandText = "SELECT * FROM Table1"
cmd.CommandType = adCmdText
Set rs = cmd.Execute

Do While Not rs.EOF
   For iCtr = 0 To rs.Fields.Count - 1
      'OutPuts Name and Value of each field
      Debug.Print rs.Fields(iCtr).Name & ": " & _
         rs.Fields(iCtr).Value
    Next
rs.MoveNext
Loop
Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

End Sub
0
 

Author Comment

by:Bijanbijan
ID: 11900274
Hi amulya_333
I made the addition as you metioned:
demoPath = "DRIVER={SQLServer};" & "SERVER=Bijan;UID=sa;" & "PWD=ba;DATABASE=Northwind" & "DSN=Northwind"

But I am getting the exact same Error.
Could be because my DNS and database are the same Name “Norhwind”?

Best Regards
Bijan
0
 
LVL 1

Expert Comment

by:amulya_333
ID: 11900622
Did you test connection for DSN? Check DSN Configuration. Change the name of DSN.
0
 
LVL 1

Expert Comment

by:amulya_333
ID: 11900627
I fit does not work with that give the Provider' sname also like provider={Provider name} in the connection string.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11901187
SQL wont authenticate SA when it is using windows authentication only.

Try this connection string:

"Data Source=Bijan;Initial Catalog=Northwind;Connect Timeout=120;Integrated Security=SSPI"
0
 

Author Comment

by:Bijanbijan
ID: 11901278
Hi amulya 333
Followings are my DSN settings:
Microsoft SQL Server ODBC Driver Version 03.81.9030

Data Source Name: Northwind
Data Source Description:
Server: Bijan
Database: Northwind
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: Yes
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No


Microsoft SQL Server ODBC Driver Version 03.81.9030

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

About the Provider, I do not what is it?
Please explain, let me know how can I find out about it.
If you need any other settings on my computer please let me know.

Best Regards
biajn
0
 

Author Comment

by:Bijanbijan
ID: 11901376
Hi ShogunWade
I used the following codes from your suggestion.

Private Sub Command75_Click()
 Dim demoConn As ADODB.Connection
 Dim r2 As ADODB.Recordset
 Dim demoPath As String

 demoPath = "Data Source=Bijan;Initial Catalog=Northwind;Connect Timeout=120;Integrated Security=SSPI"


 
' Open a connection.
 Set demoConn = New ADODB.Connection
 demoConn.Open demoPath
' Open a recordset based on a table.
 Set r2 = New ADODB.Recordset
 r2.Open "Table1", demoPath, , , adCmdTable

r2.AddNew
  r2("OrderID") = Me.OrderID
  r2("QoteID") = Me.QoteID



 r2.Close
 demoConn.Close
End Sub

But I am getting the folowing Errors again:
Runtime error’-2147217887 (80040e21)’:
Multiple-stepOLE DB Operation generated errors. Checkeach OLE DB status value, If available, No work was done.
And the curser gets focused in VB Editor on democonn.open demopath

Could we check all the necessary settings that I need to have on my SQL server and windows to gether?
Just tell me what Info and settings do you require from My computer?

Best regards
biajn
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11902793
What Edition of SQL to you have ?  (Standard MSDE, Developer,Enterprose, DataCentre)

Is SQL on the same box as client code?
0
 

Author Comment

by:Bijanbijan
ID: 11904875
Hi ShogunWade
I was going to tell you about this that I ssaw your question about it.
I should tell you that my OS is Win XP, and at first I had only SQL server Developer edition Installed on my computer, and now I just changed it to Personal edition, and also I have only client tool installed on my local machine, and I do not have Server + Client tools installed on my computer. Is this going to make any difference?
Please xplain, What do you mean by "Is SQL on the same box as client code?"

Best Regards
Bijan
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 18

Expert Comment

by:ShogunWade
ID: 11904998
So you have SQL Personal Edition on one computer, which you are accessing from a different computer which only has client tools on,  is my understanding correct?
0
 

Author Comment

by:Bijanbijan
ID: 11910072
Hi ShogunWade
No, Every thing is on my computer, I am just trying to develope a Program Using Access and Sql Server as my data base, I have made some forms  using Access and some Tables in my  SQL server Data base which I want enter or insert some Data from a form to a Rable which is not linked to my Form but all in same Data Base, That is all.

Best Regards
Bijan
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11911261
try changing the connection string to:


"Data Source=(local);Initial Catalog=Northwind;Connect Timeout=120;Integrated Security=SSPI"
0
 

Author Comment

by:Bijanbijan
ID: 11912389
Hi ShogunWade
I Revised my codes as follow:
Private Sub Command75_Click()
 Dim demoConn As ADODB.Connection
 Dim r2 As ADODB.Recordset
 Dim demoPath As String

 demoPath = "Data Source=(local);Initial Catalog=Northwind;Connect Timeout=120;Integrated Security=SSPI"

 ' Open a connection.
 Set demoConn = New ADODB.Connection
 demoConn.Open demoPath
' Open a recordset based on a table.
 Set r2 = New ADODB.Recordset
 r2.Open "Table1", demoPath, , , adCmdTable
r2.AddNew
  r2("OrderID") = Me.OrderID
  r2("QoteID") = Me.QoteID
r2.Close
 demoConn.Close
End Sub

But I am still getting the same Error as I mentioned to you.
Another strange thing is I made and added this Table1 in My Northwind database (Sample database of Microsoft) and also I made a form for it using the access wizards only 2 textboxes for entering data, But I just can not add any data from the form (The add Button in Navigation Bar is not activated at all), I have to add the data from the EM>northwind>Table1.

Best Regards
Bijan
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11912819
Its a long time since ive done VB (as opposed to VB.net)  
but,  I think you now have the correct connection string.   I think there is another problem in your code.


Im a but rusty in this area but ....

Here you open a connection

 ' Open a connection.
 Set demoConn = New ADODB.Connection
 demoConn.Open demoPath

but you are not refering to that connection in here:

' Open a recordset based on a table.
 Set r2 = New ADODB.Recordset
 r2.Open "Table1", demoPath, , , adCmdTable


0
 

Author Comment

by:Bijanbijan
ID: 11919550
Hi ShogunWade
I am not quite sure what you are saying, But You did not answered my question on the problem that I mention on not be able to add data from my Table1 form.
On the same Issue, when I want to enter data form that form I get the Following Error:
*Verify that OLE Server is registered correctly.
*Make sure your computer is corrected to the server on which the OLE server application resides.
*Close the OLE server and restart it out side of Microsoft Access. Then try the original operation again from with in Microsoft Access.

As you can see, I think we have OLE server problem among other things.
Could you tell me how can I access the OLE server? I do not where to find it on my computer.

Best regards,
Bijan
0
 

Author Comment

by:Bijanbijan
ID: 11919554
Hi suramsureshbabu
I visited the URL you provided, But Could not Find the “OLE DB Providers”.

Could you be more specific on your URL addressing?

Best Regards
Bijan
0
 

Author Comment

by:Bijanbijan
ID: 11925429
Hi ShogunWade
Following error is fixed, I no longer get this error when I wanted to enter data from my Table1 form to table1 table.
Apparently it was some Indexing problem as soon as I added a key Index for the Table1 with in the EM itself, the problem was solved,
*Verify that OLE Server is registered correctly.
*Make sure your computer is corrected to the server on which the OLE server application resides.
*Close the OLE server and restart it out side of Microsoft Access. Then try the original operation again from with in Microsoft Access.

But I would like to know,
Where is the OLE server on my computer? How can I register it?
Do you know the URL location of the Latest OLE collection or codes?

Finally I am still getting this error:
Runtime error’-2147217887 (80040e21)’:
Multiple-step OLE DB Operation generated errors. Check each OLE DB status value, if available, No work was done. (When I run the following Procedure)

Private Sub Command75_Click()
  Dim demoPath As String
  Dim demoConn As ADODB.Connection
  Dim r2 As ADODB.Recordset

 Set demoConn = Server.CreateObject("ADODB.Connection")
 demoPath = "DRIVER={SQLServer};" & "SERVER=Bijan;Integrated Security=SSPI;DATABASE=Northwind;DSN=Northwind"
demoConn.Open demoPath
Set r2 = CurrentDb.OpenRecordset("Table1")
    r2.AddNew
    r2("OID") = Me.OID
    r2("QID") = Me.QID
   
    r2.Update
    r2.Close

End Sub



But I am trying a whole new Procedure could you tell what is wrong with this Procedure?

Private Sub Command79_Click()
Dim myCn As New ADODB.Connection
Dim myRs As New ADODB.Recordset
myCn.ConnectionString = "DSN=Northwind"
myCn.Open
myRs.Open "SELECT * FROM Table1", myCn, adOpenDynamic

INSERT INTO TABLE (OID, QID) VALUES (OID, QID);

End Sub

Best regards,
Bijan
0
 
LVL 1

Expert Comment

by:amulya_333
ID: 11928825
I feel that this question deserves more points. What do you say?
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11940288
Yuor connection string is too sparse for a start.
0
 

Accepted Solution

by:
Bijanbijan earned 0 total points
ID: 11949220
To whom it may concern.
this question was a contenuation of my Recorset question. Basically the exact same Question. It is answered in Recordset Question.
There for do not reply to it any more.
Best Regards
Bijan
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11950363
Could you please close the question then,
0
 
LVL 10

Expert Comment

by:avidya
ID: 11951033
Hi Bijan,

If you need help closing this question see:
http://www.experts-exchange.com/Databases/MS_Access/help.jsp#hi9

best regards

Avidya
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now