MSDE 2000 - Access 2003: Default Instance Works, but Named Instance Doesn''t

Question

MSDE 2000 - Access 2003: Default Instance Works, but Named Instance Doesn''t

Scott H. Chang

SSCommitted

Points: 1794
More actions
September 27, 2005 at 9:37 am

#166160

Hi all,
I have Microsoft SQL Server 2000 Desktop Engine (MSDE 2000 Release A) installed on my Windows XP Pro PC that is on Microsoft NT 4 System in (i) the "default" instance configured to use Windows Authentication Mode and (ii) the "named" instance configured to use Mixed Mode. I tried to use Access 2003 to do (i) and (ii) and I got a mixed result:
(1) The following source code works nicely in the "default" instance:
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
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
///////////////////////////////////////////////
(2)
If I used the "named" instance of MSDE 2000 Release A, the following code statements do not work!!!
Provider=SQLOLEDB;Data Source=myComputerName\access2003;Initial Catalog=NorthwindCS;User Id=sa; Password=<mySQLServerPassword> where INSTANCENAME=access2003 and SAPWD=<mySQLServerPassword> were set by my System Administrator.
Do you have any ideas why the "named" instance does not work on my machine? Please help and advise.
Thanks in advance,
Scott Chang

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WSquared SSCrazy Points: 2393 More actions · Answer 1

It's probably something to do with your connection string, the easiest way to find out what it should be is to create a new Access Project with existing data and connect to a database on the named instance, then you can look at the currentproject.connection in the immediate window

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 2

Initial Catalog=NorwindCS

Norwind? I hope that's just a typo.

Scott H. Chang SSCommitted Points: 1794 More actions · Answer 3

Hi Richard,

It should be "NorthwindCS". I just edited that old post to make a correction.

Thanks,

Scott Chang

***************************************************

P. S.

I am thinking to use the "default" instance of this MSDE 2000 Release A to do the adp solutions, XML in Access 2003, SOAP, etc. (1) Can someone please tell me whether this approach will work? (2) What is the advantage of using the "named" instance of the MSDE 2000 (or MS SQL Server 2000 in general) in the ADO, ASP, XML, SOAP, etc.?

Thanks,

Scott Chang