Setting DSN programmatically ?

[Zlatko Matiæ]

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.

[billmiami2]

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 Matic]

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...

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

[billmiami2]

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

[Zlatko Matic]

Hello. Thank you for answer.
How do you mean indexed view ? A JET view on linked table, or a view on the
server?
Greetings,

Zlatko

<bi********@netscape.net> je napisao u poruci interesnoj
grupi:11*********************@o13g2000cwo.googlegr oups.com...

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

[billmiami2]

An indexed view on the server. However, the best method is to set your
indexes and primary key properly on the table on the database server.
Your ODBC table links should bring the table over exactly with no
adjusting of primary keys necessary.

Bill

[Zlatko Matic]

Unfortunately, it seems to be inaccurate in some cases, and even if Access
recognizes primary indexes correctly it is not enough for updating tables.
For example, I used natural indexes on text (varchar) fields in some server
tables. Access can't manage such primary keys on linked tables. It must be
numeric or timestamp field, otherwise there apears "#Deleted#" phenomena in
linked tables. There is an article on MSDN about it...Therefore I "cheated"
Access during linking by telling him not to recognize indexes by itself so I
can tell him for some autonumber field to be primary key (although it is not
primary key on the server)...
The problem is that I don't know to do the same "cheating" while relinking
programmatically.
<bi********@netscape.net> je napisao u poruci interesnoj
grupi:11**********************@f14g2000cwb.googleg roups.com...

An indexed view on the server. However, the best method is to set your
indexes and primary key properly on the table on the database server.
Your ODBC table links should bring the table over exactly with no
adjusting of primary keys necessary.

Bill

[billmiami2]

Zlatko,

I was unaware of these limitations on ODBC links. Like I said, I don't
use linked tables very often.

Do these #Deleted# messages appear when you delete a record in a
continous form?

Can you give me a link to the MSDN article? I would like to understand
this better.

Thanks,

Bill

[Zlatko Matiæ]

Hi.

This is the link:
http://support.microsoft.com/kb/q128809/#toc

The article says the following:

"
ACC: "#Deleted" Errors with Linked ODBC Tables
View products that this article applies to.
Article ID : 128809
Last Review : May 6, 2003
Revision : 1.0

This article was previously published under Q128809
On this page
SYMPTOMS
CAUSE
RESOLUTION
MORE INFORMATION
Steps to Reproduce Behavior
APPLIES TO

SYMPTOMS
When you retrieve, insert, or update records in a linked ODBC table, each
field in a record contains the "#Deleted" error message. When you retrieve,
insert, or update records using code, you receive the error message "Record
is deleted."
Back to the top

CAUSE
The Microsoft Jet database engine is designed around a keyset-driven model.
This means that data is retrieved, inserted, and updated based on key values
(in the case of a linked ODBC table, the unique index of a table).

After Microsoft Access performs an insert or an update of a linked ODBC
table, it uses a Where criteria to select the record again to verify the
insert or update. The Where criteria is based on the unique index. Although
numerous factors can cause the select not to return any records, most often
the cause is that the key value Microsoft Access has cached is not the same
as the actual key value on the ODBC table. Other possible causes are as
follows:
. Having an update or insert trigger on the table, modifying the key
value.
. Basing the unique index on a float value.
. Using a fixed-length text field that may be padded on the server
with the correct amount of spaces.
. Having a linked ODBC table containing Null values in any of the
fields making up the unique index.
These factors do not directly cause the "#Deleted" error message. Instead,
they cause Microsoft Access to go to the next step in maintaining the key
values, which is to select the record again, this time with the criteria
based on all the other fields in the record. If this step returns more than
one record, Microsoft Access returns the "#Deleted" message because it does
not have a reliable key value to work with. If you close and re-open the
table or choose Show All Records from the Records menu, the "#Deleted"
errors are removed.

Microsoft Access uses a similar process to retrieve records from an linked
ODBC table. First, it retrieves the key values and then the rest of the
fields that match the key values. If Microsoft Access is not able to find
that value again when it tries to find the rest of the record, it assumes
that the record is deleted.
Back to the top

RESOLUTION
The following are some strategies that you can use to avoid this behavior:
. Avoid entering records that are exactly the same except for the
unique index.
. Avoid an update that triggers updates of both the unique index and
another field.
. Do not use a Float field as a unique index or as part of a unique
index because of the inherent rounding problems of this data type.
. Do all the updates and inserts by using SQL pass-through queries so
that you know exactly what is sent to the ODBC data source.
. Retrieve records with an SQL pass-through query. An SQL pass-through
query is not updateable, and therefore does not cause "#Delete" errors.
. Avoid storing Null values within any field making up the unique
index of your linked ODBC table.

Back to the top

MORE INFORMATION
Note: In Microsoft Access 2.0, linked tables were called attached tables.
Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb (or NWIND.MDB. in Microsoft
Access 2.0)
2. Use the Upsizing Tools to upsize the Shippers table.

