December 10, 2008 at 7:39 am
Hi All
I used migration assistant to migrate access 2003 database to SQL 2005. I want to use Access as my front end and SQL 2005 as backend. I have some forms built in Access. These forms stopped working after migration. I fixed all the forms and now they are working fine. Due to migration Access (*.mdb) file is linked to local SQL server. So all the forms work fine only on that machine, when I tried to move the migrated access database to some other machine all the forms stopped working again.
Is there a way I can move the migrated access database to other machines?
Or I have to take the original access database to all the machines, use the migration assistant to migrate ,and then do all the changes in the forms to make them work.
I was just wondering if is there a easy way around to move the migrated Access database to other machines
Thanks
Gaurav
December 10, 2008 at 7:42 am
So you are moving the server that the access 2003 front-end is linked too?
when you move the access database to a new SQL server you will have to change the connection that the client-access front-end connects to.
or have I got the problem wrong..
December 10, 2008 at 7:56 am
Every machine will have its own SQL server and an access database as its front end
Now I have a working Access database (front end) linked to the local SQL server. Lets say I have an access database (ABC.mdb) which is linked to Local SQL server and this is working fine after I fixed all the forms. Now I want to to have same database (ABC.mdb) on other machines linked to there local SQL servers. How can I make access database to point to the local SQL server on different machines. Once I have Access database (front end) and SQl server (back end) on different machines I can perform merge replication between them.
I hope I am able to explain the problem
Thanks
Gaurav
December 10, 2008 at 8:01 am
SQL server should really be installed on a Server and all the client machines should then access this server. Setting up local copies of SQL server and then using mergre replication seems like an odd setup. What is the reason for this setup.?
December 10, 2008 at 8:07 am
most of the time client will not be connected to the main server. Therefore we have local SQL server on every machine with Access as its front end. So main server will be updated using merge replication once a while.
Any suggestion on how to get access front end working on different machines?
Thanks
Gaurav
December 10, 2008 at 9:18 am
I can see a couple of options offhand, not sure which is best depending on the setup of the client machines:
1) If the database name within SQL Server is the same on all client machines, you can setup an ODBC Data Source with the same DSN name on each client. This can be done by selecting localhost or 127.0.0.1 as the SQL Server so it can be a File Data Source you copy to each client machine. If the database name is different on each client then you'll need to setup the ODBC on each client individually.
2) A different option could be to use the alias feature of SQL Server Configuration Manager in the SQL Native Client Configuration.
December 10, 2008 at 9:20 am
gm.mittal (12/10/2008)
most of the time client will not be connected to the main server. Therefore we have local SQL server on every machine with Access as its front end. So main server will be updated using merge replication once a while.Any suggestion on how to get access front end working on different machines?
Thanks
Gaurav
Okay make sense, there could be an issue with the DSN having different names, what is the error message that you are getting?
December 10, 2008 at 9:51 am
Hi
I have attached the snapshot of error message with this post
Thanks Gaurav
December 10, 2008 at 9:56 am
The connection has been denied The reasons are either;
the databse does not exist ( check that it does and that you have the name correct)
the user does not have permission to the database (check that the user does have correct permissions to the database and connect permissions to the server)
December 10, 2008 at 12:39 pm
This is how I have right now
On machine say A I have migrated corrected Access database (ABC.mdb) linked to SQL server on same machine A (ABC_SQL.mdf, ABC_SQL.ldf)
I took the backup copy, detach the SQL database (ABC_SQL), copied the ABC_SQL.mdf and ABC_SQl.ldf on machine say B and then reattached the ABC_SQL.mdf on machine B
when I tried to open the corrected access database (ABC.mdb) from machine B I got the above error (snapshot of the error message is attached to the earlier post)
So I am sure that database exist and the names are correct
How can I set up the ODBC database?
December 10, 2008 at 1:13 pm
if you use the upsizing wizard in Access it has the option to generate\configure an Access front end that you can then use to link to the SQL database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 10, 2008 at 2:53 pm
You can creeate the ODBC DSN in the Administrative Tools of Windows XP / Data Sources. Administrative Tools is available in the Start menu directly or through the control pannel.
December 10, 2008 at 3:15 pm
or just click start then run and type
odbcad32
then hit return 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply