Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access 2007 front end to SS2K8 problem


Access 2007 front end to SS2K8 problem

Author
Message
valeryk2000
valeryk2000
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 412
My app includes
1) SS2K8 database on the back end
2) Access 2007 front end NOT LINKED, data flows through ADO and stored procedures on the back end.
=================
Front End consists of forms/subforms and proxy tables "connected' to subforms.
Data flows that way:
Sql server table <===> Access proxy table <===> subform
==============
Now a fun part begins:
user opens the form (with continuous subforms populated with items) and just sits back doing nothing. Then suddenly records disappear from the subform with scary "=deleted" on the items a second ago having meaningful data. Can you imagine how happy the user can be?
I checked the underlying table - empty
==================
After reloading the Access form all data comes back (SQL Server table was not changed)

What? Why? No idea

Val
WendellB
WendellB
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1634
That's quite an unusual design approach to creating a front-end to SQL Server. In general the recommendation is to either use an ADP front-end, although support in future versions is questionable, or to link the SQL Server tables using ODBC. Several questions:
What format is your Access front-end - accdb or mdb?
How are your main and subforms linked?
Also are both the main form and the subforms linked to the proxy table? If so it sounds like the proxy table is being cleared by something.
Is this an existing application that has been running for some time or is it a new one?

Wendell
Evergreen, CO

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
valeryk2000
valeryk2000
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 412
ok.
1. This is mdb
2. Subform are just imbedded into main form
3. Only subform is linked to the proxy table (because the table feeds continuous subfom) Other controls (dropdowns, textboxes) do not need a proxy table and are populated directly from SQL Server
4. It is a new application. During testing we did not see that problem.

================================
I just tried this:
a. I open an instance of the mdb on ONE computer. Data on subform is ok - not deleted.
b. I opened 2 instances of the same mdb file on 2 different computers. It takes 15 - 40 sec for the data on subform of the first computer to loose the data, the second computer subform stable.
c. I copied the mdb to two different locations, and opened each of them on two different computers. No problem at all, subforms behave!

Hence: two instances of the same mdb somehow (I do not know!!) communicate with each other and kicks the data out of each others proxy tables.

(F. Chopin, Funeral march ...)
WendellB
WendellB
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1634
Well, I'm afraid I'm not much help at this stage. I've not worked with "proxy tables" at all either in Access or SQL Server - from web research they appear to used primarily with SyBase rather than SQL Server. But the fact that you are able to get to work on some PCs but not on others suggests to me that either the network connection to one or more of the PCs is flakey, or that you have software differences in what used to be called the MDAC package. Are all workstations running the same OS, and do they all have SP3 for Office 2007?

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
ProofOfLife
ProofOfLife
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 9474
Hi Val

To understand your problem I need clarification on a couple of things. I have experienced linked tables causing forms to display #Deleted, but not the data disappearing from the form. The #Deleted is well documented on the net and generally relates to keys/indexes.

Firstly, what exactly do you mean by a proxy table? Are these just links to SQL Server created using ODBC or some form of (pass-through) query?

Secondly, do you users access a common fron end or do they all have seperate copies? I guess what I'm getting at here is if they are all using the same front-end, then one user may be upseting the data source of another. If you are using ODBC links this will be unlikely.

Cheers

Rowan
valeryk2000
valeryk2000
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 412
Rowan:
==> Firstly, what exactly do you mean by a proxy table? Are these just links to SQL Server created using ODBC or some form of (pass-through) query?
=====
There is no LINKED tables in this Access front end app. There is no pass-through queries. SQL stored procedure called from Access (by ADO connection/command) would open a recordset that populates local ("proxy") Access table that is linked to continuous subform.

==> Secondly, do you users access a common fron end or do they all have seperate copies? I guess what I'm getting at here is if they are all using the same front-end, then one user may be upseting the data source of another. If you are using ODBC links this will be unlikely.
====
Users are using ONE Access file from the share drive to open an instance of it on their computers.

Question regarding ODBC linked tables: When I try this I can only READ the data, not WRITE. Is it possible to do both on linked tables?
WendellB
WendellB
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1634
Regarding your question about linked tables, and whether they can be written to, absolutely yes. We have a number of systems deployed that used linked tables that are updateable. In fact, we also link to views which are updateable. ODBC linked tables are the preferred method for using an Access front-end to SQL Server tables per Microsoft. If you are seeing the tables as read-only, chances are your userID in SQL Server doesn't have permissions to edit or append data.

And regarding the shared front-end, that is generally considered dangerous. We always deploy the front-end to the user PC - that way if the front-end is somehow corrupted, we can simply replace the one on the PC. Otherwise, you have to get everyone out of the system so you can repair whatever has gone wrong. We learned that the hard way with some 80 PCs trying to share a front-end. It was a disaster, and we quickly changed to copying the front-end to the PC. That was in 1994. I think you will find that concept in nearly every best practice - if you want a bit more detail take a look at http://www.access-experts.com/default.aspx?selection=TutorialSplitDB&sm=18

Wendell
Evergreen, CO

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
valeryk2000
valeryk2000
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 412
Thanks.
==> If you are seeing the tables as read-only, chances are your userID in SQL Server doesn't have permissions to edit or append data.

But there is no problem for me to get to SQL Server through current Acces front end. May be there is something that I need to change on the Access side? permissions on each table, or else? Please give me more practical details
===========

==> And regarding the shared front-end, that is generally considered dangerous. We always deploy the front-end to the user PC - that way if the front-end is somehow corrupted, we can simply replace the one on the PC.

Yes. That's what we found as well. Or create folders for each user (we have only 4, but all of them are VIPs, e.g. hysterical Smile on the share drive so they could enjoy there own file - in this case we do not need to go to each computer: just substitue old files with a new one

Thanks again
WendellB
WendellB
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 1634
valeryk2000 (8/30/2012)
Thanks.
==> If you are seeing the tables as read-only, chances are your userID in SQL Server doesn't have permissions to edit or append data.

But there is no problem for me to get to SQL Server through current Acces front end. May be there is something that I need to change on the Access side? permissions on each table, or else? Please give me more practical details
===========

In order to connect to SQL Server tables you must specify a specific login name - either SQL Server based, or Integrated Security (which I much prefer). Any user who wants to be able to edit data in a table must either be given explicit permission on that table, or they must belong to a group that has that ability. One solution we use is to make them a member of the dbOwner group, but that does have some downsides, as they can do pretty much anything they want to the entire database.

On the Access side, unless you activate Access User Security, there is no permissions to set. (By default you connect as "Admin" and that user can do anything they want to the database.) However, the SQL Server table must have a primary key set if you link to it and want to edit it. Otherwise, Access treats it as read-only. The same is true of local tables (including temporary ones) unless you explicity create a primary key when you create them.

Hope that helps.

Wendell
Evergreen, CO

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
valeryk2000
valeryk2000
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 412
OK. Let me try it
Thanks a lot
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search