September 27, 2005 at 9:37 am
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
September 28, 2005 at 6:03 am
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
September 28, 2005 at 6:50 pm
Initial Catalog=NorwindCS
Norwind? I hope that's just a typo.
September 29, 2005 at 6:41 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply