How to change the Back end sql server database?

[deletedT]

I have a FE with a sql server BE.
If a create a new database in sql server, what is the best way to connect the FE to new database?

I detached a database and am not able to attach it again.
I can add a new database in sql server and restore my backup to this new database.
What's the best way to use the same FE to connect to this new database?


thanks for any kind of advice.

 

[theDBguy]

Hi. You should be able to go to the Ribbon > External Data tab

 

[deletedT]

Hi. You should be able to go to the Ribbon > External Data tab

The possible menus may be :
New Data source
Saved imports
Linked Table manager

None of them seems to be able to change the BE database.

thanks

 

[theDBguy]

The possible menus may be :
New Data source
Saved imports
Linked Table manager

None of them seems to be able to change the BE database.

thanks

Hi. It would be the New Data Source then, look for ODBC.

If you're replacing the old BE, you'll have to delete all the old linked tables first.

 

[deletedT]

Well I was looking for a way to simply tell Access look into this new database for linked table.
It seems that there's no way except deleting the current linked tables, add new ones from the new database and rename all the linked table again.
I have more than 60 tables, and importing new tables add a dbo_ to the first of each table. I normally rename and delete this prefix and renaming all these tables ..... well takes a little time.

But never mind. I use vba to rename all.

thanks for your help.

 

[theDBguy]

Well I was looking for a way to simply tell Access look into this new database for linked table.
It seems that there's no way except deleting the current linked tables, add new ones from the new database and rename all the linked table again.
I have more than 60 tables, and importing new tables add a dbo_ to the first of each table. I normally rename and delete this prefix and renaming all these tables ..... well takes a little time.

But never mind. I use vba to rename all.

thanks for your help.

Hmm, I don't have it but maybe the new Linked Table Manager can relink ODBC tables. Can you try it? I also just use VBA if the tables are the same.

 

[isladogs]

Its fairly simple to do if you use DSN-less connection strings.
The server details are stored in a table and its just a matter of updating the info or swopping to a different record.
You then loop through the fables removing the original connections and relinking.

This is how my clients relink the Access and SQL Server BEs after downloading a new version of the FE from my website.
The process is very simple for end users but of course it does require some code by the developer to set it up!

 

[deletedT]

@isladogs
Thanks for the advice. I've been using DSN linked tables from day 1 and 0 experience with DSN-less connections.
Recently I've seen a lot of talks about DSN-less connections to sql server. Is there any where I can read about cons and pros of DSN-less connections? or a comparison between these two?
I think I need a little study before I step into an unknown world.

Many thanks.

 

[Galaxiom]

I've been using DSN linked tables from day 1

Edit the details in the DSN. You can do it in the ODBC settings or in the registry if you need to automate the edit on multiple machines.

Machine DSN: HKEY_LOCAL_MACHINE > SOFTWARE (> Wow6432Node) > ODBC > ODBC.INI

User DSN: HKEY_CURRENT_USER > SOFTWARE (> Wow6432Node) > ODBC > ODBC.INI