Question ODBC Linked Tables

[dugoneill]

I have an access database front end to a SQL Server back end, written in Access 2003, stored on a shared drive.

Some people have upgraded to Access 2007.

Each time the database is opened in a different version of Access from the last time, the tables need to be re-linked or the person gets an ODBC error.

Does anyone know of a way around this behavior (having everyone upgrade unfortunately is not an option at this time)?


Thanks

 

[Banana]

One possible solution is to use DNS-less connection & automate the linking at startup.

Google Doug J. Steele's DNS-less connection for a sample code.

 

[ByteMyzer]

Create two versions of the Front End, A2003 and A2007. Distribute copies of the appropriate Front End to each of the machines that will be using this application.

 

[SOS]

I totally agree with ByteMyzer. It is important, beyond the ODBC connection, that you do not open the same file with different versions of Access. And, it is even more important that you not be sharing a frontend amongst many people on the network. They should EACH have a copy of the frontend on THEIR machine.

 

[dugoneill]

Create two versions of the Front End, A2003 and A2007. Distribute copies of the appropriate Front End to each of the machines that will be using this application.

The app itself isn't local, everyone who uses it gets to if from a shortcut on their local machines.

Really wouldn't want to have to distribute a new copy any time any changes are made to it, nor would I want to have to make changes to two versions of the same tool.

Thanks for the idea - just seems like a lot more maintenance then just making the end users relink the tables when they open it.

 

[dugoneill]

One possible solution is to use DNS-less connection & automate the linking at startup.

Google Doug J. Steele's DNS-less connection for a sample code.

Thanks I will look further into this.

 

[SOS]

The app itself isn't local, everyone who uses it gets to if from a shortcut on their local machines.

It should be though when using on a network. The FE should not be invoked from a network location and it DEFINITELY should not be shared.
See here for more about splitting to include why.

Really wouldn't want to have to distribute a new copy any time any changes are made to it, nor would I want to have to make changes to two versions of the same tool.

You can use a frontend auto updater (Bob Larson has one here) which will let you just modify the frontend and then the user will always have a copy of the newest.

Thanks for the idea - just seems like a lot more maintenance then just making the end users relink the tables when they open it.

Actually, if you do not use a separate frontend for each user and you run a single file from the network, this is not just a convenience thing. You are playing Russian Roulette as it isn't a matter of IF it will corrupt the frontend, just a matter of WHEN it will.

 

[dugoneill]

I totally agree with ByteMyzer. It is important, beyond the ODBC connection, that you do not open the same file with different versions of Access. And, it is even more important that you not be sharing a frontend amongst many people on the network. They should EACH have a copy of the frontend on THEIR machine.

Must admit that I am pretty new to this, why would this be an issue? As stated in my other reply - the maintenance is definitely less in keeping up one location instead of many and having to redistribute for when any changes are done.

Maybe in this case it doesn't matter as much since really it is only setting up a few queries where the user inputs specific parameters for what they need to view or do.

 

[dugoneill]

It should be though when using on a network. The FE should not be invoked from a network location and it DEFINITELY should not be shared.
See here for more about splitting to include why.

You can use a frontend auto updater (Bob Larson has one here) which will let you just modify the frontend and then the user will always have a copy of the newest.

Actually, if you do not use a separate frontend for each user and you run a single file from the network, this is not just a convenience thing. You are playing Russian Roulette as it isn't a matter of IF it will corrupt the frontend, just a matter of WHEN it will.

WOW, thanks a ton...a lot more I obviously need to learn here. I will definitely be checking this all out in more detail!!!