Can software written to use Access use SQL Server Instead?

[eferro]

Our company purchased some preventative maintenance software from a vendor. One of the tables that the software uses is a machine table that contains machine descriptions, serial numbers, maintenance intervals, etc. We have recently converted our own internal software to SQL Server and our own software has a machine table that contains much of the same information that's used for other purposes besides preventative maintenance. Ideally, we'd like to maintain only one table so that two tables don't get out of sync. Provided we set up our own internal table in SQL server identically to the Access table used by the software, is it possible to force the software that uses the Access table to use the SQL server table instead? What's involved in doing something like this?
Thanks in advance.

 

[r937]

yes, just change the access application so that it uses a linked table instead of its own table

this requires an odbc connection from access to sql server

i think it's under

File > Get External Data > Link Tables


rudy

http://rudy.ca/

 

[Strannik]

Good idea,r937!

To eferro:

You must create system/dile data source to SQL Server, populate server/database/user/password properties. However you won't be able to modify table structure(as Access states) just view data.

 

[SQLSister]

You can modify structure, you just have to do in SQL server and then relink. You can also modify data as long as the table has some kind of unique identifier like a primary key, a timestamp or guid field or a unique constraint.