Migrating

[prstoessel]

Hello all. I am somewhat new to Access and have a few questions.



My whole company uses Access 2003. More and more, we are seeing a need to upgrade. We have a very elaborate system of queries and macros and such that are all vital to our everyday business needs. The problem seems to be that if we purchase Access 2010, we cannot import all those queries neatly (or at all).

Would it be easier to import our stuff into 2007 first, and then into 2010?

Is there much practicality in doing this migration, or should we just look at re-writing everything in SQL?

Thanks for your input.

[boblarson]

The problem seems to be that if we purchase Access 2010, we cannot import all those queries neatly (or at all).

I guess I am confused by that statement. What can't you import?

Would it be easier to import our stuff into 2007 first, and then into 2010?

No, it woudn't.

Is there much practicality in doing this migration, or should we just look at re-writing everything in SQL?

Not sure what you mean by that statement because Access is still the user interface if you mean migrating to SQL Server. I guess we need more information about your system, what it is doing, what challenges you seem to be having and how your database currently actually works. What does it do? What are these challenging queries you seem to have an issue with?

[ajetrumpet]

The problem seems to be that if we purchase Access 2010, we cannot import all those queries neatly (or at all).

Adding to Bob's wonderful comments, which they always are():

What are talking about? I'm curious as to what SEEMS mean? What exactly seems? If I remember right, 2010 still has the import wizard, where you can import any object you want if you have a compatible .mdb file that you've created with 2010. 2010 still reads those.

[prstoessel]

Thank you, kindly, for your response. I'll be more verbose this time.

Why it seems we need to migrate:
Our company receives hundreds of thousands of lines of purchase data from different firms. Most of the time, they come in .xlsx format. Because of the constraints of Access/Excel 2003, we have to use OpenOffice Calc to open the files, save them as a .csv, and then import into a table in Access. My understanding is that Access 2007 and later can import .xlsx files directly. It'd be nice to skip a couple of steps.

The other main reason is that we have developed some Access applications that are meant to be used at our client's site. More and more of our clients are using Access 2010 and are running into some compatibility issues with our software.

Problems with importing Access 2003 stuff into Access 2010:
While researching my questions on the internet (and before seeing this forum), I found a number of people in other forums lamenting the fact that they had to do a lot of re-wiring of their queries and macros after moving their data to Access 2010. I, myself, am not quite adept enough in Access to undertake this without knowing for certain that our queries and macros will work once we move it all over.

SQL
I have a lot of reading and learning to do regarding Access and SQL. So, that explains why my questions likely do not make sense. But, as I mentioned, we have to deal with thousands of lines of data. In some cases, it's so much data that it takes Access overnight to run some macros on it. It works, but very slow. If we were to somehow put these same queries into SQL, wouldn't it not only run faster, but accommodate larger database sizes?

Thank you all, so very much for this forum. I am trying very hard to learn Access and it's good to see such a great resource available.

Regards,
Paul

[boblarson]

Why it seems we need to migrate:
Our company receives hundreds of thousands of lines of purchase data from different firms. Most of the time, they come in .xlsx format. Because of the constraints of Access/Excel 2003, we have to use OpenOffice Calc to open the files, save them as a .csv, and then import into a table in Access. My understanding is that Access 2007 and later can import .xlsx files directly. It'd be nice to skip a couple of steps.

Makes sense.

Problems with importing Access 2003 stuff into Access 2010:
While researching my questions on the internet (and before seeing this forum), I found a number of people in other forums lamenting the fact that they had to do a lot of re-wiring of their queries and macros after moving their data to Access 2010. I, myself, am not quite adept enough in Access to undertake this without knowing for certain that our queries and macros will work once we move it all over.

What you are going to need to do is have ONE machine and use copies of your database to TEST the migration. There is going to be no way to know what will have a problem or not until you do this. We have done the same thing with our Access databases where I work regarding Access 2003 to 2007. I found a few issues that would need to be addressed but nothing major. It really all depends on what you have and how you are using it as to what you will find. There may be people who have had some major challenges in upgrading but there are also quite a lot who found no issues at all. It is really on a case by case basis so there really isn't any way to just say, "yes" they will all work without a problem, or "no" it isn't going to work and will require a lot of work. The only way is to have a test machine and then test it.

SQL
I have a lot of reading and learning to do regarding Access and SQL. So, that explains why my questions likely do not make sense. But, as I mentioned, we have to deal with thousands of lines of data. In some cases, it's so much data that it takes Access overnight to run some macros on it. It works, but very slow. If we were to somehow put these same queries into SQL, wouldn't it not only run faster, but accommodate larger database sizes?

Yes, it will help with database size as SQL Server can handle everything you throw at it up to the limit of your hard drive space available. And, while it isn't necessarily going to speed things up immediately (it may but it actually can run slower depending on what you are doing) you can optimize things so that the server does most of the work instead of having to send tons of data over the network to have Access crunch numbers on a client machine. If you set it up well, you can move to using stored procedures and views on the SQL Server which can speed up some processing. But if you use a lot of built in Access functions or your own procedures that you have written in Access VBA then it can actually slow down the process. There are some performance FAQs out there which you can use.

[prstoessel]

Again, thank you for the reply. If I were to get Access 2010 for my workstation to test things out, do I have to uninstall the Access 2003, or can they both coexist on the same machine?

[boblarson]

I would try to get a machine that has Access 2010 installed ONLY. They can co-exist but you may not get a true reading on what needs to be fixed if you have both when testing. See if they can allocate a machine for a while from someone who has left or something. Just to do testing. It will save you some big headaches in trying to have both on the same machine.

[prstoessel]

I just installed my new Office 2010 onto my 64-bit Windows 7 computer. It seems that all the queries and macros written in Access 2003 work just fine. Very relieved so far.

I do not like the menus. Why does Microsoft always feel compelled to rename stuff and put them in different places? Such a learning curve now to go find everything... ugh!