Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-13-2019, 03:52 PM   #16
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,238
Thanks: 15
Thanked 1,591 Times in 1,511 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Moving to SQL Server

I'm not blaming this on John because he was just trying to be helpful but this is right up there at the top of my list of worst and least helpful "help" entries of all time. Given that, I couldn't find any new entry that was any better.

MY understanding of the purpose of the TimeStamp/RowVersion is to allow the server to efficiently identify if a record has been updated since it was fetched without having to reread the entire record and compare each field to its copy of the "before" data values. In a multi-user environment, you have user1 who reads row1 and user2 who also reads row1. User2 is fast and updates row1 which changes the TimeStamp/RowVersion. When user1 gets around to applying his update, the server recognizes that someone else has updated the row since user1 retrieved it and so rejects the update.

I have also run into situations where TimeStamps were required but since I generally use them because it makes updates more efficient from Access, I only ran into an issue once and I'm thinking now it was because of a bit field or something else where SQL Server and Access are not quite in tune.

__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 09-13-2019 at 03:57 PM.
Pat Hartman is offline   Reply With Quote
Old 09-13-2019, 04:13 PM   #17
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,238
Thanks: 15
Thanked 1,591 Times in 1,511 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Moving to SQL Server

@Mackbear,
You are probably going to be flabbergasted when you convert your tables to SQL Server and find the app runs much slower than it did with Jet/ACE tables. Who knew? Jet/ACE are optimized to work with Access and are exceptionally fast so if you created your Access FE using "Access" techniques rather than client/server techniques, you WILL have to make FE changes. The biggest issue will be forms that are bound to tables or to queries that have no selection criteria. This technique forces Access to pull down the ENTIRE recordset to memory on the local PC where it can be filtered using form filters. If your tables have only a few thousand rows (you probably shouldn't be converting to SQL Server at this time), then you probably won't notice slowness. However, if you are converting because you have too much data to continue using Jet/ACE then the difference will be noticeable. The client/server technique is to always bind your forms to queries that include selection criteria that dramatically limits the rows returned. it is far more efficient to bring down 20 records, one at a time for the user to interact with than to bring down 200,000 and filter them locally to get to the few records that the user needs to access. Sometimes I create search forms if the search criteria can be extensive or complicated and the search form builds the WHERE clause and binds the query to a list form. The user then selects one record at a time from the list form to work with. In other cases, if there are only a couple of fields, I use static querydefs with WHERE clauses that reference the search fields on the form. These forms open empty. The use enters his criteria and then the single record he wants is retrieved. Access naturally "passes through" ALL queries so if your query requests 1 record, that is all the server will retrieve and return to you. This cuts down network traffic as well as reducing the memory requirements. Actual Pass thru queries are a good idea for bulk updates and bulk deletes because they do NOT run within a transaction so Access won't prompt you "100 records will be updated, do you want to continue?". It will just apply the update/delete. Pass through queries are slightly faster because there is no local overhead but not enough to switch to unbound forms and all that entails.

In some cases, you might find you have to create views to perform certain common joins to get some efficiency and in even rarer cases, you might need to create stored procedures to collect the data for complex reports. In NO case have I in 25+ years had to resort to using an unbound form for editable data. I did have one case where the search criteria was so convoluted that I did need a sp to populate the form so even though it was still technically bound, it wasn't updateable.

When I create apps, I always build them as if I might some day have to convert the BE to SQL Server. That means that when the time comes, I can do the conversion generally in under an hour as long as I don't have any conversion issues.
__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 09-13-2019 at 04:20 PM.
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Mackbear (10-10-2019)
Old 09-13-2019, 05:09 PM   #18
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,638
Thanks: 95
Thanked 1,498 Times in 1,413 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Moving to SQL Server

Quote:
Originally Posted by NauticalGent View Post
With that, Galaxiom was right (as usual) about alluding to me not “know(ing) what I was doing”.
When I said:
Quote:
Originally Posted by Galaxiom
I suspect adding the TimeStamp column is a fudge designed to avoid asking questions of Access users who don't know what they are doing.

Access has always catered for the lowest common denominator.
... my aim was not at anyone here, but at Microsoft for making their products guess what the user needs.

(Like trying to type " 2.3 cm" at the beginning of the line in Word and getting a numbered list automatically despite there not being 1.1 let alone a sensible sequence. Or what Excel assumes when entering values to columns formatted as Percentage.)

Access is set up with features that make it as simple and as accessible as possible which is what I meant by "lowest common denominator". However, sometimes its automatic decisions solve the problem but the outcomes might not necessarily be the optimum.

For example. Access VBA doesn't come with Option Explict on by default so the novice user doesn't need to think about variable declaration, despite the potential problems from not declaring variables. We all learnt that it should be on pretty quickly.

That is why I suspected TimeStamp might be a cure-all for linking problems that Microsoft directs the user to use because it doesn't require any knowledge or explanation.

Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
NauticalGent (09-13-2019)
Old 09-13-2019, 08:41 PM   #19
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,990
Thanks: 439
Thanked 295 Times in 256 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Moving to SQL Server

Thanks for the clarification G, guess I’m too defensive and sensitive at times.
__________________
- Ciao, John
NauticalGent is offline   Reply With Quote
Old 10-10-2019, 02:30 PM   #20
Mackbear
Newly Registered User
 
Join Date: Apr 2019
Posts: 144
Thanks: 72
Thanked 0 Times in 0 Posts
Mackbear is on a distinguished road
Re: Moving to SQL Server

Quote:
Originally Posted by Pat Hartman View Post
@Mackbear,
You are probably going to be flabbergasted when you convert your tables to SQL Server and find the app runs much slower than it did with Jet/ACE tables. Who knew? Jet/ACE are optimized to work with Access and are exceptionally fast so if you created your Access FE using "Access" techniques rather than client/server techniques, you WILL have to make FE changes. The biggest issue will be forms that are bound to tables or to queries that have no selection criteria. This technique forces Access to pull down the ENTIRE recordset to memory on the local PC where it can be filtered using form filters. If your tables have only a few thousand rows (you probably shouldn't be converting to SQL Server at this time), then you probably won't notice slowness. However, if you are converting because you have too much data to continue using Jet/ACE then the difference will be noticeable. The client/server technique is to always bind your forms to queries that include selection criteria that dramatically limits the rows returned. it is far more efficient to bring down 20 records, one at a time for the user to interact with than to bring down 200,000 and filter them locally to get to the few records that the user needs to access. Sometimes I create search forms if the search criteria can be extensive or complicated and the search form builds the WHERE clause and binds the query to a list form. The user then selects one record at a time from the list form to work with. In other cases, if there are only a couple of fields, I use static querydefs with WHERE clauses that reference the search fields on the form. These forms open empty. The use enters his criteria and then the single record he wants is retrieved. Access naturally "passes through" ALL queries so if your query requests 1 record, that is all the server will retrieve and return to you. This cuts down network traffic as well as reducing the memory requirements. Actual Pass thru queries are a good idea for bulk updates and bulk deletes because they do NOT run within a transaction so Access won't prompt you "100 records will be updated, do you want to continue?". It will just apply the update/delete. Pass through queries are slightly faster because there is no local overhead but not enough to switch to unbound forms and all that entails.

In some cases, you might find you have to create views to perform certain common joins to get some efficiency and in even rarer cases, you might need to create stored procedures to collect the data for complex reports. In NO case have I in 25+ years had to resort to using an unbound form for editable data. I did have one case where the search criteria was so convoluted that I did need a sp to populate the form so even though it was still technically bound, it wasn't updateable.

When I create apps, I always build them as if I might some day have to convert the BE to SQL Server. That means that when the time comes, I can do the conversion generally in under an hour as long as I don't have any conversion issues.
Thankss for this, i think this is how i already built my FE, everything is filtered according to what the user needs to work on, all forms are bound to queries. If this is the case then, do i simply import the tables to SQL and create linked tables to the FE?
Mackbear is offline   Reply With Quote
Old 10-10-2019, 07:45 PM   #21
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,990
Thanks: 439
Thanked 295 Times in 256 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Moving to SQL Server

Quote:
Originally Posted by Mackbear View Post
If this is the case then, do i simply import the tables to SQL and create linked tables to the FE?
Correct. Choose Export from the External section of then ribbon and choose ODBC and then follow along. You will need to determine what kind of DSN (File or Machine) you want to use - whole other topic there, I eventually went to DSN-less.

There is an excellent tutorial by Steve Bishop about migrating to SQL Server. Highly recommend. Here is a link the the first video and then you can go from there.

https://youtu.be/e9MMDDI1pUI

Best of luck!
__________________
- Ciao, John
NauticalGent is offline   Reply With Quote
Old 10-10-2019, 09:49 PM   #22
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,507
Thanks: 92
Thanked 1,670 Times in 1,551 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Moving to SQL Server

This is only a side comment, but here goes:

In SQL Server you have rowversion. In ORACLE (which is what we used for certain U.S. Navy Reserve databases) the same concept is called "instantiation number" and I believe it serves the same purpose as described in that article.

An idle DB doesn't change Instantiation Number at all. Among other things, if you were trying to recover a database from backups, your goal was to restore all tables to the same Instantiation Number because that means you had a "snapshot" of the data that was valid for the time interval during which the I.N. was that value.

Sounds easy, right? Wrong. I'll keep out of the weeds and simply tell you that if you are doing remote data mirroring and the host site goes down bad enough for the remote site to have to take over, it is often a nightmare to restore from backup to achieve a uniform Instantiation Number. I actually won a bet with the vendor of the replication system by predicting that their configuration would never achieve a valid restore-set. Got a freebie steak dinner from Cattleman's Restaurant in Fort Worth over that bet.

But the point of rowversion is often not merely to somehow support transactions but also to give you a reference "I.D." that would be your target for data recovery.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-11-2019, 12:37 AM   #23
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,326
Thanks: 40
Thanked 3,666 Times in 3,535 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Moving to SQL Server

Quote:
i think this is how i already built my FE, everything is filtered according to what the user needs to work on
Not sure if you have understood what Pat was saying or whether it is your termininology. Filtering occurs in forms after you have brought across the recordset. Pat is saying apply criteria before you bring across the recordset.

Note if you use the WHERE parameter for docmd.openform, this is actually a filter.

So instead of using the openform where parameter or in code something like

me.filter="CustID=" & 123

you build it into the recordsource

me.recordsource ="SELECT * FROM myTable WHERE " & "CustID=" & 123
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-11-2019, 04:46 PM   #24
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,238
Thanks: 15
Thanked 1,591 Times in 1,511 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Moving to SQL Server

Thanks CJ. Sometimes I need to be clarified

MackBear, If you are lucky enough to be using A2010 or earlier, you can use the upsizing wizard. Otherwise, you need to use SSMA or SQL Server itself. SSMA has been a nightmare for me since trying to use it completely hosed my entire system because of bad instructions on how to get around the database engine issue that MS caused when they made SSMA 64 bit only. So be verrrrrrry careful. I haven't had to upsize anything in a couple of years given that I am mostly retired and in maintenance mode with my clients and not taking on new ones. But, one of the options when you use SSMA is to add rowversion to all tables. I recommend that you do. it makes updating more efficient given how Access interacts with the server.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-12-2019, 12:34 PM   #25
ahmed_optom
Newly Registered User
 
Join Date: Oct 2016
Posts: 58
Thanks: 16
Thanked 1 Time in 1 Post
ahmed_optom is on a distinguished road
Re: Moving to SQL Server

One thing I have learned from transitioning to sql be, is that you realise access is very quick and easy to work with. It will make you consider going back to access....

If you want to change or modify your back end when its in sql, prepare for pain.

Things that access just does automatically, dont happen automatically. For example, primary IDs, being able to just do anything to any table, this is something that has to be "allowed". Datatypes are not immediately all recognisable or sensible (in my opinion).

As for your front end, most will be fine, but you may notice some vba needs to be modified. In my case its mainly due to the fact that with access as soon as some data is created for a record, the record is created, in sql, its not. A few dlookups had to be modified, some procedures needed to be rethought through.

ahmed_optom is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Skipping code after moving to sql server Geirr Modules & VBA 5 09-29-2017 03:42 AM
Moving from Access to SQL Server Libre SQL Server 3 09-03-2015 07:21 AM
Moving Access application to new server Bigjayce Modules & VBA 4 08-10-2007 03:06 AM
Moving database to another server pablotx General 1 12-04-2006 09:10 AM
Moving to SQL Server stormin_norm General 8 04-20-2005 07:35 AM




All times are GMT -8. The time now is 02:06 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World