Office中国论坛/Access中国论坛

标题: vba如何连接网络上的sql数据库? [打印本页]

作者: jinjindog007 时间: 2008-2-1 00:14
标题: vba如何连接网络上的sql数据库?
我在word里用vba做了个控件,当中需要连接到网络上的sql数据库.网上连接sql数据库的代码太杂了,而且很多不适用于网络数据库,实在不知道用哪条好,哪位好心人能告诉我下具体的代码应该怎么写啊,先谢谢了

[ 本帖最后由 jinjindog007 于 2008-2-1 00:19 编辑 ]

作者: haemon 时间: 2008-2-12 02:17
For Standard Security:

oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword;"

For a Trusted Connection:

oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI;"

To connect to a "Named Instance" (SQL Server 2000)

oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName\Inst2;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword;"

To Prompt user for username and password:

oConn.Provider = "sqloledb"
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;"

To connect via an IP address:

oConn.Open "Provider=sqloledb;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Network Library=DBMSSOCN;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword;"
Note:
- xxx.xxx.xxx.xxx is an IP address
- "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than Named Pipes (Q238949)
- 1433 is the default port number for SQL Server
- You can also add "Encrypt=yes" for encryption

For more information, see: Microsoft OLE DB Provider for SQL Server

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)