Hints for Best Practice to work with SQL Server data mixed with MS access data tables

[Luthius]

Guys
We have a CMMS that its database is SQL Server.
My intention is to retrieve some information that is already there and use it in my MS Access database for Issues management.
I already setup a DSN to connect to SQL Server and works very well.

I would like your tips and hints with the best way to manage my MS Database because I will use some tables from SQL Server database getting their Primary Keys so I can do the relationship with my Issue table for instance.

My concern is that this database will work in a network and I don't want it too slow.

There are tons of records on SQL Server database. It's not necessary to get all of these data everytime, so I want to work in a clever way optmizing the connection/access to SQL Server and also to the backend saved in my network.

Does anybody already work with such setup mixing data from SQL Server and MS Access?

Luthius

 

[xenou]

The only rule of thumb I know is to reduce the amount of data "going over the wire". So aim for that. For instance, rather than pulling all the data from a table, pull only the records you need to see. I wouldn't worry too much about performance unless you have bad performance - start with what seems obvious to you and then see how it works.