Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft SQL Server _ Update To SQL Using SSMA

Posted by: Marto Jul 26 2019, 04:44 AM


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


Posted by: GroverParkGeorge Jul 26 2019, 06:54 AM

I am not aware of a specific document like that.

What errors, specifically, are being raised?

Posted by: Marto Aug 1 2019, 08:51 AM


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?

Posted by: GroverParkGeorge Aug 1 2019, 09:08 AM

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 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.

Posted by: Marto Aug 1 2019, 11:58 AM

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

Posted by: AlbertKallal Aug 1 2019, 12:05 PM

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.

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.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Posted by: Marto Aug 1 2019, 12:16 PM

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.