是在程序里头配置或删除ODBC?还是用VB把ODBC配置程序窗口给调出来? '========================================================================Public Const ODBC_ADD_DSN = 1 ' Add User DataSource Public Const ODBC_CONFIG_DSN = 2 ' Configure (edit) User DataSource Public Const ODBC_REMOVE_DSN = 3 ' Remove User DataSource Public Const ODBC_ADD_SYS_DSN = 4 ' Add System DataSource Public Const ODBC_CONFIG_SYS_DSN = 5 ' Configure (edit) System DataSource Public Const ODBC_REMOVE_SYS_DSN = 6 ' Remove System DataSourcePublic Const strORADriver As String = "Oracle73 Ver 2.5" Public Const strSQLDriver As String = "SQL Server"Public Const strAttributes = "DSN=MYDSN_Name" & Chr$(0) & "SERVER=DBServer" & Chr$(0) & "UserID=Userid" & Chr$(0) & "Description=My DSN via program" & Chr$(0)Private Sub cmdRemoveDS_Click() Dim lngRet As Long' Remove a datasource lngRet = SQLConfigDataSource(0&, ODBC_REMOVE_SYS_DSN, strSQLDriver, strAttributes)
If lngRet = 0 Then MsgBox Err.Description End IfEnd SubPrivate Sub cmdCreateDS_Click() Dim lngRet As Long' Create/Reconfigure a datasource ' If a DS does not exist the configure will automatically create one lngRet = SQLConfigDataSource(0&, ODBC_CONFIG_SYS_DSN, strSQLDriver, strAttributes)
If lngRet = 0 Then MsgBox Err.Description End IfEnd Sub'========================================================================Private Sub cmdOpenODBC_Click() Dim strODBCDllName As String strODBCDllName = "C:\WINNT\System32\ODBCAD32.EXE" On Error Resume Next Shell strODBCDllName End Sub'========================================================================
不好意思,上面的例子说不到点子上如何动态新增、移除 ODBC DSN?一般我们建立 Client 端 DSN 都是在使用者的机器上进入【控制台】【ODBC 资料来源管理员】去建立,但是如果我们开发的 APP 使用者很多时,这就有点累人了,所以我们可以将这个动作放在程序中!新增 DSN 的方法有二种: 1、使用 DBEngine 物件的 RegisterDatabase 方法 2、呼叫 SQLConfigDataSource API不管使用以上任何一种方法新增 DSN,一共会写入二个地方,一个是注册表,一个是 ODBC.INI。而删除 DSN 的方法同上面的第二种方法,呼叫 SQLConfigDataSource API。以下之模组以 Oracle73 Ver 2.5 为例,在 Form 的声明区中加入以下声明及模组: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 PointerPrivate Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As LongPublic 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 SubPublic 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 SubPrivate Sub Command2_Click() DeleteDSN "Test" End Sub'而写到系统的资料如下:1、ODBC.INI[ODBC 32 bit Data Sources] Test=Oracle73 Ver 2.5 (32 bit)[Test] Driver32=C:\ORAWIN95\ODBC250\sqo32_73.dll2、注册表机码:HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources 名称:Test 资料:Oracle73 Ver 2.5机码:HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Test 名称:Description 资料:Test 名称:Driver 资料:C:\ORAWIN95\ODBC250\sqo32_73.dll 名称:Server 资料:Oracle8 名称:UserId 资料:Scott※注一及注二可任选一种,只要将不使用的方法 Mark 起来即可! ※若您想使用其他之资料库,只要将以上模组稍作修改即可!
我修改了你的代码它没有报错也有提提示OK! ,可就是看不到创建Test,还有就是要密码的话怎么写?我用的SQL SERVER 2000 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 = "8.00.194" sAttributes = "Server=sql server" & Chr$(0) sAttributes = sAttributes & "DESCRIPTION=" & sDSN & Chr$(0) sAttributes = sAttributes & "DATABASE=DBFinance" & Chr$(0) sAttributes = sAttributes & "Userid=Scott" & Chr$(0) 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 = "8.00.194" sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0) nRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, sDriver, sAttributes) End Sub Private Sub Command1_Click() CreateDSN "Test" MsgBox "OK!" End Sub 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 = "8.00.194" sAttributes = "Server=sql server" & Chr$(0) sAttributes = sAttributes & "DESCRIPTION=" & sDSN & Chr$(0) sAttributes = sAttributes & "DATABASE=DBFinance" & Chr$(0) sAttributes = sAttributes & "Userid=Scott" & Chr$(0) 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 = "8.00.194" sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0) nRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, sDriver, sAttributes) End Sub Private Sub Command1_Click() CreateDSN "Test" MsgBox "OK!" End Sub
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 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 Function CreateDSN(sDSN As String, sDriver) As Boolean Dim nRet As Long Dim sAttributes As String sAttributes = sAttributes & "SERVER=(local)" & vbNullChar sAttributes = sAttributes & "DSN=" & sDSN & "" & vbNullChar sAttributes = sAttributes & "DESCRIPTION=" & sDSN & vbNullChar sAttributes = sAttributes & "DATABASE=pubs" & vbNullChar nRet = SQLConfigDataSource(0&, ODBC_ADD_DSN, sDriver, sAttributes) CreateDSN = CBool(nRet) End Function Public Function DeleteDSN(sDSN As String, sDriver) As Boolean Dim nRet As Long Dim sAttributes As String sAttributes = sAttributes & "DSN=" & sDSN & vbNullChar nRet = SQLConfigDataSource(0&, ODBC_REMOVE_DSN, sDriver, sAttributes) DeleteDSN = CBool(nRet) End Function Private Sub cmdCreateDSN_Click() If CreateDSN("SQL Server DSN Test", "SQL Server") Then MsgBox "DSN ""SQL Server DSN Test"" created successfully!" Else MsgBox "Failed to create DSN ""SQL Server DSN Test""!" End If If CreateDSN("Oracle DSN Test", "Oracle ODBC Driver") Then MsgBox "DSN ""Oracle DSN Test"" created successfully!" Else MsgBox "Failed to create DSN ""Oracle DSN Test""!" End If End Sub Private Sub cmdDeleteDSN_Click() If DeleteDSN("SQL Server DSN Test", "SQL Server") Then MsgBox "Delete DSN ""SQL Server DSN Test"" successfully!" Else MsgBox "Failed to delete DSN ""SQL Server DSN Test""!" End If If DeleteDSN("Oracle DSN Test", "Oracle ODBC Driver") Then MsgBox "Delete DSN ""Oracle DSN Test"" successfully!" Else MsgBox "Failed to delete DSN ""Oracle DSN Test""!" End If End Sub
'========================================================================Public Const ODBC_ADD_DSN = 1 ' Add User DataSource
Public Const ODBC_CONFIG_DSN = 2 ' Configure (edit) User DataSource
Public Const ODBC_REMOVE_DSN = 3 ' Remove User DataSource
Public Const ODBC_ADD_SYS_DSN = 4 ' Add System DataSource
Public Const ODBC_CONFIG_SYS_DSN = 5 ' Configure (edit) System DataSource
Public Const ODBC_REMOVE_SYS_DSN = 6 ' Remove System DataSourcePublic Const strORADriver As String = "Oracle73 Ver 2.5"
Public Const strSQLDriver As String = "SQL Server"Public Const strAttributes = "DSN=MYDSN_Name" & Chr$(0) & "SERVER=DBServer" & Chr$(0) & "UserID=Userid" & Chr$(0) & "Description=My DSN via program" & Chr$(0)Private Sub cmdRemoveDS_Click()
Dim lngRet As Long' Remove a datasource
lngRet = SQLConfigDataSource(0&, ODBC_REMOVE_SYS_DSN, strSQLDriver, strAttributes)
If lngRet = 0 Then
MsgBox Err.Description
End IfEnd SubPrivate Sub cmdCreateDS_Click()
Dim lngRet As Long' Create/Reconfigure a datasource
' If a DS does not exist the configure will automatically create one
lngRet = SQLConfigDataSource(0&, ODBC_CONFIG_SYS_DSN, strSQLDriver, strAttributes)
If lngRet = 0 Then
MsgBox Err.Description
End IfEnd Sub'========================================================================Private Sub cmdOpenODBC_Click()
Dim strODBCDllName As String
strODBCDllName = "C:\WINNT\System32\ODBCAD32.EXE"
On Error Resume Next
Shell strODBCDllName
End Sub'========================================================================
1、使用 DBEngine 物件的 RegisterDatabase 方法
2、呼叫 SQLConfigDataSource API不管使用以上任何一种方法新增 DSN,一共会写入二个地方,一个是注册表,一个是 ODBC.INI。而删除 DSN 的方法同上面的第二种方法,呼叫 SQLConfigDataSource API。以下之模组以 Oracle73 Ver 2.5 为例,在 Form 的声明区中加入以下声明及模组: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 PointerPrivate Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As LongPublic 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 SubPublic 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 SubPrivate Sub Command2_Click()
DeleteDSN "Test"
End Sub'而写到系统的资料如下:1、ODBC.INI[ODBC 32 bit Data Sources]
Test=Oracle73 Ver 2.5 (32 bit)[Test]
Driver32=C:\ORAWIN95\ODBC250\sqo32_73.dll2、注册表机码:HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources
名称:Test 资料:Oracle73 Ver 2.5机码:HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Test
名称:Description 资料:Test
名称:Driver 资料:C:\ORAWIN95\ODBC250\sqo32_73.dll
名称:Server 资料:Oracle8
名称:UserId 资料:Scott※注一及注二可任选一种,只要将不使用的方法 Mark 起来即可!
※若您想使用其他之资料库,只要将以上模组稍作修改即可!
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 = "8.00.194"
sAttributes = "Server=sql server" & Chr$(0)
sAttributes = sAttributes & "DESCRIPTION=" & sDSN & Chr$(0)
sAttributes = sAttributes & "DATABASE=DBFinance" & Chr$(0)
sAttributes = sAttributes & "Userid=Scott" & Chr$(0)
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 = "8.00.194"
sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0)
nRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, sDriver, sAttributes)
End Sub
Private Sub Command1_Click()
CreateDSN "Test"
MsgBox "OK!"
End Sub
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 = "8.00.194"
sAttributes = "Server=sql server" & Chr$(0)
sAttributes = sAttributes & "DESCRIPTION=" & sDSN & Chr$(0)
sAttributes = sAttributes & "DATABASE=DBFinance" & Chr$(0)
sAttributes = sAttributes & "Userid=Scott" & Chr$(0)
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 = "8.00.194"
sAttributes = sAttributes & "DSN=" & sDSN & Chr$(0)
nRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, sDriver, sAttributes)
End Sub
Private Sub Command1_Click()
CreateDSN "Test"
MsgBox "OK!"
End Sub
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
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 Function CreateDSN(sDSN As String, sDriver) As Boolean
Dim nRet As Long
Dim sAttributes As String
sAttributes = sAttributes & "SERVER=(local)" & vbNullChar
sAttributes = sAttributes & "DSN=" & sDSN & "" & vbNullChar
sAttributes = sAttributes & "DESCRIPTION=" & sDSN & vbNullChar
sAttributes = sAttributes & "DATABASE=pubs" & vbNullChar
nRet = SQLConfigDataSource(0&, ODBC_ADD_DSN, sDriver, sAttributes)
CreateDSN = CBool(nRet)
End Function
Public Function DeleteDSN(sDSN As String, sDriver) As Boolean
Dim nRet As Long
Dim sAttributes As String
sAttributes = sAttributes & "DSN=" & sDSN & vbNullChar
nRet = SQLConfigDataSource(0&, ODBC_REMOVE_DSN, sDriver, sAttributes)
DeleteDSN = CBool(nRet)
End Function
Private Sub cmdCreateDSN_Click()
If CreateDSN("SQL Server DSN Test", "SQL Server") Then
MsgBox "DSN ""SQL Server DSN Test"" created successfully!"
Else
MsgBox "Failed to create DSN ""SQL Server DSN Test""!"
End If
If CreateDSN("Oracle DSN Test", "Oracle ODBC Driver") Then
MsgBox "DSN ""Oracle DSN Test"" created successfully!"
Else
MsgBox "Failed to create DSN ""Oracle DSN Test""!"
End If
End Sub
Private Sub cmdDeleteDSN_Click()
If DeleteDSN("SQL Server DSN Test", "SQL Server") Then
MsgBox "Delete DSN ""SQL Server DSN Test"" successfully!"
Else
MsgBox "Failed to delete DSN ""SQL Server DSN Test""!"
End If
If DeleteDSN("Oracle DSN Test", "Oracle ODBC Driver") Then
MsgBox "Delete DSN ""Oracle DSN Test"" successfully!"
Else
MsgBox "Failed to delete DSN ""Oracle DSN Test""!"
End If
End Sub
"系统NSD"该怎么做?
ODBC_CONFIG_DSN - USERDSN_ONLY
ODBC_REMOVE_DSN - USERDSN_ONLY
ODBC_ADD_SYS_DSN - SYSTEMDSN_ONLY
ODBC_CONFIG_SYS_DSN - SYSTEMDSN_ONLY
ODBC_REMOVE_SYS_DSN - SYSTEMDSN_ONLY 密码在进行实际连接的时候提供给Connection对象