我的公司计划从32位的MS Office 365迁移到64位的等效版本。这个工具多年来在不同的WinOS上都能完美地工作(XP/7/10,无论是32位还是64位),支持不同的32位MS Office版本(XP,2007,2013和365 Pro)。但是当在MS Office 365 Pro 64位上运行该工具时,我在与数据库的连接上很挣扎(所有其他障碍,例如PtrSafe声明都已解决)。我安装了64位版本的ODBC IBM DB2客户端,并在SysWoW64\ODBCAD中配置了此客户端,连接测试成功返回。目前为止,我认为至少驱动程序是正常的。在VBA中调用以打开连接时,我总是收到以下错误消息:
在我看来,Excel似乎不知道为什么没有识别出DB2驱动程序-〉注册问题。下面是我的代码:
Option Explicit
Public oDB2Connection As ADODB.Connection
Public zQuery As String
Public zDB2User As String
Public zDB2Pwd As String
Public oRecordSet As ADODB.Recordset
Public iLoginTrials As Integer
Public zTable As String
Sub ConnectDB2()
Dim PS As stPositions
Dim bFirstLogin As Boolean
Dim zStr As String
'//create connection object
Set oDB2Connection = New ADODB.Connection
Set oRecordSet = New ADODB.Recordset
On Error GoTo Errorhandler
iLoginTrials = 0
bFirstLogin = False
With oDB2Connection
While (.State = 0)
If (.State = 0) Then
zDB2User = oMainSheet.Range(RANGE_DB_USR).Value
zDB2Pwd = oMainSheet.Range(RANGE_DB_PWD).Value
If (AccessLevel < AL_RO) Then
bFirstLogin = True
With DlgLogin
.LoginReadOnly.Visible = True
.LoginPM.Visible = True
.LoginAdmin.Visible = True
.LoginReadOnly.Value = 1
.UserName = USER_RO
.Password = "member"
.StartUpPosition = 1 'posCenterOwner
PS = PositionForm(WhatForm:=DlgCalendar, AnchorRange:=ActiveSheet.Cells(1, 1))
.Top = PS.FrmTop ' set the Top position of the form
.Left = PS.FrmLeft ' set the Left position of the form
.Show '//RunUserInterface
End With
End If
End If
.ConnectionString = "Driver={IBM DB2 ODBC DRIVER};" _
& "Database=<myDB>;" _
& "Hostname=<myHost>;" _
& "Port=<myPort>;" _
& "Protocol=TCPIP;" _
& "Uid=" & zDB2User & ";" _
& "Pwd=" & zDB2Pwd & ";"
'//set cursor location
.CursorLocation = adUseClient
'//open database
.Open
zQuery = "SET CURRENT SCHEMA = 'DCODB2'"
Message zQuery
.Execute zQuery, , adExecuteNoRecords
If (.State = 1) Then
With oMainSheet
.Unprotect ("*")
.Range(RANGE_DB_USR).Value = zDB2User
.Range(RANGE_DB_PWD).Value = zDB2Pwd
.Protect ("*")
End With
End If
'//in case of failure try again to login
PP iLoginTrials
If (iLoginTrials > 3) Then
zStr = "Access to PM database denied"
MsgBox zStr
Message zStr
End
End If
Wend '//.State = 0
If (.State And bFirstLogin) Then
ShowDlgDone "Connection to DB2 successfully established", vbModal
Message "Userlevel: " & zDB2User
End If
End With '//oDB2Connection
Exit Sub
Errorhandler:
zDB2Pwd = ""
With oMainSheet
.Unprotect ("*")
.Range(RANGE_DB_USR).Value = ""
.Range(RANGE_DB_PWD).Value = zDB2Pwd
.Protect ("*")
End With
MsgBox "Error: " & ERR.Description
Resume Next
End Sub
对于64位Excel,MySQL连接的类似构造也会失败。
1条答案
按热度按时间yuvru6vn1#
我自己发现的!查看注册表中的HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers,我看到那里的驱动程序的名称看起来略有不同:“IBM DB2 ODBC驱动程序-IBMDBCL 1”
在我的代码中,我最初写道:“IBM DB2 ODBC驱动程序”
所以有了上面的修复,我让它工作了,万岁!!