以编程方式设置DSN？ [英] Setting DSN programmatically ?

问题描述

您好。

我有一个使用PostgreSQL数据库的MS Access前端。

我已经成功创建了保存的文件DSN。我的paa-through查询是

引用该文件以及链接表。

但我不明白当我将前端发布到我的前端时会发生什么

其他一些PC。 Access如何知道File DSN在哪里？

有没有办法以编程方式设置连接。例如，

一些带有必要信息的登录表单？

谢谢。

Hello.
I have a MS Access front-end working with PostgreSQL database.
I have successfully created saved File DSN. My paa-through queries are
referring to that file as well as linked tables.
But I don''t understand what will happen when I distribute my front-end to
some other PC. How will Access know where is File DSN ?
Is there any way that I set the connection programmatically. For example,
some login form with neccessary informations ?
Thanks.

推荐答案

I仅使用与SQL Server，Oracle和其他

数据库的无DSN连接。它使事情变得更容易，更便携。

下面是一些示例代码，可用于重置

传递查询和链接表。你应该在启动时运行

的应用程序。


传递查询简单快速重置。链接的

表格有点复杂，每次打开应用程序时都必须重新创建。否则，当您开始使用该应用程序时，可能会提示您输入密码

。我没有删除每个

tabledef，然后重新创建它，而是制作了tabledef的副本，并将

附加到名称temp一词。这样，如果出现错误

并且您无法创建副本（即，无法连接到

数据库服务器）那么您就不要不要丢失原来的tabledef。


比尔E.

好​​莱坞，佛罗里达

-------- -------------------------------------------------- ---

Public Const strConnect ="在这里设置连接字符串


函数SetConnections（）

错误GoTo捕手

Dim strTableName，strSourceTableName As String

Dim objTableDef As TableDef


''重置所有传递查询的连接属性

每个QueryDef在CurrentDb.QueryDefs中

如果QueryDef.Connect<> ""然后

QueryDef.Connect =" ODBC;" &安培; strConnect

结束如果

下一页


''重置链接表的连接

For Each TableDef在CurrentDb.TableDefs

如果TableDef.Connect<> ""然后

''获取表名和源表名

strTableName = TableDef.Name

strSourceTableName = TableDef.SourceTableName


''重新创建tabledef

设置objTableDef = New TableDef

使用objTableDef

.Name = strTableName& Temp

.SourceTableName = strSourceTableName

.Connect =" ODBC;" &安培; strConnect

结束

CurrentDb.TableDefs.Append objTableDef


''如果新的tabledef已成功创建，请删除< br $>
old tabledef

CurrentDb.TableDefs.Delete strTableName


''将新tabledef的名称更改为旧的< br $>
tabledef

CurrentDb.TableDefs（strTableName&" Temp"）。姓名=

strTableName


结束如果

下一页

设置objTableDef =没什么

退出功能

Trapper：

MsgBox设置与SQL Server数据库的连接时出错

退出功能

结束功能

I use only DSN-less connections with SQL Server, Oracle and other
databases. It makes things so much easier and so much more portable.
Below is a bit of sample code that you can use to reset your
pass-through queries and linked tables. You should run it on startup
of the application.

The pass-through queries are simple and quick to reset. The linked
tables are a bit more complicated and must be recreated each time you
open the application. Otherwise, you may be prompted for passwords
when you begin to use the application. Instead of deleting each
tabledef and then recreating it, I make a copy of the tabledef and
append to the name the word "temp". In this way, if the an error
occurs and you can''t create the copy (i.e., can''t connect to the
database server) then you don''t lose your original tabledef.

Bill E.
Hollywood, FL
-------------------------------------------------------------
Public Const strConnect= "Set your connection string here"

Function SetConnections()
On Error GoTo Trapper
Dim strTableName, strSourceTableName As String
Dim objTableDef As TableDef

''Reset the connection property for all pass through queries
For Each QueryDef In CurrentDb.QueryDefs
If QueryDef.Connect <> "" Then
QueryDef.Connect = "ODBC;" & strConnect
End If
Next

''Reset connections for linked tables
For Each TableDef In CurrentDb.TableDefs
If TableDef.Connect <> "" Then
''Get the table name and source table name
strTableName = TableDef.Name
strSourceTableName = TableDef.SourceTableName

