UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update To SQL Using SSMA, SQL Server 2012    
 
   
Marto
post Jul 26 2019, 04:44 AM
Post#1



Posts: 402
Joined: 13-January 03
From: Dublin, Ireland


Hi

I have been trying to use the SSMA 2014 to update and existing database backend (2003) to SQL and i am getting error messages.
Out of 85 tables 19 of these will not load in. Now i have no doubt that there may be issues with these but the descriptions in the error log to me are cryptic.
I have checked them for Primary Key and all tables have a PK

Is there a document to explain what the errors are and more importantly how to fix them. Or something to explain the prerequisites are

TIA
Martin

--------------------
There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.
Go to the top of the page
 
GroverParkGeorge
post Jul 26 2019, 06:54 AM
Post#2


UA Admin
Posts: 36,177
Joined: 20-June 02
From: Newcastle, WA


I am not aware of a specific document like that.

What errors, specifically, are being raised?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Marto
post Aug 1 2019, 08:51 AM
Post#3



Posts: 402
Joined: 13-January 03
From: Dublin, Ireland


Hi

Sorry about delay in replying.

I have managed to get the tables to migrate to SQL. I was using an older version of the SSMA so I downloaded the latest version v8.3.0 and ran it.
The latest version had more descriptive error messages so i was able to address the issues.

I am running into other issues with the data conversion regarding dates. They now all have the time as well .i.e. 2019-08-01 10:45:17.740 is there any way when doing the conversion to stop the time being added on?



--------------------
There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.
Go to the top of the page
 
GroverParkGeorge
post Aug 1 2019, 09:08 AM
Post#4


UA Admin
Posts: 36,177
Joined: 20-June 02
From: Newcastle, WA


There is a lot about times and dates that is not always understood, I'm afraid.

ALL Date fields in Access and in SQL Server contain both the date and the time, whether you explicitly want times or not.

These values are stored as decimal values, e.g. July 23, 2019 at 12:44PM = 43669.5305555556

The part to the left of the decimal, 43699, is the elapsed days since the starting date for Access, which is December 30, 1899 (12/30/1989 in US notation, or 30/12/1989 in the rest of the world).

The part to the right of the decimal, .5305555556, is the elapsed time since midnight.

Sometimes you can store new dates with no time, i.e. as 00000 minutes past midnight. But that doesn't change the fact that Access (and SQL Server) do store the time, always. It's just that it may be midnight on that date.

So, when you see dates appearing in your SQL Server tables with a non-zero time, that means they are coming from the Access table that way, i.e. with a time.

Now, you can FORMAT dates for display so that you--the human user--don't see the time. That is where it can get confusing. Just remember, the time is ALWAYS included regardless of how the DISPLAY of it is formatted.

See, for example, some of my YouTubes here. There are four or five in the series.

I'm afraid that suppressing the times is probably not a good idea, unless you truly want to store midnight as the default time for all dates. Is that appropriate?


If so, you can run an update query to convert the times to 00:00:00 or midnight.
This post has been edited by GroverParkGeorge: Aug 1 2019, 09:10 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Marto
post Aug 1 2019, 11:58 AM
Post#5



Posts: 402
Joined: 13-January 03
From: Dublin, Ireland


I don't need to supress the time but I only need to show the date in the forms. I have tried to change the format to dd/mm/yy in the date txtbox on the form but it’s still showing the time

--------------------
There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.
Go to the top of the page
 
AlbertKallal
post Aug 1 2019, 12:05 PM
Post#6


UtterAccess VIP
Posts: 2,903
Joined: 12-April 07
From: Edmonton, Alberta Canada


You as a general rule should not have issues with date/time. If there is no time portion in the Access data, then you not see a time portion in the SQL size (I think it will be zero).

If you want to remove the time portion,then this likely was a oversight, or something that you did not want to save in the Access application. I would in fact thus remove the time portion in the Access data BEFORE you migrate, and then fix the code and places were you VERY likly used now() (which has a time part) in place of date() which does not.

The other "got ya", is by default SSMA will use datetime2 formats. If you link using the standard SQL driver then Access will see these columns as text, and you have a gazillion issues and problems. The fix is to change the settings (field mapping) in SSMA to use datetime. You can also consider linking the tables using the newer native 11 (or later) ODBC drivers, but then you have to install that driver on each workstation. I guess only you can choose which will be less work.

However, if I was wanting to strip out the time part, I would do it Access side with a update query.
eg:
CODE
update tblInvoice set InvoiceDate = DateValue([InvoiceDate]


After you migrate the data and link the access tables, you want to quick check a few linked tables in design view - and double, triple check that the date columns are seen by access as a datetime column.

Also, because somewhere along the way the Access FE was inserting the time part, it will continue to do so after the migration, so you want to address this issue, and I would address this issue BEFORE you migrate the data to SQL server.

After a migration if done correct, you should not have to change one line or code, or anything on the access client side (in the context of date code, filters etc.). If you having issues with date/time after a migration, then assume something went wrong, fix it, and migrate again.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
Marto
post Aug 1 2019, 12:16 PM
Post#7



Posts: 402
Joined: 13-January 03
From: Dublin, Ireland


I have not migrated my working data yet only a test set.
Seems like i have a bit more preparation to do.

Thank you both for the information.

--------------------
There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 02:19 AM