Migrating Access Tables to SQL Server, a Comparison of Methods

[GPGeorge]

Former Access MVPs Tom Wickerath and George Hepworth will discussion preparing for a migration from Access to SQL Server and demonstrate two tools for doing it.

The Access Pacific AUG Monthly Chapter meeting is on April 6th, at 6:30PM Pacific (UTC -8)
(Please verify your Time Zone offset for your local start time.)

We'll discuss pre-migration preparations and planning, then take a closer look at two methods for migrating data from your Access tables to SQL Server.

• Scripting tables in VBA and exporting the data from Access to SQL Server -- Tom Wickerath.
• Using SSMA to create tables in SQL Server and migrate data from Access to those tables -- George Hepworth.

In preparing for this presentation, George encountered a bug in the newest version of SSMA (9.3) and reported it. Within 24 hours the SSMA team responded. Within 72 hours they'd released the fix. That kind of response is notable and noteworthy. Don't you wish all of our bug reports could be resolved as efficiently?

Bring your own questions and suggestions for the group. We like to round out a presentation with discussions about real world situations.

If you have ever struggled with migrating data from Access to SQL Server, using SSMA or another approach, please join us. We'll try to explain what to look for, how to prepare for the migration, and how to use the tools.

 

[arnelgp]

presentation with discussions about real world situations.

the real world is users are already using Date/Time Extended in access
but found it difficult to use, especially when it is on Form and you assign it to
a variable (VBA).

 

[GPGeorge]

the real world is users are already using Date/Time Extended in access
but found it difficult to use, especially when it is on Form and you assign it to
a variable (VBA).

That is one of the big issues we will talk about for a data migration, but I suspect there's no simple answer.

Fortunately, for Access developers, though, we get to control that during a data migration to a new database, assuming the target database is under our control. We have the opportunity to define which datatypes our data will be converted to for the migration. We can avoid DateTime2 datatypes.

If the target database is not under our control, and the DBA has already created fields as DateTime2, we have to work with that. And that can lead to several problems. I've been retired for so long that I don't see the particular problem you mention with VBA variables, though. Can you elaborate a bit, or even join the meeting to discuss it?

There are other data type problems. I am trying to get some definitive answer to this one, for example. So far with no response.

 

[arnelgp]

see this thread:
Type mismatch for date. Cannot query via direct window | Access World Forums (access-programmers.co.uk)
and
Query - format MS SQL date/time extened that is linked to Access as yyyy-mm-dd | Access World Forums (access-programmers.co.uk)

test this db (table1 form).

 

[GPGeorge]

Thank you. I think there are solutions to this problem. See my reply in that linked thread.

• You have to use an ODBC driver that supports extended datatypes (ODBC 17 and 18 do).
• You have to enable support in the accdb for them. When linking to tables with those extended datatypes, Access should advise you accordingly and ask you to enable that support.

But this is, again, exactly the kind of discussion we want to have with users who are migrating or planning to migrate their data.

 

[arnelgp]

You have to enable support in the accdb for them. When linking to tables with those extended datatypes, Access should advise you accordingly and ask you to enable that support.

my demo is standalone/local table.

 

[GPGeorge]

my demo is standalone/local table.

I'll look at it.

 

[arnelgp]

you know, it is much easier to work if the table is linked to mssql.
you just create a View in mssql that convert the datetime2 to date and create a Linked table from that view.

on local table, i can't find a way to deal with it.

 

[GPGeorge]

I'll look at it.

Thanks for that.
I note that in the original demo, you did not enable support for extended Date datatypes. Enabling it doesn't change this behavior, though.

I wonder if this has been reported to Microsoft?

Re: remediation. That's one way to handle it. Another is to simply not migrate the data to datetime2 in the first place. Again, that depends on whether you control the targe database, or if it is an existing database created by a DBA who isn't open to cooperation.

 

[GPGeorge]

Thanks for that.
I note that in the original demo, you did not enable support for extended Date datatypes. Enabling it doesn't change this behavior, though.

I wonder if this has been reported to Microsoft?

Re: remediation. That's one way to handle it. Another is to simply not migrate the data to datetime2 in the first place. Again, that depends on whether you control the targe database, or if it is an existing database created by a DBA who isn't open to cooperation.

The Recording of our Presentation is now on YouTube.

I am working on organizing a follow up to cover some issues in more depth than we had time for on Thursday. It went an hour and 42 minutes after editing out the vocal ticks (ums and ers and transitions), so buckle in when you watch.

 

[isladogs]

@arnelgp
The DTE datatype doesn't yet have a name (I reported this oversight to the Access team about 2 weeks ago).
However, I discovered that it does have an enum which is 26

Does that help you in any way until it is properly identifiable by name?

 

[GPGeorge]

I'm not sure how it could, Colin.
I'll record a quick video illustrating what I've learned so far, based on Arnel's original description.

I'm most interested in the DateTime2 data type in a linked SQL Server table. It is possible to enable the Date Time Extended data type for Access tables, as well.

 

[isladogs]

It may be worth looking at Maria Barnes' video on the topic

.
Some specific things may have been updated since it was made a year ago.

So far, I'm not clear what additional functionality is gained by ticking the checkbox in Access options.
The data can be read without this checked. Similarly with the bigint datatype

 

[GPGeorge]

As far as I can tell, the difference is in the precision of the values available in Access.

Compare this screen shot. I'll try to record a video today, but there's a lot on my plate at the moment.

 

[isladogs]

I meant I'm not sure what additional functionality is gained in working with DTE fields with the Access options checkbox ticked.

This is a linked Access table with bigint & DTE fields. Both datatype support options are unticked

Whether or not it is ticked, I see the same degree of precision with DTE fields and am able to edit them in a table/query/form
However, issues occur when trying to reference them or do calculations on DTE fields e.g. difference between two values

 

[GPGeorge]

I agree. The whole thing is murky to me. I completed the first video exploring the problem. However, I think I found a way around it by handling them as strings in VBA. At least I found a page suggesting that. So that's the next step.

The third step will be to see what happens when those two check boxes are never checked. The message which pops up indicates the accdb will be permanently changed when you do that. I am not sure what the implications are.

I'm wondering if this behavior should be classified as a bug or a feature.

 

[isladogs]

The effect of checking either checkbox is that the Access file structure is permanently changed
That means older versions of Access cannot open the file even if you never use bigint or DTE and even if you untick the option later.

So, to my mind, what possible benefit is there at the moment in ticking either checkbox?

 

[GPGeorge]

The effect of checking either checkbox is that the Access file structure is permanently changed
That means older versions of Access cannot open the file even if you never use bigint or DTE and even if you untick the option later.

So, to my mind, what possible benefit is there at the moment in ticking either checkbox?

I'm hard pressed to see any advantage. Plus the errors in VBA indicate at least one disadvantage.

However, there might be one that we're not seeing yet. I think it's time to ask around.

 

[GPGeorge]

It seems like you have provided information about an upcoming Access Pacific AUG (Access User Group) Monthly Chapter meeting, including details about the agenda and speakers. The meeting will discuss preparing for a migration from Access to SQL Server and demonstrate two tools for accomplishing the migration.

That one is past. Our next meeting is day after tomorrow, the 1st of June.