I have a set of SQL servers(runs perfectly with SQL Server Management Studio/new Windows 10 PC's) and one server which runs on a super old Windows XP machine and created with MS Access. When I try to connect the Access db's with SQL the ODBC won't let connect two machines since the access running PC is super old. Management does not want me to re-create the access db in SQL as well. I need to find a way to connect access db to other SQL db's.
My thoughts so far : Write a simple script which can overlook data in the access db periodically and copy to a new SQL server. But unfortunately I don't have any idea where to start.
Any ideas other than my thoughts which can be easily done? Or does anyone know how to write a script as I suggested?
Thank you!
Are you talking about a long time Access application that was and did connect to sql server? Or are you NOW talking about some VERY new case in which say you are attempting to connect SQL server to a access data file? These are VAST two different questions. I see little or no reason that if that Access application was connecting to sq server that you cannot continue to do so. And you NEVER connect to a access database, but in fact OPEN a file. You can no more connect to a access file then you can connect to a word file. That is a file based system.
So, those Access applications if they were connecting to sql server? Then you should be able to continue to do so. However, it is possible that you are suggesting or wanting to migrate the data from Access tables to sql server? And once again, that is certainly possible, and there is a even a migration tool available that will help you in this process.
Of course in most cases, often the data part and data tables is in fact a MINOR issue. Why? Because Access is not really a database, but in fact a tool and system to create software. So your forms, your code, the application parts? They cannot be moved to sql server, and of course sql server does not have forms, a GUI and does NOT have any tools to create the UI.
So, you have to clear up what you are attempting here. If those Access applications (not a database anymore) were using say Oracle, or SQL server as the database, then Access was not the database, and was the application development tools. So you can use vb.net, c#, FoxPro, or even Access to build a application. However where the data resides and was/is being used? That is also a choice that Access developers can make.
So, you have to be CRYSTAL clear here as to where the data resides now, and where it always resided. As noted, even back in XP days, it was 100% possible and common for a Access application to store its data in SQL server - and thus as a result Access was not the database anymore, but was the tools and system used to create the application part, the reporting part, and the forms + code part.
If the access files are JUST used for data? Then sure, you can migrate such data to sql server - and there are tools for that task. However, if you have a existing application in Access that ALSO uses Access data files? Then you can migrate the data to sql server and continue to use the Access application part (with linked tables to sql server). However, while such migrations are relative easy to do, some code changes and some updates to the Access application parts ARE required, and required by a experienced developer. Once those minor changes are made, and the data tables are migrated to say sql server, then the Access application can continue to function and work before - the only difference is now the data resides on SQL server. As noted, such a migration project allows you to continue to use the Access application part - but it will require "some" changes to the code, and thus a deep and good experience with Access, and the application in question is usually required for such migration projects.
Moving the data from Access to SQL is easy - near trivial. However, what is NOT trivial is then making the changes to the Access application part that is now to use the data tables on SQL server as opposed to Access tables. This kind of migration is common fair, and about 99% of the existing Access application will work fine with SQl server as the data source - but you need experience in having worked with Access + SQL server - and that skill level is not to be ignored here.
Make no mistake, learning the Access developer tools? it takes as much skill to earn vb.net, C#, or FoxPro, or VB6 or just about any other developer system.