live connection between an Access table and a SQL server table

[iggydarsa]

Hi,

I have a table in Access called "Client". I also have a table in SQL server named "TAT". They have the same data structure and I already exported the data from Access to SQL.
However, everytime there are changes in Access, I want it to be reflected in SQL as well.
I created a linked table in Access pointing to TAT table but I dont know how to bind the Client table in Access with the TAT table in SQL (or linked table named TAT in Access)

Any ideas?

Thanks!

 

[iggydarsa]

26 views and no reply,
Assuming no solution?

 

[xenou]

What have you tried? You should certainly be able to just insert/update/delete if you want. This requires that the linking be done in such a way that this is allowed. Some connections are read/write, others are read-only. Have you talked to the SQL Server admin/dba? While in Access we generally make most or all users "admins", in SQL Server it is much more common for some users to have read privileges and others (fewer) to have read/write privileges, and even then only on certain tables, not all tables.

 

[iggydarsa]

thanks for the reply xenou,

I have read/write access for both platforms.
this is what i have done:
1- I created a linked table in Access
2- When I did that I used the export feature so it created the same table structure in SQL

Now I have following tables:
In SQL:
tbl_TAT

In Access:
tbl_Client (regular table)
tbl_TAT (linked table to SQL)

What I need is to bind tbl_Client to tbl_TAT.

If I have to create insert/update events; how do i do it so these events can be triggered when a new record is entered or an existing is modified. Not quite savvy with Access yet so I am not sure about the query as well.

Thanks.

 

[HiTechCoach]

Will the data ever be chaged directly in the table tbl_TAT (linked table to SQL) and not in the Access table?

Keeping the to table in sync in not as difficult if is is a one direction (access to SQL server ) sync only. You delete the data fromteh SQL Server table and then append al the record from Access.


If it will be a two way sync then I would highly recommend just using the table in the SQL server for everything.

***ALERT: Before you do the following make sure Auto Correct for the current database is turned off. **Hopefully you keep this turned off.
If you were to rename the tbl_Client (regular table) in acess to something like tbl_Clientold. Next creet a query based on the tbl_TAT (linked table to SQL) table. Include all fields and save the query witht he name tbl_Client. Now everyting in Acess will use the table int he SQl server.

 

[iggydarsa]

Will the data ever be chaged directly in the table tbl_TAT (linked table to SQL) and not in the Access table?

No, the data will be entered/modified only on the Client (regular) table.

Keeping the to table in sync in not as difficult if is is a one direction (access to SQL server ) sync only. You delete the data fromteh SQL Server table and then append al the record from Access.

My understanding is, what you described is a one time thing. SQL table has to be updated whenever there are changes to the Client table.

If it will be a two way sync then I would highly recommend just using the table in the SQL server for everything.

No, only the Client table in Access will be used.

So is there a way?

 

[HiTechCoach]

No, the data will be entered/modified only on the Client (regular) table.


My understanding is, what you described is a one time thing. SQL table has to be updated whenever there are changes to the Client table.

The delete and append can be run as often as needed. It could be run for a single record, multiple records, or all the records.

You could always use an update query instead of a delete and append. Which method you use depends in your needs and if there are an related tables in the SQL Server where a delete would violate the enforcement of referential integrity.

So is there a way?

Yes there is a way.

After the record is saved, you will have to use VBA code to apply the same changes to the record in the SQL server.

You could delete the single record from the SQL Server (SS) and append the updated one from Access.

You could use an update query to update the SQL Server record to match the record in Access.

WARNING: There is always room of issues when trying to sync data. Make sure to have good error handling sand recovery methods.

The best way to insure the SQL Server table is always up-to-date is to just use the SQL server table.

I have several Access application that use an Access back end and an SQL server back end at the same time.