Option Compare Database
Public appAccess As Access.Application

Sub CallSQLDMOSQLServerLogin()
Dim srvname As String
Dim suid As String
Dim pwd As String

' ÉèÖà SQL Server µÄµÇ¼²ÎÊý
srvname = "(local)"
'suid = "sa"
'pwd = ""

' µ÷Óà SQL Server µÇ¼¹ý³Ì
SQLDMOSQLServerLogin srvname, suid, pwd
End Sub

Sub SQLDMOSQLServerLogin(srvname As String, suid As String, pwd As String)
Dim srv1 As SQLDMO.SQLServer

' н¨Ò»¸ö·þÎñÆ÷ʵÀý
Set srv1 = New SQLDMO.SQLServer

' µ÷Óà SQL Server µÇ¼Á¬½Ó·½·¨
srv1.Connect srvname, suid, pwd


' ¶Ï¿ªÁ¬½Ó
srv1.Disconnect
Set srv1 = Nothing
End Sub

Sub CallSQLDMOWindowsLogin()
Dim srvname As String

' ÉèÖà Windows µÇ¼²ÎÊý
srvname = "(local)"

SQLDMOWindowsLogin srvname
End Sub

Sub SQLDMOWindowsLogin(srvname As String)
Dim srv1 As SQLDMO.SQLServer

' н¨Ò»¸ö·þÎñÆ÷ʵÀý
Set srv1 = New SQLDMO.SQLServer

' ÔÚµ÷ÓÃÇ°£¬ÉèÖà LoginSecure ÊôÐÔΪ True
' ʹÓ÷þÎñÃû½øÐÐÁ¬½Ó
srv1.LoginSecure = True
srv1.Connect srvname

' ¶Ï¿ªÁ¬½Ó
srv1.Disconnect
Set srv1 = Nothing
End Sub

Sub CallChangeServerAuthenticationMode()
Dim constAuth As Byte

' ÉèÖà constAuth ²ÎÊýΪ£º
' SQLDMOSecurity_Integrated Ϊ Windows Authentication ģʽ
' SQLDMOSecurity_Mixed Ϊ Mixed Authentication ģʽ

' ÉèÖà constAuth µÄĬÈÏÖµ
constAuth = SQLDMOSecurity_Mixed

' µ÷Óøıä SQL Server Éí·ÝÈÏ֤ģʽµÄ·½·¨
ChangeServerAuthenticationMode constAuth
End Sub

Sub ChangeServerAuthenticationMode(constAuth As Byte)
Dim srv1 As SQLDMO.SQLServer

' Ö¸¶¨Äĸö·þÎñÆ÷£¬Ä¬ÈÏΪ Local (±¾µØ·þÎñÆ÷)
srvname = "(local)"

' ʹÓü¯³É°²È«(Windows)·½Ê½Ð½¨Ò»¸ö SQL Server ¶ÔÏó²¢½øÐÐÁ¬½Ó
Set srv1 = New SQLDMO.SQLServer
srv1.LoginSecure = True
srv1.Connect srvname

' ÉèÖà SecurityMode ÊôÐÔΪ Windows »ò»ìºÏÉí·ÝÑé֤ģʽ
srv1.IntegratedSecurity.SecurityMode = constAuth
srv1.Disconnect

' µ÷Óà Stop ·½·¨Í£Ö¹·þÎñÆ÷£¬Ö±µ½·þÎñÆ÷ÍêÈ«Í£Ö¹
srv1.Stop
Do Until srv1.Status = SQLDMOSvc_Stopped
Loop

' ÖØÐÂÒÔ»ìºÏģʽÆô¶¯·þÎñÆ÷
srv1.Start True, srvname

' ¶Ï¿ªÁ¬½Ó
srv1.Disconnect
Set srv1 = Nothing
End Sub

Sub ToWindowsAuthentication()
Dim srv1 As SQLDMO.SQLServer

' Ö¸¶¨Äĸö·þÎñÆ÷£¬Ä¬ÈÏΪ Local (±¾µØ·þÎñÆ÷)
srvname = "(local)"

' ʹÓü¯³É°²È«(Windows)·½Ê½Ð½¨Ò»¸ö SQL Server ¶ÔÏó²¢½øÐÐÁ¬½Ó
Set srv1 = New SQLDMO.SQLServer
srv1.LoginSecure = True
srv1.Connect srvname

' ÉèÖà SecurityMode ÊôÐÔΪ Windows Éí·ÝÑé֤ģʽ
srv1.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Integrated
srv1.Disconnect

' µ÷Óà Stop ·½·¨Í£Ö¹·þÎñÆ÷£¬Ö±µ½·þÎñÆ÷ÍêÈ«Í£Ö¹
srv1.Stop
Do Until srv1.Status = SQLDMOSvc_Stopped
Loop

' ÖØÐÂÒÔ»ìºÏģʽÆô¶¯·þÎñÆ÷
srv1.Start True, srvname

' ¶Ï¿ªÁ¬½Ó
srv1.Disconnect
Set srv1 = Nothing

End Sub

Sub WindowsToMixedAuthentication()
Dim srv1 As SQLDMO.SQLServer

' Ö¸¶¨Äĸö·þÎñÆ÷£¬Ä¬ÈÏΪ Local (±¾µØ·þÎñÆ÷)
srvname = "(local)"

' ʹÓü¯³É°²È«(Windows)·½Ê½Ð½¨Ò»¸ö SQL Server ¶ÔÏó²¢½øÐÐÁ¬½Ó
Set srv1 = New SQLDMO.SQLServer
srv1.LoginSecure = True
srv1.Connect srvname

' ÉèÖà SecurityMode ÊôÐÔ»ìºÏÉí·ÝÑé֤ģʽ
srv1.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Mixed
srv1.Disconnect

' µ÷Óà Stop ·½·¨Í£Ö¹·þÎñÆ÷£¬Ö±µ½·þÎñÆ÷ÍêÈ«Í£Ö¹
srv1.Stop
Do Until srv1.Status = SQLDMOSvc_Stopped
Loop

' ÖØÐÂÒÔ»ìºÏģʽÆô¶¯·þÎñÆ÷
srv1.Start True, srvname

' ¶Ï¿ªÁ¬½Ó
srv1.Disconnect
Set srv1 = Nothing

End Sub

Sub CallOpenADPWindowsOrSQLServer()
Dim srvname As String
Dim dbname As String
Dim prpath As String
Dim prname As String
Dim suid As String
Dim pwd As String
Dim bolWindowsLogin As Boolean

' ÉèÖôò¿ª ADP ³ÌÐòµÄ²ÎÊý
srvname = "(local)"
dbname = "NorthwindCS" ' ADP Á¬½ÓµÄÊý¾Ý¿â
prpath = "C:\Documents and Settings\Administrator\My Documents\" ' ADP ÎļþËùÔڵĴÅÅÌλÖÃ
prname = "NorthwindCS" ' ADP ÎļþÃû
suid = "msdn5"
pwd = "password"

' ¸Ã²ÎÊýÓÃÓÚ¿ØÖƵ±Ç°Óû§Ê¹Óà Windows µÇ¼
' ´úÌæ SQL Server µÄ suid ºÍ pwd
bolWindowsLogin = False

' ʹÓà Windows »ò SQL Server µÇ¼µ÷Óôò¿ªÃûΪ prname ADP µÄ×Ó³ÌÐò
OpenADPWindowsOrSQLServer srvname, dbname, prpath, prname, suid, pwd, bolWindowsLogin
End Sub

Sub OpenADPWindowsOrSQLServer(srvname As String, dbname As String, _
prpath As String, prname As String, _
suid As String, pwd As String, bolWindowsLogin As Boolean)

Dim bolLeaveOpen As Boolean
Dim strPrFilePath As String
Dim sConnectionString As String

' ÊÇ·ñ±£³ÖÏÖÓдò¿ªµÄ³ÌÐò?
If MsgBox("Ôڸùý³ÌÖÐÊÇ·ñ¹Ø±Õ´ò¿ªµÄ ADP?", vbYesNo) = vbYes Then
bolLeaveOpen = True
End If

' н¨ Access »á»°ÊµÀý (ʹÓà .9 : Access 2000, .10 : Access 2002)
Set appAccess = CreateObject("Access.Application.9")

' ʹÓõǼÃûºÍ¿ÚÁî´ò¿ª ADP ²¢Ê¹Æä¿ÉÊÓ
strPrFilePath = prpath & prname
appAccess.OpenAccessProject strPrFilePath
appAccess.Visible = True

' Ö¸¶¨ ADP ʹÓõÄÐ嵀 Windwos »ò SQL Server µÇ¼·½Ê½
If bolWindowsLogin Then
appAccess.CurrentProject.OpenConnection _
"PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;" & _
"PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=" & _
dbname & ";DATA SOURCE=" & srvname
Else
sConnectionString = "PROVIDER=SQLOLEDB.1;INITIAL CATALOG=" & _
dbname & ";DATA SOURCE=" & srvname
appAccess.CurrentProject.OpenConnection _
sConnectionString, _
suid, pwd
End If

' °´ÉÏÃæÌá³öµÄÒªÇó¹Ø±Õ Access »á»°ÊµÀý
If bolLeaveOpen = False Then
appAccess.CloseCurrentDatabase
Set appAccess = Nothing
End If
End Sub

Sub CallLoginDemo()
Dim srvname As String
Dim suid As String
Dim pwd As String

' ÉèÖõǼ SQL Server µÄ²ÎÊý
srvname = "(local)"
suid = "sa"
pwd = ""

' µ÷Óà SQL Server µÇ¼×Ó³ÌÐò
LoginDemo srvname, suid, pwd
End Sub

Sub LoginDemo(srvname As String, suid As String, pwd As String)
Dim srv1 As SQLDMO.SQLServer
Dim lgn1 As SQLDMO.Login

' н¨Ò»·þÎñÆ÷ʵÀý
Set srv1 = New SQLDMO.SQLServer

' ÒÔ SQL Server ·½Ê½Á¬½Ó SQL Server
srv1.Connect srvname, suid, pwd

' н¨µÇ¼¶ÔÏó
Set lgn1 = New SQLDMO.Login

' Ìí¼ÓĬÈÏ SQL Server µÇ¼ msdn6
lgn1.Name = "UserX"
lgn1.Database = "NorthwindCS"
lgn1.SetPassword "", "password"
srv1.Logins.Add lgn1

'ÖØÐÂн¨µÇ¼¶ÔÏó
Set lgn1 = New SQLDMO.Login

' Ìí¼Ó»ùÓÚ Windows ×é SQL_users µÄµÇ¼¶ÔÏó
lgn1.Name = "MYDESK\UserSQL"
lgn1.Database = "NorthwindCS"
lgn1.Type = SQLDMOLogin_NTGroup
srv1.Logins.Add lgn1

' ÔÚÌí¼ÓµÇ¼ºó£¬ÁгöµÇ¼Áбí
Debug.Print "Ìí¼Ó 2 ¸öеǼºóµÄµÇ¼Áбí:"
For Each lgn1 In srv1.Logins
Debug.Print DecodeLoginType(lgn1.Type), lgn1.Name
Next lgn1

' ɾ³ý¸Õн¨µÄµÇ¼
srv1.Logins.Remove "MYDESK\UserSQL"
srv1.Logins.Remove "UserX"

' ÁгöµÇ¼Áбí
Debug.Print vbCr & "ɾ³ý 2 ¸öеǼºóµÄµÇ¼Áбí:"
For Each lgn1 In srv1.Logins
Debug.Print DecodeLoginType(lgn1.Type), lgn1.Name
Next lgn1

' ¶Ï¿ªÁ¬½Ó
srv1.Disconnect
Set srv1 = Nothing
End Sub

Function DecodeLoginType(lgn_type As Byte) As String

Select Case lgn_type
Case 0
DecodeLoginType = "SQLDMOLogin_NTUser"
Case 1
DecodeLoginType = "SQLDMOLogin_NTGroup"
Case 2
DecodeLoginType = "SQLDMOLogin_Standard"
Case Else
DecodeLoginType = "³¬³ö·¶Î§"
End Select

End Function

Sub MakeLoginWithDatareaderUser()
Dim srv1 As SQLDMO.SQLServer
Dim lgn1 As SQLDMO.Login
Dim usr1 As SQLDMO.User
Dim srvname As String
Dim suid As String
Dim pwd As String
Dim dbname As String
Dim prpath As String
Dim prname As String
Dim bolWindowsLogin As Boolean

' ÉèÖòÎÊý
srvname = "(local)"
suid = "sa"
pwd = ""
dbname = "NorthwindCS"

' н¨·þÎñÆ÷ʵÀý
Set srv1 = New SQLDMO.SQLServer

' ÒÔ SQL Server µÇ¼·½Ê½½øÐÐÁ¬½Ó
srv1.Connect srvname, suid, pwd

' н¨Ò»¸ö SQL Server µÇ¼¶ÔÏó UserX
suid = "UserX"
Set lgn1 = New SQLDMO.Login
lgn1.Name = suid
lgn1.Database = dbname
lgn1.SetPassword "", pwd
srv1.Logins.Add lgn1

' ÔÚÊý¾Ý¿â NorthwindCS ÉÏн¨Ò»¸öÓû§¶ÔÏó UserX£¬²¢ÓëµÇ¼¶ÔÏó UserX Ïà¹ØÁª
Set usr1 = New SQLDMO.User
usr1.Name = suid
usr1.Login = lgn1.Name
srv1.Databases(dbname).Users.Add usr1
srv1.Databases(dbname).DatabaseRoles("db_datareader").AddMember usr1.Name

' ÉèÖõ÷Óà ADP µÄ×Ó³ÌÐò²ÎÊý
prpath = "C:\Documents and Settings\Administrator\My Documents\"
prname = "NorthwindCS"

