September 9, 2005 at 9:04 am
Hi all,
I have MSDE 2000 Release A installed in my Windows XP Pro - Microsoft NT 4
LAN System in the following 2 instances:
1) a "default" instance cofigured to use Windows Authentication Mode,
2) a "named" instance cofigured to use Mixed Mode.
I executed the attached VBA code in Access 2003 and I got a Run-Time Error
'-2147467259 (800004005)': [DBNETLIB][ConnectionOpen(Connect())SQL.Server does not exist or access denied.
In the Microsoft MSDN Library:
Multiple Instances of SQL Server
Microsoft® SQL Server™ 2000 supports multiple instances of the SQL Server database engine running concurrently on the same computer. Each instance of the SQL Server database engine has its own set of system and user databases that are not shared between instances. Applications can connect to each SQL Server database engine instance on a computer in much the same way they connect to SQL Server database engines running on different computers.
There are two types of instances of SQL Server:
Default Instances
The default instance of the SQL Server 2000 database engine operates the same way as the database engines in earlier versions of SQL Server. The default instance is identified solely by the name of the computer on which the instance is running, it does not have a separate instance name. When applications specify only the computer name in their requests to connect to SQL Server, the SQL Server client components attempt to connect to the default instance of the database engine on that computer. This preserves compatibility with existing SQL Server applications.
There can only be one default instance on any computer, the default instance can be any version of SQL Server.
Named Instances
All instances of the database engine other than the default instance are identified by an instance name specified during installation of the instance. Applications must provide both the computer name and the instance name of any named instance to which they are attempting to connect. The computer name and instance name are specified in the format computer_name\instance_name.
Please help and advise me how and where I can find the Computer_Name for the Data Source.
Thanks in advance,
SHC
/////////////////////////////////////
Sub OpenMySQLDB()
Dim cnn1 As Connection
Dim rst1 As Recordset
Dim str1 As String
'Create a Connection object after instantiating it,
'this time to a SQL Server database.
Set cnn1 = New ADODB.Connection
str1 = "Provider=SQLOLEDB;Data Source=Computer_Name;" & _
"Initial Catalog=NorthwindCS;User Id=sa;Password=;"
cnn1.Open str1
'Create recordset reference, and set its properties.
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
'Open recordset, and print a test record.
rst1.Open "Customers", cnn1
Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value
'Print a message in MsgBox
If cnn1.State = adStateOpen Then
MsgBox "Connection was established."
End If
'Clean up objects.
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
End Sub
September 9, 2005 at 9:36 am
When U have more than 1 sql server instance installed on a server you must fully qualify your sqlserver name
MyMachine\SqlserverInstanceName
September 9, 2005 at 10:21 am
Hi Ray, Thanks for your response.
I do not understand your response completely. I am not a computer expert and need more technical details of what you mean by "..you must fully qualify your sqlserver name....MyMachine\SqlserverInstanceName." Please help me again.
Many Thanks,
SHC
P. S. In my Control Panel=> Administrator Tools=>Services, I saw the
following 2 names: MSSQLSERVER for the "default" instance
MSSQL$ACCESS2003 for the "named" instance
that were installed by a computer expert who followed the instructions of MSDE 2000 Release A Installation Examples presented in the ReadmeMSDE2000A.htm. Hope this information is useful to you in understanding my "Computer_Name" problem.
September 9, 2005 at 11:07 am
Really Scott, this is way too simple.
Go to the computer that has SQL Server on it. Right click on the My Computer icon. Select Properties. Go to Computer Name tab. Find the computer name there.
-SQLBill
September 9, 2005 at 11:45 am
Hi SQLBill, Thanks for your help.
I found my "Computer_Name" as you instructed. I used it to change the "str1" code statement as
str1 = "Provider=SQLOLEDB;Data Source=NAB-WK-EN39197.nab.ds.usace.army.mil\access2003;" & _
"Initial Catalog=NorthwindCS;User Id=sa;Password=<mypassword>;"
I executed the program and I got the same error message!!!??? I need help again. Any suggestions?
Thanks,
SHC
September 9, 2005 at 1:18 pm
I don't use those type of connections, but try this:
\\[NAB-WK-EN39197].nab.ds.usace.army.mil\access2003
SQL Server doesn't like dashes in names and that might be causing the problem.
-SQLBill
September 9, 2005 at 1:22 pm
Scott,
It's poor security to post your actual login and password. Especially when you post your server and computername. While it's already late, click the edit button on that post and edit out the information.
For example: Password=<mypassword>
-SQLBill
September 9, 2005 at 1:29 pm
Hi SQLBill, Thanks for your 2 responses.
1. I put [NAB-WK-EN39197].nab.ds.usace.army.mil in and executed the program and I got the same error message. More suggests?
2. I will try to change my password.
Thanks again,
Scott Chang
September 9, 2005 at 3:08 pm
Is the machine your computer or a remote server?
If it's your local machine try "Data Source=(local);"
You wrote that the default instance uses windows authentication. If this is the case, to connect to thet instance you should use "Integrated Security=SSPI;" instead of "UserId=<whatever>;password=<Secret>;"
The full connection string for both instances:
Default: Provider=SQLOLEDB;Data Source=(local);Initial Catalog=NorthwindCS;Integrated Security=SSPI;
Access2003: Provider=SQLOLEDB;Data Source=(local)\Access2003;Initial Catalog=NorthwindCS;Integrated Security=SSPI;
or Access2003: Provider=SQLOLEDB;Data Source=(local)\Access2003;Initial Catalog=NorthwindCS;UserId=;Password=<yourNewSQLServerPassword>;
Hope this helps,
Regards
Otto
In the words of the late Robin Cooke: "We would have done better to bring peace to Palestine instead of war to Iraq"
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
September 9, 2005 at 3:14 pm
Another thought: You shouldn't be using the sa account for normal use of SQL/Server. Ideally, only use windows authentication and not mixed mode. - but that's probably a subject for another thread.
Regards
Otto
Q: Who donated the statue of liberty to the United States?
A: The French
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
September 12, 2005 at 7:59 am
Hi Otto, Thanks for your respones.
I have an independent Windows XP Pro that is built on Microsoft NT 4 LAN System - it is local.
I copied the NorthwinCS.SQL and NorthwindCS.adp files from my C:\Program Files\Microsoft office\OFFICE11\SAMPLES and put them in my C:\Access11Files\Chapter01\folder where the "OpenMySQLDB program is.
(1) If I executed the OpenMySQLDB program via
Default: Provider=SQLOLEDB;Data Source=<localComputerName>;
Initial Caltlog=NorthwindCS;Ingetrated Security=SSPI;
I got MsgBox "Connction was established." printed out. But there is no value of Fields(0) or Field(1) of rst1 printed.
(2) If I executed the OpenMySQLDB program via
Access2003: Provider=SQLOLEDB;Data Source=<localComputerName\Access2003>;
Initial Caltlog=NorthwindCS;Ingetrated Security=SSPI;
I got "Run-time error '-2147467259(8004005): Cannot open database requested in login 'NorthwindCS'. Login fails.
(3) If I executed the OpenMySQLDB program via
Access2003: Provider=SQLOLEDB;Data Source=<localComputerName\Access2003>;
Initial Caltlog=NorthwindCS;User Id=sa; Password=<mySQLServerPassword>;
I got "Run-time error '-2147467259(8004005): Cannot open database requested in login 'NorthwindCS'. Login fails.
I think I got the connection to my MDSE 2000 SQL Server sucessfully via either the "default" instance or the "named" instance, but I have a problem in opening the "NorthwindCS" database !!!??? I have no idea how to correct this problem. Please help and advise me in solving this problem.
Many Thanks,
SHC
September 12, 2005 at 8:00 am
Hi Otto, Thanks for your respones.
I have an independent Windows XP Pro that is built on Microsoft NT 4 LAN System - it is local.
I copied the NorthwinCS.SQL and NorthwindCS.adp files from my C:\Program Files\Microsoft office\OFFICE11\SAMPLES and put them in my C:\Access11Files\Chapter01\folder where the "OpenMySQLDB program is.
(1) If I executed the OpenMySQLDB program via
Default: Provider=SQLOLEDB;Data Source=<localComputerName>;
Initial Caltlog=NorthwindCS;Ingetrated Security=SSPI;
I got MsgBox "Connction was established." printed out. But there is no value of Fields(0) or Field(1) of rst1 printed.
(2) If I executed the OpenMySQLDB program via
Access2003: Provider=SQLOLEDB;Data Source=<localComputerName\Access2003>;
Initial Caltlog=NorthwindCS;Ingetrated Security=SSPI;
I got "Run-time error '-2147467259(8004005): Cannot open database requested in login 'NorthwindCS'. Login fails.
(3) If I executed the OpenMySQLDB program via
Access2003: Provider=SQLOLEDB;Data Source=<localComputerName\Access2003>;
Initial Caltlog=NorthwindCS;User Id=sa; Password=<mySQLServerPassword>;
I got "Run-time error '-2147467259(8004005): Cannot open database requested in login 'NorthwindCS'. Login fails.
I think I got the connection to my MDSE 2000 SQL Server sucessfully via either the "default" instance or the "named" instance, but I have a problem in opening the "NorthwindCS" database !!!??? I have no idea how to correct this problem. Please help and advise me in solving this problem.
Many Thanks,
SHC
September 13, 2005 at 4:59 pm
Did you really use 'Initial Caltlog' (sic) in your code or was that an typo when posting? Same for 'Ingetrated Security' (sic).
I tried the following code on my machine:
Option Explicit
Sub testIt()
' Need to set a reference to Microsoft ActiveX Data Objects 2.x Library
Dim oConn As New ADODB.Connection
Dim oRs As ADODB.Recordset
Dim l As Long
'oConn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local)\netsdk;Initial Catalog=Northwind;integrated Security=SSPI"
oConn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=Northwind;integrated Security=SSPI"
Call oConn.Open
Set oRs = oConn.Execute("Select top 10 OrderID, CustomerID from orders")
If Not oRs.EOF() Then
Debug.Print oRs.Fields(0).Name, oRs.Fields(1).Name
Do Until oRs.EOF()
Debug.Print oRs(0).Value, oRs(1).Value
oRs.MoveNext
Loop
End If
Call oRs.Close
Call oConn.Close
Set oRs = Nothing
Set oConn = Nothing
End Sub
Both variants of the connect string worked fine. On my machine, both the default installation and the \NETSDK installation have the Northwind database.
To try it for yourself, open a new module in Excel, and paste in the code. You will have to set a reference to Microsoft ActiveX data Objects 2.x library. I think 2.8 is the latest, but any earlier version should work too. Place the cursor somewhere in the code, and hit F5. You should see the data in the debug window. You may have to beat the macro security over the head to get it to allow you to run macros (Tools -> options -> macro security -> medium should be fine). This is for office 10, things may be different in Office 11.
(Apologies for the newbie's guide, if you already new this stuff, please ignore it)
BTW: error 80004005 is a generic error meaning 'I can't get at your data'
Do you have the Pubs database? Can you connect to that?
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
September 13, 2005 at 5:10 pm
Just tried your original code on my machine. Works fine connecting to the default server and the named instance.
Just a thought: you won't see the output unless you open the debug (Immediate) window (Ctrl-G).
Regards
Otto
Otto Schreibke
The future is a foreign country, they do things differently there.
(Stolen from Arthur C Clarke)
September 27, 2005 at 8:53 am
Hi Otto, Thanks for your responses.
I tried very hard to do the "default" instance and the 2 "named" instances you told me and I got a mixed result: the "default" instance works and the 2 "named" instances do not work!!!
The following source code via the "default" instace works nicely:
Sub OpenMySQLDB()
Dim cnn1 As Connection
Dim rst1 As Recordset
Dim str1 As String
Dim MyVar1
Dim MyVar2
'Create a Connection object after instantiating it,
'this time to a SQL Server database.
Set cnn1 = New ADODB.Connection
str1 = "Provider=SQLOLEDB;Data Source=<myComputerName>;" & _
"Initial Catalog=NorthwindCS;Integrated Security=SSPI;"
cnn1.Open str1
'Create recordset reference, and set its properties.
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
'Open recordset, and print a test record.
rst1.Open "Customers", cnn1
MyVar1 = "****************************** Customers Table ********************************"
Debug.Print MyVar1
MyVar2 = "CustomerID CompanyName ContactName ContactTitle"
Debug.Print MyVar2
Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value, rst1.Fields(2).Value, rst1.Fields(3).Value
'Print a message in MsgBox (Page 188, Julitta Korol's Book)
If cnn1.State = adStateOpen Then
MsgBox "Connection was established."
End If
'Clean up objects.
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
End Sub
///////////////////////////////////////////////
I wonder whether you can tell me why the 2 ways of the "named" instance (you told me) do not work on my machine. Can I have the MSDE 2000 Release A installed on my machine for the "default" instance and the "named" instance at the same time? Can Access 2003 take/use the "named" instance? Please help me and respond again.
Many Thanks,
Scott Chang
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply