SQL Service Pack with MS Access

Question

SQL Service Pack with MS Access

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I'm not sure but that can't help the server... in any way. Maybe that would be enough of a boost to save the day, but I doubt it.

Christi SSC-Addicted Points: 402 More actions · Answer 2

Thank you for you input! I really appreciate the tip.

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 3

Once again, Remi is absolutely correct about the timestamps. In my apps, EVERY table has a timestamp and a single unique primary key. As you have just proven, ff you don't have a time stamp, Access must check EVERY SINGLE COLUMN to make sure the data has not changed in the interim. Timestamps should be considered mandatory for all databases, and not just for Access clients.

Also, I seem to remember an issue like yours discussed in one of the zillions of KB articles MS has published since SP3. I would suggest taking some time and going through the long list of recent KB article titles.

HTH,

Rich

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 4

Try this KB site search and see what might help you:

http://www.kbalertz.com/sresults.aspx?sw=sql+server+2000+sp3&st=1

Good luck...

Rich

Christi SSC-Addicted Points: 402 More actions · Answer 5

Thank you Richard! I will go through them. I appreciate your input

I think I will just push to have our company get out of MS Access. It just does not make sense that all of our other applications can run on sp3(a) and not have issues, yet when it's tied with Access it does.

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 6

"I think I will just push to have our company get out of MS Access."

IMO, I would say that is the wrong response. I would rather say that you need better education for your Access programmers, though. As Remi has pointed out, there are some seriously bad design flaws in your app (including your SQL Server tables), that should have been found with testing very early on. Every SQL Server app (access or otherwise) should make heavy use of the Profiler to determine how the client interacts with the server. Especially with Access, since it generates so much complex parameterized SQL under the covers.

A general principal about Access is that is allows really poor "developers" to create really poor apps that actually work anyway, most of the time.

However, it also allows good Access developers to create really great apps very quickly. A good Access ADP developer MUST also be proficient in SQL Server development. That combination seems to be rather uncommon, unfortunately.

Caveat emptor.

For what it's worth (not much, unfortunately) I have no problem with SP3a and my 400+ table Access 2002 ADP.

Please let us know if you are able to track down a specific problem, other than the timestamp issue.

Good luck..

Rich

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

"A general principal about Access is that is allows really poor "developers" to create really poor apps that actually work anyway, most of the time."

I'm still rolling on the floor for this one. That's exactly what my company's software was all about .

Also I'd like to hear about those other caveats that you reffered to!!!

Christi SSC-Addicted Points: 402 More actions · Answer 8

Well, so much for the timestamp. I spoke with the developer and we added the timestamp column to the top 5% of our tables. In doing so, it made our updateable views non-updateable.

Is there any workaround for this?

We have well over 680 views,800 stored procs and 600 tables. Redesigning forms is not an option, there are hundreds of forms and subforms.

Just for the record, our developers are awesome...not adding a timestamp column does not make one a poor developer. Unless you know everything about programming I don't think it's very nice to leap to the conclusion that my team is a group of "poor" developers.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Sorry for the unforseen problem, I had never experienced it (using mostly sps here). Anyways I was able to recreate the problem like this.

1 - Create a view that refferences 2 tables.

2 - Make a form that updates the selected unique table (successfully).

3 - Add the timestamp to the unique table.

4 - Close/reopen access.

5 - Try to update again (fails this time).

6 - Add the timestamp to the view definition

7 - Close/reopen access

8 - Restest the update which works now.

Christi SSC-Addicted Points: 402 More actions · Answer 10

Just so I am following correctly, you have to add the timestamp column into the view?

Thanks for you input! I really appreciate it!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Seems logical... Access usually uses sp_help* to get the tables definition. If this info is not in the view, I don't see how it could use it. Maybe it fails because the view definition and table definition create some sort of conflict??

HTH.

Christi SSC-Addicted Points: 402 More actions · Answer 12

Also, can the view have 2 timestamps?

(some of our views use different tables for the update)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

That one I didn't test. You'll have to check it out and report back. I assume that the view can update only one table at the time... but it can be anyone of them at the time. So I guess you need to supply all Timestamps for the updatable tables.

Christi SSC-Addicted Points: 402 More actions · Answer 14

Christi

SSC-Addicted

Points: 402

September 7, 2005 at 12:00 pm

#588194

Thank you rqR'us. I will let you know how it goes.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

Actually it's RG, but I see the confusion with the underlining in the forum... problem solved now .