如何在Access2003和Access2002中创建DSN的连接到SQLServer对链接表
2023-06-13 09:13:45 时间
方法 1: 使用 CreateTableDef 方法
CreateTableDef 方法可创建链接表。 若要使用此方法, 创建一个新模块, 然后以下 AttachDSNLessTable 函数添加到新模块。
复制代码代码如下:
CreateTableDef 方法可创建链接表。 若要使用此方法, 创建一个新模块, 然后以下 AttachDSNLessTable 函数添加到新模块。
"//Name : AttachDSNLessTable
"//Purpose : Create a linked table to SQL Server without using a DSN
"//Parameters
"// stLocalTableName: Name of the table that you are creating in the current database
"// stRemoteTableName: Name of the table that you are linking to on the SQL Server database
"// stServer: Name of the SQL Server that you are linking to
"// stDatabase: Name of the SQL Server database that you are linking to
"// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
"// stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next
If Len(stUsername) = 0 Then
"//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
"//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function
若要调用AttachDSNLessTable函数,请代码,它类似于之一以下代码示例在Autoexec宏中或启动窗体Form_Open事件中:
AttachDSNLessTable("authors","authors","(local)","pubs","","")
PrivateSubForm_Open(CancelAsInteger)
IfAttachDSNLessTable("authors","authors","(local)","pubs","","")Then
"//Allisokay.
Else
"//Notokay.
EndIf
EndSub
向Access数据库添加多个链接表时注意您必须调整编程逻辑。"//Name:CreateDSNConnection
"//Purpose:CreateaDSNtolinktablestoSQLServer
"//Parameters
"//stServer:NameofSQLServerthatyouarelinkingto
"//stDatabase:NameoftheSQLServerdatabasethatyouarelinkingto
"//stUsername:NameoftheSQLServeruserwhocanconnecttoSQLServer,leaveblanktouseaTrustedConnection
"//stPassword:SQLServeruserpassword
FunctionCreateDSNConnection(stServerAsString,stDatabaseAsString,OptionalstUsernameAsString,OptionalstPasswordAsString)AsBoolean
OnErrorGoToCreateDSNConnection_Err
DimstConnectAsString
IfLen(stUsername)=0Then
"//UsetrustedauthenticationifstUsernameisnotsupplied.
stConnect="Description=myDSN"&vbCr&"SERVER="&stServer&vbCr&"DATABASE="&stDatabase&vbCr&"Trusted_Connection=Yes"
Else
stConnect="Description=myDSN"&vbCr&"SERVER="&stServer&vbCr&"DATABASE="&stDatabase&vbCr
EndIf
DBEngine.RegisterDatabase"myDSN","SQLServer",True,stConnect
"//Adderrorchecking.
CreateDSNConnection=True
ExitFunction
CreateDSNConnection_Err:
CreateDSNConnection=False
MsgBox"CreateDSNConnectionencounteredanunexpectederror:"&Err.Description
EndFunction
注意如果再次,调用RegisterDatabase方法DSN更新。若要调用CreateDSNConnection函数,请代码,它类似于之一以下代码示例在Autoexec宏中或启动窗体Form_Open事件中:
CreateDSNConnection("(local)","pubs","","")
PrivateSubForm_Open(CancelAsInteger)
IfCreateDSNConnection("(local)","pubs","","")Then
"//Allisokay.
Else
"//Notokay.
EndIf
EndSub
请CreateTableDef方法,有关访问下列MicrosoftDeveloperNetwork(MSDN)Web站点:
相关文章
- SQLServer 错误 10060 在建立与服务器的连接时出错。 在连接到 SQL Server 时,在默认的设置下 SQL Server 不允许远程连接可能会导致此失败。 (提供程序:TCP 提供程序,错误:0 – 由于被连接方在一段时间后未正确响应,或者连接的主机无法响应,连接尝试失败。)(Microsoft SQL Server,错误:10060) 故障 处理 修复 支持远程
- Linux环境中使用BIEE 连接SQLServer业务数据源
- 重启后的SQLServer: 一次值得纪念的变革(sqlserver重启)
- 连接SQLServer数据库的步骤与方法(连接sqlserver)
- 如何快速有效地导入SQLServer(导入sqlserver)
- 安卓应用的SQL Server连接体验(安卓用sqlserver)
- 如何实现SQLServer数据库的连接(如何连sqlserver)
- 伍华聪:让SQLServer服务更好的为你服务(伍华聪sqlserver)
- 管理SQLServer资料库,精准掌握数据(sqlserver资料库)
- 如何检查和提升SQLServer数据库的覆盖率(sqlserver覆盖率)
- 索引SQLServer加速查询:聚集索引的优势(sqlserver 聚集)
- 掌握SqlServer,记录你的行动与收获(sqlserver日记)
- 型如何掌握SQLServer文本型数据的管理(sqlserver 文本)
- 使用SQLServer实现安卓App数据库连接(sqlserver 安卓)
- 使用SQLServer记录姓名及号码的简单方法(sqlserver 姓号)
- 使用SQLserver算法获取日期的星期信息(sqlserver取星期)
- 提高安全性:使用SQLServer加密表(sqlserver加密表)
- SQL Server加强信息注释,掌控数据库更轻松(sqlserver加备注)
- 如何在SQLServer中实现列的合并(sqlserver列合并)
- 找SQLServer中如何快速查找所需信息(sqlserver中查)
- 数据如何在SQLServer中添加数据(sqlserver中增加)
- Qt与SQLServer结合,提升开发效率(qt和sqlserver)