Hi
The company I work for has a few centrally managed SQL and MSDE databases spread around which we are in the process of migrating to SQL2005, we are then likely to consolidate these onto larger servers and almost definitly move them to more central locations. At the moment although they are managed centrally the actual servers are sat in a variety of sales offices and small factory cupboards etc - organic growth - ain't it great?
One aspect of this organic growth is that there are an unknown number of small systems, apps and databases connecting into these 'central' servers for data. Often the people who actually developed these have long since moved on...
We know we have a lot of reverse engineering to do but we would rather perform some steps only once
I had planned to connect all the MS Access databases to the SQL databases via file DSNs, this way when we inevitably need to migrate their part of the tangle of databases we just alter the connection data in the file DSN and presto - users machine is pointed to the correct database. Bear in mind that we may have to make several moves for some of the databases and this starts to reduce our effort and complexity considerably
However, in testing it appears MS Access pays lip service to the File DSN merely reading and storing the connection details within it, instead of a link to the File DSN itself (I have checked this by going to the Connect field in the MSysObjects table). This means that the plan will not work as we can alter the file DSN but Access will still not get the new connection info until the link is manually recreated
Is there any simple method anyone knows that I can use to achieve what I am after? Ideally I would like to avoid VBA as I plan to give basic instructions to a majority of the users so they can do it themselves, giving them VBA code is in my experience asking for a headache
Any bright ideas or anyone done anything similar in the past?
Cheers Simon
The company I work for has a few centrally managed SQL and MSDE databases spread around which we are in the process of migrating to SQL2005, we are then likely to consolidate these onto larger servers and almost definitly move them to more central locations. At the moment although they are managed centrally the actual servers are sat in a variety of sales offices and small factory cupboards etc - organic growth - ain't it great?
One aspect of this organic growth is that there are an unknown number of small systems, apps and databases connecting into these 'central' servers for data. Often the people who actually developed these have long since moved on...
We know we have a lot of reverse engineering to do but we would rather perform some steps only once
I had planned to connect all the MS Access databases to the SQL databases via file DSNs, this way when we inevitably need to migrate their part of the tangle of databases we just alter the connection data in the file DSN and presto - users machine is pointed to the correct database. Bear in mind that we may have to make several moves for some of the databases and this starts to reduce our effort and complexity considerably
However, in testing it appears MS Access pays lip service to the File DSN merely reading and storing the connection details within it, instead of a link to the File DSN itself (I have checked this by going to the Connect field in the MSysObjects table). This means that the plan will not work as we can alter the file DSN but Access will still not get the new connection info until the link is manually recreated
Is there any simple method anyone knows that I can use to achieve what I am after? Ideally I would like to avoid VBA as I plan to give basic instructions to a majority of the users so they can do it themselves, giving them VBA code is in my experience asking for a headache
Any bright ideas or anyone done anything similar in the past?
Cheers Simon