NOTE: This table contains an AutoNumber field (or Counter field in
Microsoft Access 2.0) that is translated on SQL Server by the Upsizing Tools
into a trigger that emulates a counter.
3. Open the linked Shippers table and enter a new record. Make sure
that the record you enter has the same data in the Company Name field as the
previous record.
4. Press TAB to move to a new record. Note that the "#Deleted" error
fills the record you entered.
5. Close and re-open the table. Note that the record is correct.

Back to the top

"

In my experience, this occurs not when deleting records but when uldating or
adding records.
I have xeperienced a lot of problem with this phenomena.
Also, there is a problem that Access sometimes doesn't recognize primary key
correctly. It recognize wrong column to be primary key. I don't understand
why...

Greetings,

Zlatko

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

Zlatko,

I was unaware of these limitations on ODBC links. Like I said, I don't
use linked tables very often.

Do these #Deleted# messages appear when you delete a record in a
continous form?

Can you give me a link to the MSDN article? I would like to understand
this better.

Thanks,

Bill

[billmiami2]

Zlatko,

Thank you for the article and link.

This article does not mention that your table must have a numeric or
timestamp column as your primary key. In fact, I did an experiment
using the following SQL Server table which uses a date column and a
nvarchar column as its primary key.

CREATE TABLE [dbo].[tblVoyage] (
[Date] [smalldatetime] NOT NULL ,
[Shift] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SpecialEvent] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Points] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblVoyage] WITH NOCHECK ADD
CONSTRAINT [PK_tblVoyage] PRIMARY KEY CLUSTERED
(
[Date],
[Shift]
) ON [PRIMARY]
GO

Some sample data is as follows:
Date Shift Type SpecialEvent Notes Points
5/8/2005 AM Null Null Null 10
5/8/2005 PM Null Null Null 10
5/9/2005 AM Null Null Null 10
5/9/2005 PM Null Null Null 10
5/10/2005 AM Null Null Null 10

I opened a new Access database and created a linked table. The primary
key on the linked table was automatically created for the Date and
Shift columns which is correct. I created a continuous form based on
the linked table and was able to insert, update and delete perfectly
with no #DELETE# error messages.

What is the structure of the PostGre SQL table that is giving you
problems? What is its primary key?

I believe also that there may be a problem with the ODBC driver for
PostGre SQL. You may want to look at some of the newsgroups related to
postgre for help. If, in the end, you can't resolve this problem using
ODBC linked tables, you may need to use another approach to interacting
with your PostGre database like using pass throughs and ADODB.

Bill

[Zlatko Matic]

Hello.
I still don't know what was the reason for my problems with "#Deleted#" and
indexes, but fortunately, the error dissapeared after recreating the
database tables on the PostgreSQL server. I just had to duplicate tables,
then delete old tables and rename new tables to previos names. Now Access
recognizes all indexes correctly (!?).
Maybe the problem was with previous version of Postgre...I don't know.
Now, everything seems to be OK...
Anyway, thank you for help.
Best regards,

Zlatko

<bi********@netscape.net> je napisao u poruci interesnoj
grupi:11*********************@g43g2000cwa.googlegr oups.com...

Zlatko,

Thank you for the article and link.

This article does not mention that your table must have a numeric or
timestamp column as your primary key. In fact, I did an experiment
using the following SQL Server table which uses a date column and a
nvarchar column as its primary key.

CREATE TABLE [dbo].[tblVoyage] (
[Date] [smalldatetime] NOT NULL ,
[Shift] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Type] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SpecialEvent] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Points] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblVoyage] WITH NOCHECK ADD
CONSTRAINT [PK_tblVoyage] PRIMARY KEY CLUSTERED
(
[Date],
[Shift]
) ON [PRIMARY]
GO

Some sample data is as follows:
Date Shift Type SpecialEvent Notes Points
5/8/2005 AM Null Null Null 10
5/8/2005 PM Null Null Null 10
5/9/2005 AM Null Null Null 10
5/9/2005 PM Null Null Null 10
5/10/2005 AM Null Null Null 10

I opened a new Access database and created a linked table. The primary
key on the linked table was automatically created for the Date and
Shift columns which is correct. I created a continuous form based on
the linked table and was able to insert, update and delete perfectly
with no #DELETE# error messages.

What is the structure of the PostGre SQL table that is giving you
problems? What is its primary key?

I believe also that there may be a problem with the ODBC driver for
PostGre SQL. You may want to look at some of the newsgroups related to
postgre for help. If, in the end, you can't resolve this problem using
ODBC linked tables, you may need to use another approach to interacting
with your PostGre database like using pass throughs and ADODB.

Bill

[billmiami2]

Zlatko,

I'm glad to hear that you solved the problem. Note that other users
have recently asked for help with Postgre on this newsgroup. Perhaps
you can give them a hand.

Bill E.
Hollywood, FL