如何在 Access 2003 和 Access 2002 中创建 DSN 的连接到 SQLServer 对链接表
程序员文章站
2022-07-01 23:50:59
方法 1: 使用 createtabledef 方法 createtabledef 方法可创建链接表。 若要使用...
方法 1: 使用 createtabledef 方法
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 事件中:
若要调用 createdsnconnection 函数, 请代码, 它类似于之一以下代码示例在 autoexec 宏中或启动窗体 form_open 事件中:
注意 此方法假定通过使用 " mydsn " 作为 dsn 名称, 您已经创建链接 sqlserver 表 access 数据库中。
请 createtabledef 方法, 有关访问下列 microsoft developer network (msdn) web 站点:
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 事件中:
• | 当您使用 autoexec, 调用 attachdsnlesstable 函数, 并然后传递参数, 如以下所示从 runcode 操作。
|
• | 当您使用启动窗体, 将代码, 它类似于以下以 form_open 事件。 向 access 数据库添加多个链接表时 注意 您必须调整编程逻辑。 |
方法 2: 使用 dao.registerdatabase 方法
dao.registerdatabase 方法可在 autoexec 宏中或启动表单中创建 dsn 连接。 尽管此方法不删除对 dsn 连接, 要求它不帮助您通过代码中创建 dsn 连接解决问题。 若要使用此方法, 创建一个新模块, 然后以下 createdsnconnection 函数添加到新模块。'//name : createdsnconnection
'//purpose : create a dsn to link tables to sql server
'//parameters
'// stserver: name of 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 createdsnconnection(stserver as string, stdatabase as string, optional stusername as string, optional stpassword as string) as boolean
on error goto createdsnconnection_err
dim stconnect as string
if len(stusername) = 0 then
'//use trusted authentication if stusername is not supplied.
stconnect = "description=mydsn" & vbcr & "server=" & stserver & vbcr & "database=" & stdatabase & vbcr & "trusted_connection=yes"
else
stconnect = "description=mydsn" & vbcr & "server=" & stserver & vbcr & "database=" & stdatabase & vbcr
end if
dbengine.registerdatabase "mydsn", "sql server", true, stconnect
'// add error checking.
createdsnconnection = true
exit function
createdsnconnection_err:
createdsnconnection = false
msgbox "createdsnconnection encountered an unexpected error: " & err.description
end function
注意 如果再次, 调用 registerdatabase 方法 dsn 更新。若要调用 createdsnconnection 函数, 请代码, 它类似于之一以下代码示例在 autoexec 宏中或启动窗体 form_open 事件中:
• | 当您使用 autoexec, 调用 createdsnconnection 函数, 并然后传递参数, 如以下所示从 runcode 操作。
|
• | 当您使用启动窗体, 将代码, 它类似于以下以 form_open 事件。
|
请 createtabledef 方法, 有关访问下列 microsoft developer network (msdn) web 站点:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/f1/d2/s5a289.asp (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/f1/d2/s5a289.asp)
有关 registerdatabase 方法, 请访问以下 msdnweb 站点:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/f1/d2/s5a2ea.asp (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/f1/d2/s5a2ea.asp)
上一篇: 科普:俄太空机器人“何许人也”
下一篇: ASP实现URL编码