Access frontend data not updated into SQL server even after migration (1 Viewer)

[manusri]

Hi all,
I have a complex Access database with a lot of Subforms and related tables. It is used by atleast 10 people with minimum of 3 users in there at the same time. The database is growing and causing problems. the IT team at my company suggested i migrate the data to SQL backend. Now i am a beginner when it comes to SQL and blindly(not really... your forum help me understand what i was doing) followed their instructions.

IT configured the SQL server for me so i started with Management studio,

1. Create a new "database" in the management studio.
2. Open SSMA and follow the wizard to link, convert, load and migrate access objects (tables in my case) into the database you created.
3. After conversions, take care of any errors and warnings.
4. Save the project and exit.

My front end users have used the database with no hitches since then.
Now, when i go back into the SSMA and look at the SQL Metadata, it dint store/update any data changes made on the front end (Access database). When i try only "Migrating" the data again it says "No Objects were selected".

How do i migrate/update data from the access front end to SQL server back end. I feel i am missing a big piece here but the IT team here is saying i will have to unlink the tables everytime and then re link, convert, load and migrate to get updated data. and that is the only way.

The whole idea behind moving it to SQL server was so there is no data loss when the front end crashes for any reason. I have suggested going .NET but, my company does not have enough resources for that. Can you please help me fix this issue.

 

[Galaxiom]

The front end should not have any data in it. All data should be in the back end whether that is Access or SQL Server.

It sounds to me like you had been sharing an unsplit database. That would have definitely caused major problems.

Once the backend is in SQL Server it isn't really practical to use the upsize wizard any more. Change the table design in the SSMS and update the links in the front end using the Linked Table Manager.

 

[Steve R.]

I moved my MS Access (back-end) to SQL server. My MS access (front-end) did not contain data. Note the comments by Galaxiom.
I will briefly outline what I did. You will have to research how each task is accomplished.

1. I worked with the IT department to get tables established on SQL server, based on the MS Access back-end tables.
   
2. We established an ODBC connection on the network between the MS Access front-end and the SQL Server.
   
3. A DAO recordset was created on the MS Access front-end (alternatively you can use the back-end) and the data from the MS Access back-end was uploaded to the SQL server.

Some tweaking was involved, but in the end it all worked. You will need to research each of these steps and work with your IT staff.

 

[Rx_]

Look for some of the key terms Linked Tables, Connection String, DNSLess connection around this forum.
Using code to establish a Linked Table can be prefered over using the ODBC connection on each and every individual PC.