连接到SQL通过VBA code问题 [英] Connect to SQL Through VBA Code Issues
问题描述
我有一个运行在数据库启动时运行一些querys和更新一些表的code。我想设置一个code，连接到链接数据表的外部源，所以我不必每次都输入密码。我不断收到一个奇怪的错误，虽然，在登录窗口仍popsup但信息ppopulated $ P $，当我点击OK我收到其他错误消息。我已经显示下面的code和错误窗口。
I have a code that runs at the DB startup to run some querys and update some tables. I am trying to setup a code that connects to the external source of the linked data tables so I don't have to enter the password each time. I keep getting an odd error though, the login window still popsup but the information is prepopulated, when I click ok I get another error message. I have displayed the code and error window below.
Function MasterRun()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Set conn = CreateObject("ADODB.Connection")
Dim rs2 As ADODB.Recordset
Dim strConn As String
strConn = strConn & "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=SOURCE;"
strConn = strConn & "INITIAL CATALOG=DB;"
strConn = strConn & "UID=User;"
strConn = strConn & "PWD=Password;"
conn.ConnectionString = strConn
conn.Open
On Error GoTo MasterRun_Err
DoCmd.SetWarnings False
StartUp
Update
DoCmd.OpenQuery "qry_V_TMS_EMPLOYEE", acViewNormal, acEdit
DoCmd.Save acQuery, "qry_V_TMS_EMPLOYEE"
DoCmd.Close acQuery, "qry_V_TMS_EMPLOYEE"
DoCmd.OpenQuery "qry2013CDMtg", acViewNormal, acEdit
DoCmd.Save acQuery, "qry2013CDMtg"
DoCmd.Close acQuery, "qry2013CDMtg"
DoCmd.OpenQuery "qryUpdateEmpData", acViewNormal, acEdit
DoCmd.OpenForm "frmCDData"
DoCmd.SetWarnings True
MsgBox ("The database has been updated and is ready for use.")
MasterRun_Exit:
Exit Function
MasterRun_Err:
MsgBox Error$
Resume MasterRun_Exit
End Function
和下方的登录窗口弹出。这是弹出之前，我甚至开始尝试在这方面code写的窗口。
And below is the login window that pops up. This is the window that popped up before I even started trying to write in this connection code.
我已经转载了这个问题的修正，我想到了一个不同的方式来建立连接。
I have reposted a revision of this question, I have thought of a different way to establish the connection.
推荐答案
有可能涉及你的问题多的问题，但其中第一个跳出我是你想要的，所以我不必每次输入密码的。
There may be multiple issues involved with your question, but the first which jumps out at me is that you want "so I don't have to enter the password each time".
但你的code包括...
But your code includes ...
conn.Properties("Prompt") = adPromptAlways
这行的意思的问我的用户名和密码，每次我打开连接。的这就是你的意图相反。
That line means "ask me for user name and password every time I open the connection". That is the opposite of your intention.
删除了这一行，看看有什么其他的问题依然存在。
Remove that line and see what other problems remain.
随着对眼前的问题更普遍的意见，焦点......这是失败的连接尝试。因此，只注重了......
As more general advice, focus on the immediate problem ... which is the failed connection attempt. So focus only on that ...
Public Sub TestConnection()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
' you already created the connection object in the line above,
' so CreateObject is not useful:
'Set conn = CreateObject("ADODB.Connection")
Dim strConn As String
strConn = "PROVIDER= ..." ' this is where you need work
' make sure you built the connection string you expected;
' view the output from the next line in the Immediate window ---
' Ctrl+g will take you there
Debug.Print strConn
conn.ConnectionString = strConn
conn.Open
conn.Close
Set conn = Nothing
End Sub
这篇关于连接到SQL通过VBA code问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
