用代码建立DSN(SQL)
Private Const ODBC_ADD_DSN = 1
Private Const ODBC_CONFIG_DSN = 2
Private Const ODBC_REMOVE_DSN = 3
Private Const ODBC_ADD_SYS_DSN = 4
Private Const ODBC_REMOVE_SYS_DSN = 6
Private Const vbAPINull As Long = 0&
Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
Public Sub CreateDSN(sDSN As String)
On Error Resume Next
Dim nRet As Long
Dim sDriver As String
Dim sAttributes As String
sDriver = "SQl server"
sAttributes = "DSN=" & sDSN & Chr$(0)
sAttributes = sAttributes & "Server=(local)" & Chr$(0)
sAttributes = sAttributes & "Database=webstation" & Chr$(0)
nRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_SYS_DSN, sDriver, sAttributes)
End Sub
Public Sub DeleteDSN(sDSN As String)
On Error Resume Next
Dim nRet As Long
Dim sDriver As String
Dim sAttributes As String
sDriver = "SQl server"
sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0)
nRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_SYS_DSN, sDriver, sAttributes)
End Sub
Private Sub Command1_Click()
CreateDSN "Test"
End Sub
Private Sub Command2_Click()
DeleteDSN "Test"
End Sub
ODBC DSN Connections
Using an ODBC DSN (Data Source Name) is a two step process.
1) You must first create the DSN via the "ODBC Data Source Administrator"
program found in your computer's Control Panel (or Administrative Tools
menu in Windows 2000). Make sure to create a SYSTEM DSN (not a USER
DSN) when using ASP. You can also create the DSN via Visual Basic code.
2) Then use the following connection string - with your own DSN
name of course.
Private Const ODBC_ADD_DSN = 1 'Add data source
Private Const ODBC_CONFIG_DSN = 2 'Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 'Remove data source
Private Const vbAPINull As Long = 0& 'NULL Pointer
Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
(ByVal hwndParent As Long, ByVal fRequest As Long, _
ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
Public Sub CreateDSN(sDSN As String)
Dim nRet As Long
Dim sDriver As String
Dim sAttributes As String
sDriver = "Oracle73 Ver 2.5"
sAttributes = "Server=Oracle8" & Chr$(0)
sAttributes = sAttributes & "DESCRIPTION=" & sDSN & Chr$(0)
'sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0)
sAttributes = sAttributes & "DATABASE=DBFinance" & Chr$(0)
sAttributes = sAttributes & "Userid=Scott" & Chr$(0)
'sAttributes = sAttributes & "PWD=myPassword" & Chr$(0)
DBEngine.RegisterDatabase sDSN, sDriver, True, sAttributes '注一
'nRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, sDriver, sAttributes) '注二
End Sub
Public Sub DeleteDSN(sDSN As String)
Dim nRet As Long
Dim sDriver As String
Dim sAttributes As String
sDriver = "Oracle73 Ver 2.5"
sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0)
nRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, sDriver, sAttributes)
End Sub
'假设要产生的 DSN 为 Test,实际使用示例如下:
Private Sub Command1_Click()
CreateDSN "Test"
End Sub
Private Sub Command2_Click()
DeleteDSN "Test"
End Sub
'而写到系統的数据如下:
1、ODBC.INI
[ODBC 32 bit Data Sources]
Test=Oracle73 Ver 2.5 (32 bit)
Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv As Long, ByVal fDirection As Integer, ByVal szDSN As String, ByVal cbDSNMax As Integer, pcbDSN As Integer, ByVal szDescription As String, ByVal cbDescriptionMax As Integer, pcbDescription As Integer) As Integer
Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" (ByRef env As Long) As Long