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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> 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
 
 
Start new topic
Replies
GroverParkGeorge
post Jul 26 2019, 06:54 AM
Post#2


UA Admin
Posts: 36,986
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
 
AlbertKallal
post Aug 1 2019, 12:05 PM
Post#4


UtterAccess VIP
Posts: 2,976
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
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    4th April 2020 - 12:48 AM