可以在VBA中使用从DAO Database类创建的对象来使用DSNless连接 .

使用ODBC连接与数据库的连接按预期工作，但是如果使用其他连接字符串类型（如www.connectionstrings.com所述），则不会 Build 连接 .

public Sub dbConnectTest()

Dim myDB As DAO.Database
Dim conn As String
Dim tns As String
Dim odbcString as String

odbcString = "ODBC;DSN=Location Name;UID=ANUSER;PWD=apassword;DBQ=A_TNS_NAME"

' this part works
   Set myWorkspace = DBEngine.CreateWorkspace("APPNAME", "admin", "")
   Set myDB = myWorkspace.OpenDatabase(Name:="", Options:=dbDriverNoPrompt, ReadOnly:=True, _
                                                        Connect:=odbcString)
 ' same here                                                        
    Set myDB = OpenDatabase("", False, False, "ODBC")                                                       
' any of below part don't work

 odbcString = "Driver=(Oracle in XEClient);dbq=server:1980/SID;UID=ANUSER;PWD=apassword;"
 odbcString = "Driver={Oracle in OraHome92};Dbq=A_TNS_NAME;UID=ANUSER;PWD=apassword;"
 odbcString = "Driver={Microsoft ODBC for Oracle};CONNECTSTRING=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=server)(PORT=1980)))(CONNECT_DATA=(SERVICE_NAME=SID)));Uid=ANUSER;Pwd=apassword;"
     Set myDB = OpenDatabase("", False, False, odbcString)                                                      



end sub

我想更改连接字符串，因为即使在用户注销后myDB对象设置为空，当使用新密码请求新登录时，旧连接字符串以某种方式保留，而不是成功连接错误连接对象被重新获得 .

问候，

vba oracle odbc database-connection