April 13, 2020 at 6:26 pm
Someone on another forum asked about how to upsize/migrate a database from Access to SQL Server. One of the huge differences between Access and SQL Server is Access VBA modules. Is there a good "Upsizing Guide"/Gotcha list for that available somewhere? My gut reaction right now is to modify all the functions and subs that the Access DB uses so that the use of each is logged so I have some idea where to start with this mess.
Any good reading on this anywhere?
April 13, 2020 at 7:52 pm
Step 1 - sit down with users of the Access database and figure out what it does
Step 2 - throw it out and build one from scratch in SQL Server that does what they need.
See? Easy. Two steps.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
April 13, 2020 at 10:42 pm
There are a number of good resources in evaluating your options. One of my favorites is:
https://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html
Others you might consider:
I spent 20 years working with SQL Server as a back-end to Access front-ends, and there are some compelling reasons to consider that approach. The challenge in migrating entirely to SQL Server is that you have to create a user interface that is easy to implement, friendly for the user, and easy to change. If you have the front-end already done in Access, migrating the data to SQL Server or Azure in the cloud is a pretty manageable effort compared with building a new front-end with other technology. Just my two cents - and I've been retired for over 5 years.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 14, 2020 at 12:21 am
Wendell,
Thanks for answering! I was hoping you would. Super handy list! I'll have to have a read and pass on the links. (Pretty sure I told the OP to post here, but you can lead a horse to water...)
Generally speaking, one could just use the wizard to upsize, and then fix the parts that don't work... (glosses over a lot!) The one part that's going to be hard is migrating the VBA stuff that deals with recordsets (cursors in SQL Server speak). One part that's going to be a challenge is migrating the Access queries that are kind of "mutants" from a SQL Server perspective - like views /stored procedures.
I'm quite sure there's no one size fits all answer. This is gonna be interesting!
The OP's big question was about what to do with the UDFs in his Access DB. Is there a standard way to deal with those? (Or even a rubric somewhere?) I used Access for a long time and have had to do hideous things with it (like write dynamic SQL), but haven't really had to migrate a functional app to SQL Server. Is it fair to say that the UDFs and queries that use them may have to be rewritten from the ground up? There are some features in SQL Server that are similar, but once you get into VBA programming, things get wonky.
Maybe an interim step is to do like Albert Kallal says and migrate the tables and then leave the rest in the front end unless there's good reason to migrate them (performance issues etc)?
Thoughts? Thanks!
Pieter
April 14, 2020 at 12:23 am
LOL... Part of the problem is the fact that Access uses VBA to accomplish anything complicated (transactions etc), and uses VBA to manipulate the interface etc. Separating the two might be hard.
April 14, 2020 at 4:49 pm
I presume our paths have crossed somewhere in the past, but haven't been able to figure out where and when.
First, lets make sure we are talking about the same thing. What version of Access is currently being used? Second, is it split into a front-end and a back-end? And while we are at it, what is the version of SQL Server, and is it using Integrated Security? And finally if it is split, are design changes of the front-end requested frequently?
If the tables are in a back-end, we typically use an upsizing tool to create those tables where it works. For those that contain NVarchar, we create the table in SQL Server with appropriate data types, and then run queries in Access to populate them after having linked the new table to the accdb file. That of course assumes you have setup and ODBC data source beforehand so you can link from Access. Once you get the data to SQL Server, you may want to consider the following:
One way to improve performance is to create Views in SQL Server. If you want to have it updateable, you will need to define an index for the view. Doing that sort of thing has couple of advantages - you avoid Access queries with more than two linked tables, and you can do things like archiving changes and logical deletes.
As I noted before, you typically want to avoid Access queries with more than 2 linked SQL Server tables. As long as you do that, the ODBC driver will convert your Access SQL to SQL Server syntax and give the result set, or a limited number of rows if the result is a large number of rows. You also typically don't want to run queries where some tables are in Access and others are in SQL Server.
Then you start looking for queries in the front-end that have performance issues. It means looking first at the queries that are saved. Then you look at forms and reports that are slow, and design queries that run well. In some cases, you may want to create a UDF, in others a pass-through query.
There's a lot more info in the references I pointed you to in my previous reply, but those are the basics. Taking that approach, we built systems with nearly 100 local users, and tables with multi-million rows, and local user forms response times typically under 1 second.
Hope that helps - I guess I should add that you need to become fairly proficient in Access forms, reports and queries, as well as VBA. And I guess I'm agreeing with Albert as far as the basic approach.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 22, 2020 at 5:32 am
This was removed by the editor as SPAM
November 3, 2020 at 8:21 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply