473,698 Members | 2,217 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Setting DSN programmaticall y ?

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 programmaticall y. For example,
some login form with neccessary informations ?
Thanks.
Nov 13 '05 #1
11 10272
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, strSourceTableN ame As String
Dim objTableDef As TableDef

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

'Reset connections for linked tables
For Each TableDef In CurrentDb.Table Defs
If TableDef.Connec t <> "" Then
'Get the table name and source table name
strTableName = TableDef.Name
strSourceTableN ame = TableDef.Source TableName

'Recreate the tabledef
Set objTableDef = New TableDef
With objTableDef
.Name = strTableName & "Temp"
.SourceTableNam e = strSourceTableN ame
.Connect = "ODBC;" & strConnect
End With
CurrentDb.Table Defs.Append objTableDef

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

'Change the name of the new tabledef to that of the old
tabledef
CurrentDb.Table Defs(strTableNa me & "Temp").Nam e =
strTableName

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

Nov 13 '05 #2
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********@net scape.net> je napisao u poruci interesnoj
grupi:11******* *************** @g49g2000cwa.go oglegroups.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, strSourceTableN ame As String
Dim objTableDef As TableDef

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

'Reset connections for linked tables
For Each TableDef In CurrentDb.Table Defs
If TableDef.Connec t <> "" Then
'Get the table name and source table name
strTableName = TableDef.Name
strSourceTableN ame = TableDef.Source TableName

'Recreate the tabledef
Set objTableDef = New TableDef
With objTableDef
.Name = strTableName & "Temp"
.SourceTableNam e = strSourceTableN ame
.Connect = "ODBC;" & strConnect
End With
CurrentDb.Table Defs.Append objTableDef

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

'Change the name of the new tabledef to that of the old
tabledef
CurrentDb.Table Defs(strTableNa me & "Temp").Nam e =
strTableName

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

Nov 13 '05 #3
Zlatko,

I don't know if you can programmaticall y 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

Nov 13 '05 #4
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********@net scape.net> je napisao u poruci interesnoj
grupi:11******* **************@ o13g2000cwo.goo glegroups.com.. .
Zlatko,

I don't know if you can programmaticall y 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

Nov 13 '05 #5
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

Nov 13 '05 #6
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
programmaticall y.
<bi********@net scape.net> je napisao u poruci interesnoj
grupi:11******* *************** @f14g2000cwb.go oglegroups.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

Nov 13 '05 #7
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

Nov 13 '05 #8
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********@net scape.net> je napisao u poruci interesnoj
grupi:11******* *************** @g47g2000cwa.go oglegroups.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


Nov 13 '05 #9
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_Gene ral_CP1_CI_AS NOT NULL ,
[Type] [nvarchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[SpecialEvent] [nvarchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_Gene ral_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

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
4554
by: dave | last post by:
I am sending an email from an asp.net application. I have set the from address a b@b.com but would like the name of this user to show up as Big Bob. How can i tell .net email object that when the user gets the email i want the from person to be Big Bob. In exchange the display name for this user is Bob Brown. Currently users see Bob Brown as the from name field. Is this possible?
18
18395
by: Dixie | last post by:
Can I set the Format property in a date/time field in code? Can I set the Input Mask in a date/time field in code? Can I set the Format of a Yes/No field to Checkbox in code? I am working on a remote update of tables and fields and can't find enough information on these things. Also, how do you index a field in code?
1
1034
by: RF | last post by:
Hi All Experts, I trying to create an app/script to set local group policies on a machine. How can I achieve setting group policies programmatically without using any GUI. For example, I want to set the User Configuration --> Windows Settings --> Internet Explorer Maintenance --> Connection --> proxy settings, using a script or my own custom app.
3
1408
by: Nadav | last post by:
Hi, I have a collection of files needed their version to be set, This should be done programmatically, I wonder... is there any existing API that enable modification/addition of version to an existing binary? It is possible to parse the '.rsrc' section manually, however, I am trying to avoid this, any samples, pointers or advice would be appreciated. -- Nadav
2
2713
by: Anil | last post by:
Hello, I am using VS2005 Beta2 and I have added a Simple Line-Graph chart to a ReportViewer control. I can click on the chart and alter for example the Minimum and Maximum scale values of the Y-axis. The question I have is that, can I change these values from within my code and how?
6
11074
by: | last post by:
I have made some user controls with custom properties. I can set those properties on instances of my user controls, and I have programmed my user control to do useful visual things in response to how those properties are set. I want to be able to do two other things: a) add User control instances to my page, filling in the place of placeholder controls, and b) programmatically setting custom properties on those dynamically spawned...
0
1274
by: lordcenon | last post by:
hi, to all... i need a little help. i am using vb5 and vb6, i'd like to use higher versions but economically not able to yet at the moment, so my question evolves around only for these 2 versions... i'd like to know how to change my browser's (IE or firefox) setting for a proxy server and its port programmatically using visual basic? many thanks for any assistance i can get... more power to all :)
0
2173
by: RKT | last post by:
I have a DataGridView bound to an MS Access table. This is a single- user application. When the User is adding or editing a row, the User may click on a Control elsewhere. That Control has context which I use to programmatically set *other* r/o cell values for the currently selected grid row. So, after that click on another control, and stuffing the related cell values, I want to seamlessly allow the user to resume editing/adding that row...
6
10112
by: alun65 | last post by:
I'm having trouble programmatically adding a user control and then setting some of it's server controls. I add the user control to the code behind and add it to a placeholder: protected void Page_Load(object sender, EventArgs e) { UserControls_WebUserControl myControl = new UserControls_WebUserControl(); PlaceHolder1.Controls.Add(web);
2
2670
by: Ronald S. Cook | last post by:
In my solution, I have a client (Windows app) and a class library. The class library project has a connection string setting that I would like to be able to change (from the client project ultimately). However, when I try to change the setting programmatically, it says it can't because it's read-only. This is not the case when I want to change a setting in the client project. Any idea what's up? If it matters, the class library has...
0
8673
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8601
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9156
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9021
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8892
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8860
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7716
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6518
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4614
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.