' ¸Ã²ÎÊýÓÃÓÚ¿ØÖƵ±Ç°Óû§Ê¹Óà Windows µÇ¼
' ´úÌæ SQL Server µÄ suid ºÍ pwd
bolWindowsLogin = False

' ʹÓà Windows »ò SQL Server µÇ¼µ÷Óôò¿ªÃûΪ prname ADP µÄ×Ó³ÌÐò
OpenADPWindowsOrSQLServer srvname, dbname, prpath, prname, suid, pwd, bolWindowsLogin
End Sub

Sub CleanUpAfterMakeLoginWithDAtaReader()
Dim srv1 As New SQLDMO.SQLServer

srv1.Connect "(local)", "sa", ""

srv1.Databases("NorthwindCS").Users.Remove "UserX"
srv1.Logins.Remove "UserX"
End Sub

--------------------------------------------------------------------------------
Ïà¹ØÎÄÕÂ
ÈçºÎÄÜͨ¹ý´°Ìå·ÃÎÊ±í£¬µ«²»ÄÜÖ±½Ó¶ÁÈ¡±í£¿ 2004-8-27 21:58:17
Êý¾Ý¿âÉè¼ÆÖеÄÃô½Ý·½·¨ 2004-2-9 11:19:16
µ÷ÓÃoutlookÉú³É²¢·¢ËÍÓʼþµÄ´úÂë 2004-1-30 10:32:07
SQL SERVER»ù±¾¸ÅÄîѧϰӡÏó 2004-1-1 21:23:13
Access Êý¾Ý¿âºÍ Access ÏîÄ¿Ö®¼äµÄÊý¾ÝÀàÐ͵ıȽϻòÓ³Éä 2003-12-26 14:20:19
´óÐÍÊý¾Ý¿âÉè¼ÆÔ­Ôò 2003-12-15 12:49:18
ERwin--¼ò»¯µÄÊý¾Ý¿âÉè¼Æ¹¤¾ß 2003-12-15 12:41:40
ERwinÈí¼þ¼ò½é 2003-12-15 12:39:55
¾­µä×Ó²éѯӦÓà 2003-11-17 8:54:51
Á˽â Microsoft Access °²È«ÐÔ 2003-11-13 11:21:08
²»µ±±àдSQLÓï¾äµ¼ÖÂϵͳ²»°²È« 2003-11-13 10:55:26
±ÜÃâAccessºÍSQL ServerµÄ¿ÕÖµ³åÍ» 2003-10-17 20:03:19
±í´ïʽÉú³ÉÆ÷Öеļ¸ÖÖ³£Ó÷ûºÅ 2003-10-17 19:55:56
´´½¨ÍêÃÀ±¨±í 2003-10-17 19:37:17
£Ó£Ñ£Ì Óï·¨²Î¿¼ÊÖ²á 2003-10-17 19:12:40
SQL SERVER ÓëACCESS¡¢EXCELµÄÊý¾Ýת»» 2003-10-17 19:12:00
´æ´¢¹ý³ÌÈëÃÅ 2003-10-17 19:06:48
MS Jet SQL for Access 2000Öм¶Æª(IV) 2003-10-17 18:52:20
MS Jet SQL for Access 2000Öм¶Æª (III) 2003-10-17 18:51:47
MS Jet SQL for Access 2000Öм¶Æª (II) 2003-10-17 18:51:01
MS Jet SQL for Access 2000Öм¶Æª (I) 2003-10-17 18:47:38
ʹÓÃADOÁ¬½Óµ½·À»ðǽºóµÄSQL Server 2003-10-17 18:43:57
ADOÁ¬½ÓÊý¾Ý¿â×Ö·û´®´óÈ« 2003-10-17 18:40:47
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨¶þ£© 2003-10-14 8:45:33
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨Èý£© 2003-10-14 8:44:20
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨Ò»£© 2003-10-14 8:43:26
ACCESSÊý¾Ý·ÃÎÊÒ³ÅäÖÃʵÀý 2003-10-14 8:36:00
ÌáÉýÇ°ºǫ́ģʽ³ÌÐòµÄËÙ¶È 2003-10-10 10:17:08
AccessÆô¶¯ÃüÁîÐÐÑ¡Ïî´óÈ« 2003-10-10 10:02:00
Ò»¸ö¼òµ¥µÄAdoÓ÷¨Ê¾Àý 2003-10-6 16:24:38
Access 2002 ÖеÄÃû³Æ×Ô¶¯¸üÕý˵Ã÷ 2003-10-6 15:16:24
Êý¾Ý¿âÓ¦ÓóÌÐòÐè×¢ÒâµÄÎÊÌâ 2003-10-6 11:11:06