''Recreate the tabledef
Set objTableDef = New TableDef
With objTableDef
.Name = strTableName & "Temp"
.SourceTableName = strSourceTableName
.Connect = "ODBC;" & strConnect
End With
CurrentDb.TableDefs.Append objTableDef

''If the new tabledef was successfully created, delete the
old tabledef
CurrentDb.TableDefs.Delete strTableName

''Change the name of the new tabledef to that of the old
tabledef
CurrentDb.TableDefs(strTableName & "Temp").Name =
strTableName

End If
Next
Set objTableDef = Nothing
Exit Function
Trapper:
MsgBox "Error setting connections to SQL Server Database"
Exit Function
End Function

您好，感谢您的回答和代码。

我成功实现了您的代码并将其与登录表单相结合，本地

表存储用户名和密码，以及创建公共代码使用的公共变量strConnection的函数s。

但是，我的一些表存在问题。也就是说，我在链接表时经历了很多问题，主要是使用＃Deleted＃;当Access没有数字或时间戳主键时，会发生

的现象。
评估记录是否已被更改。因此我把数字

自动增量字段以及时间戳字段并禁用自动

识别主键以便我可以告诉Access要考虑哪个字段

作为主键。我在链接过程中手动完成了它并且工作正常。

现在，你的代码在每次启动时自动重新链接表，所以现在我再次有了这样的问题。

我怎么能解决它？

有没有办法告诉Access通过代码将哪些字段作为

主键？ Obviosly Access无法通过

本身正确识别主键。

谢谢。


Zlatko

< bi ******** @ netscape.net> je napisao u poruci interesnoj

grupi：11 ********************** @ g49g2000cwa.googleg roups.com ...

Hello and thanks for your answer and the code.
I succesfully implemented your code and combined it with login form, local
table that stores username and password and a function that creates public
variable strConnection which your code uses.
But, there is a problem with some of my tables. Namely, I have experienced a
lot of problem with linking tables, mostly with "#Deleted#" phenomena which
happens when Access doesn''t have numeric or timestamp primary key to
evaluate whether a record has been changed. Therefore I put numeric
autoincrement field as well as a timestamp field and disabled automatic
recognition of primary key so that I can tell Access which field to consider
as primary key. I have done it manually during linking and it worked.
Now, your code relinks tables automatically on every startup, so now I have
such problems again.
How could I solve it ?
Is there a way how to tell Access through code which fields to take as
primary key ? Obviosly Access can''t recognize primary key correctly by
itself.
Thanks.

Zlatko
<bi********@netscape.net> je napisao u poruci interesnoj
grupi:11**********************@g49g2000cwa.googleg roups.com...

我只使用与SQL Server，Oracle和其他数据库的无DSN连接。它使事情变得更容易，更便携。
下面是一些示例代码，可用于重置
传递查询和链接表。你应该在应用程序的启动时运行它。

传递查询很简单，快速重置。链接的表格有点复杂，每次打开应用程序时都必须重新创建。否则，当您开始使用该应用程序时，可能会提示您输入密码。我没有删除每个
tabledef然后重新创建它，而是制作了tabledef的副本，并在名称中附加了temp一词。这样，如果发生错误
并且您无法创建副本（即，无法连接到数据库服务器），那么您不会丢失原始tabledef。

比尔E.
好莱坞，佛罗里达
---------------------------- ---------------------------------
Public Const strConnect ="在此处设置您的连接字符串

函数SetConnections（）
错误GoTo Trapper
Dim strTableName，strSourceTableName As String
Dim objTableDef As TableDef

''重置连接所有传递查询的属性
对于CurrentDb.QueryDefs中的每个QueryDef
如果QueryDef.Connect<> ""然后是QueryDef.Connect =" ODBC;" &安培; strConnect
结束如果

''重置链接表的连接
对于每个TableDef在CurrentDb.TableDefs
如果TableDef.Connect<> ""然后
''获取表名和源表名
strTableName = TableDef.Name
strSourceTableName = TableDef.SourceTableName

''重新创建tabledef
设置objTableDef = New TableDef
使用objTableDef
.Name = strTableName& Temp
。SourceLableName = strSourceTableName
.Connect =" ODBC;" &安培; strConnect
结束
CurrentDb.TableDefs.Append objTableDef

