Converting to SQL Server

[redbirdofthesouth]

I am trying to overcome some issues of multiple users with multiple versions of Access, by converting to client/server configuration, using SQL Server, with multiple front ends. This works great with xp!
However when I use Access97 to link to these external tables, I get complaints about the length of the table names.
I got around this, kinda, by shortening the name in SQL Server, creating the link in Access, renaming the table in Access to the correct name as referred in all the code, then renaming it in SQL Server.
BUUUUTTT, I am worrying about whether this will affect the connectivity and if it doesn't, what about the need to refresh periodically.
If I change the table name that means changing all the references to it, those in queries and reports and on all the front ends. Good luck finding them all...
Does anyone have an idea of a way around this?

 

[fredk]

redbirdofthesouth -

I am looking into moving my access databse to sql server and continue to use access as a front end.

I was wondering if you have any tips?

Thanks much!

Fred

 

[ReluctantDataGuy]

Just my opinion, but if you don't have to, don't do it (!)

I'm associated with a project that in Access alone would have take 3 to 4 months. Our IS department felt that the number of users and the size of the application warranted a change of the backend to SQL server.

What the promised to do, and failed to do, was provide expertise in SQL server to make the transition easier. The only person they stuck us with for a limited time was a guy who was GOING TO CLASS to learn SQL Server himself. Great support huh.

So we're now going on our 4th YEAR, and the main programmer has had to learn SQL server from scratch, along with Access ADP, since there are severe limitations when you use a regular .MDB with SQL server.

Basically, we've had nothing but trouble, and the vast majority of problems we've had have had little or no documentation - or even help in tech sites - to overcome them.

If you have REAL SQL Server support, you might fair better.