Moving to SQL Server (1 Viewer)

Mackbear

Registered User.
Local time
Today, 11:50
Joined
Apr 2, 2019
Messages
168
Hello, it's me again.... Reaching out for your guys valuable assistance...

So I already have Access database front/back end set up, and we are looking to move the data to sql server. Will there be major changes on the front end that I created? Like the queries and VBA codes that I have on the front end? What are the changes I need to make? Thank you very much for your assistance!

:confused::confused::confused:
 

isladogs

MVP / VIP
Local time
Today, 16:50
Joined
Jan 14, 2017
Messages
18,186
Depending on how your tables were designed, you may need to make some changes to some datatypes so the tables can be transferred to SQL Server

There don't need to be any changes to the front end queries and VBA code, at least initially.
However for optimum performance you may decide to move some of your queries into SQL Server itself and do the processing at the server level. If so, that will requires some conversion work
You can then use various features such as views, passthrough queries and stored procedures to make use of the power of SQL Server
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:50
Joined
Oct 29, 2018
Messages
21,358
Hi. Just curious, what does your database do, how big is it, and what version of SQL Server were you thinking of using?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:50
Joined
Aug 30, 2003
Messages
36,118
I just helped a client with this conversion. One gotcha if you use SSMA, the default data type conversion mapped date/time in Access to datetime2 (or something similar), which after conversion caused problems. I had to change the mapping to datetime.

One table had a couple of miskeyed dates that Access didn't mind but wouldn't convert. Something like 6/24/209.

He also had an odd thing where he had yes/no fields in a table that he summed in a query. Yes/no maps to bit in SQL Server, and the query barfed saying you can't sum a bit field. I switched that to tinyint I think. That would only matter if you were also summing a yes/no field somewhere.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 12:50
Joined
Apr 27, 2015
Messages
6,281
Just to add to the confusion, when I first “upsized”, I did so with AC2010 and was able to use the wizard. The wizard automatically added a timestamp field so I could edit tables as I normally would. After we “upgraded” to AC2013, the wizard was no longer available and any new tables that I added had to be exported manually.

Easily done but I did not understand the importance of the timestamp field and I took me a few hours to figure it out!
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:50
Joined
Jan 20, 2009
Messages
12,849
Just to add to the confusion, when I first “upsized”, I did so with AC2010 and was able to use the wizard. The wizard automatically added a timestamp field so I could edit tables as I normally would.

Never used the Upsize Wizard and have never needed to add a TimeStamp column to make a table editable.

So long as the table has a appropriate Primary Key there shouldn't be a problem. 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.
 

Mackbear

Registered User.
Local time
Today, 11:50
Joined
Apr 2, 2019
Messages
168
Hi. Just curious, what does your database do, how big is it, and what version of SQL Server were you thinking of using?


Hello, one of the database stores activity time stamps coming from the front end of several users, the other one stores records that needs to be worked on by several users using the front end. I am not sure yet what version of sql server we are going to use and will get back to you on that. Right now it is not that big yet, just about 3 months worth of data. Thanks!
 

Mackbear

Registered User.
Local time
Today, 11:50
Joined
Apr 2, 2019
Messages
168
I just helped a client with this conversion. One gotcha if you use SSMA, the default data type conversion mapped date/time in Access to datetime2 (or something similar), which after conversion caused problems. I had to change the mapping to datetime.

One table had a couple of miskeyed dates that Access didn't mind but wouldn't convert. Something like 6/24/209.

He also had an odd thing where he had yes/no fields in a table that he summed in a query. Yes/no maps to bit in SQL Server, and the query barfed saying you can't sum a bit field. I switched that to tinyint I think. That would only matter if you were also summing a yes/no field somewhere.

So is it advisable to avoid the yes/no data type? How to map the date time?
 

Mackbear

Registered User.
Local time
Today, 11:50
Joined
Apr 2, 2019
Messages
168
Just to add to the confusion, when I first “upsized”, I did so with AC2010 and was able to use the wizard. The wizard automatically added a timestamp field so I could edit tables as I normally would. After we “upgraded” to AC2013, the wizard was no longer available and any new tables that I added had to be exported manually.

Easily done but I did not understand the importance of the timestamp field and I took me a few hours to figure it out!

What is the timestamp field for?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 12:50
Joined
Apr 27, 2015
Messages
6,281
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.

I have tried to look for some sort of rationalization that this was not an insult aimed at me. I failed, so here it is:

Your prowess in all things Access is rivaled only by your utter lack of tact, diplomacy and manners. Not all that surprising but very disappointing...

@MackBear, despite what our esteemed moderator as alluded to, my tables were properly normalized and had PK’s (autonumbers to be specific). I could edit the table in question in the SSMS but when it was used in a query that served as the RecordSource for my form, it was un-editable. I examined and compared it to all the other tables that were upsized and the only thing missing was the timestamp field. After adding it, everything was fine. If you are able to get by without it, great. But if for some reason you have a similar problem, then this “fudge” may be your answer.
 

isladogs

MVP / VIP
Local time
Today, 16:50
Joined
Jan 14, 2017
Messages
18,186
Hopefully I won't cause any offence in this reply.

I've converted many databases to SQL Server using both the upsizing wizard in A2010 (since deprecated) and more recently using the SQL Server Migration Assistant. I've also never needed to add a timestamp field (now rowversion field) and my tables have always been editable in Access.

Perhaps that's because I still use the basic SQL Server driver and only use older datatypes in SQL Server. For example, I've never used datetime2

The only time I tried adding a timestamp was as an experiment to see if it added useful functionality. If it did so, I was unaware of it.
Perhaps I am missing something important as other forum members seem to stress its needed
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:50
Joined
Jan 20, 2009
Messages
12,849
So is it advisable to avoid the yes/no data type? How to map the date time?

Yes/No datatypes are fine as bit columns in SQL Server. However they do need to have a default value and Nulls not allowed or they will have update problems when linked with Access.

Sometimes developers Sum them in Access to get a count of how many are True and this doesn't work with bit columns in SQL Server. As Paul suggested they can be substituted with a tinyint that can be summed.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 12:50
Joined
Apr 27, 2015
Messages
6,281
Update:

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.

So...I had to make a couple of tables today and experienced the same problem I have in the past.

Given the level of expertise of those who have weighed in on this post, I set about with the mindset that there HAD to be something I was overlooking...

...and there was. Seems the settings (replication) on my tables and bit fields were the culprits.

With that, Galaxiom was right (as usual) about alluding to me not “know(ing) what I was doing”. Won’t be the last time I have to suffer through that.

I stand by my statement about his manners though :cool:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Feb 19, 2002
Messages
42,971
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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Feb 19, 2002
Messages
42,971
@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.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:50
Joined
Jan 20, 2009
Messages
12,849
With that, Galaxiom was right (as usual) about alluding to me not “know(ing) what I was doing”.

When I said:
Galaxiom said:
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.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 12:50
Joined
Apr 27, 2015
Messages
6,281
Thanks for the clarification G, guess I’m too defensive and sensitive at times.
 

Mackbear

Registered User.
Local time
Today, 11:50
Joined
Apr 2, 2019
Messages
168
@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?
 

Users who are viewing this thread

Top Bottom