''如果新的tabledef已成功创建，请删除
旧tabledef
CurrentDb.TableDefs .Delete strTableName

''将新tabledef的名称更改为旧的
tabledef的名称
CurrentDb.TableDefs（strTableName&" Temp"）。Name =
strTableName

结束如果
下一步
设置objTableDef = Nothing
退出函数
捕手：
MsgBox"错误设置连接到SQL Server数据库
退出函数
结束函数

I use only DSN-less connections with SQL Server, Oracle and other
databases. It makes things so much easier and so much more portable.
Below is a bit of sample code that you can use to reset your
pass-through queries and linked tables. You should run it on startup
of the application.

The pass-through queries are simple and quick to reset. The linked
tables are a bit more complicated and must be recreated each time you
open the application. Otherwise, you may be prompted for passwords
when you begin to use the application. Instead of deleting each
tabledef and then recreating it, I make a copy of the tabledef and
append to the name the word "temp". In this way, if the an error
occurs and you can''t create the copy (i.e., can''t connect to the
database server) then you don''t lose your original tabledef.

Bill E.
Hollywood, FL
-------------------------------------------------------------
Public Const strConnect= "Set your connection string here"

Function SetConnections()
On Error GoTo Trapper
Dim strTableName, strSourceTableName As String
Dim objTableDef As TableDef

''Reset the connection property for all pass through queries
For Each QueryDef In CurrentDb.QueryDefs
If QueryDef.Connect <> "" Then
QueryDef.Connect = "ODBC;" & strConnect
End If
Next

''Reset connections for linked tables
For Each TableDef In CurrentDb.TableDefs
If TableDef.Connect <> "" Then
''Get the table name and source table name
strTableName = TableDef.Name
strSourceTableName = TableDef.SourceTableName

''Recreate the tabledef
Set objTableDef = New TableDef
With objTableDef
.Name = strTableName & "Temp"
.SourceTableName = strSourceTableName
.Connect = "ODBC;" & strConnect
End With
CurrentDb.TableDefs.Append objTableDef

''If the new tabledef was successfully created, delete the
old tabledef
CurrentDb.TableDefs.Delete strTableName

''Change the name of the new tabledef to that of the old
tabledef
CurrentDb.TableDefs(strTableName & "Temp").Name =
strTableName

End If
Next
Set objTableDef = Nothing
Exit Function
Trapper:
MsgBox "Error setting connections to SQL Server Database"
Exit Function
End Function

Zlatko，


我不知道你是否可以在链接的

表中以编程方式设置索引。我做了一个快速的实验并且无法做到。我从来没有这么做过
。


您是否链接到表格或视图？当您链接到表时，表的

主键应该反映在创建的

链接中 - Access（或ODBC）确实识别出这一点。另一方面，如果你链接到一个视图，你可能会被提示选择一个唯一的标识符。


你似乎在说那个主要的标识符源表中的键是

不足。有没有理由你不能改变它？


我不熟悉＃Deleted＃现象，因为它与链接相关

表。但是，我认为重新查询你的表单/子表单

会解决这个问题。


如果你可以在表格上创建一个索引视图，也许你可以按照你需要的方式设置

索引并链接到索引视图而不是

表。通过这种方式，您将自动获得

链接中的正确索引。


我不经常使用链接表。也许有人更有经验

这个可以帮忙。


比尔

Zlatko,

I don''t know if you can programmatically set the index in the linked
table. I did a quick experiment and was not able to do it. I''ve never
needed to do this.

Are you linking to tables or views? When you link to a table, the
primary key of your table should be reflected in the created
link--Access (or ODBC) does recognize this. On the other hand, if you
link to a view, you may be prompted to choose a unique identifier.

You seem to be saying that the primary key in the source table is
inadequate. Is there a reason why you can''t you change it?

I''m not familiar with the #Deleted# phenomenon as it related to linked
tables. However, i would think that a requery of your form/subform
would take care of this.

If you can create an indexed view on the table, perhaps you can set the
index the way you need it and link to the indexed view instead of the
table. In this way, you would automatically get the correct index in
the link.

I don''t use linked tables very often. Perhaps someone more experienced
with this can help out.

Bill

这篇关于以编程方式设置DSN？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！