SQL Server 2008 R2 – Linked Servers Through Linked Tables in MS-Access

ms accessodbcsql servervisual-foxpro

We have a really old data source (file-based) in Visual FoxPro, that we currently use via linked table through an ODBC data source. Recently, we started having an issue because the latest update to our Anti-virus Suite is stopping it from connecting in this manner. There are other ODBC data sources which do not use the same driver which do not have this issue.

Also the Visual FoxPro driver ODBC driver is being shoehorned in using the registry because it's no longer supported by Windows 7.

Is adding my Visual FoxPro data source as a linked server in SQL server and then in the MS-Access database, relinking the effected tables to it a possible solution to this issue? Note: the MS-Access databases use VBA and queries to retrieve and manipulate the data.

Best Answer

You should be able to get away with this by creating views in SQL Server that are effectively wrappers for tables in the linked server. You will of course have to deal with the extra layer of security configuration for the linked server, thus ensuring that the Access users are connecting to the external data with the proper credentials (whether it be something per-user, or just a global set of credentials that's used for everybody accessing the FoxPro data).

Keep a close eye on performance, since Access is going to be generating queries meant for SQL Server, and it tends to be a bit chatty to begin with. SQL Server is then going to generate queries for fetching the data from FoxPro. With that many layers of query generation, performance could be a crapshoot for large tables, and it'll be pretty difficult to diagnose or improve it.

If the FoxPro data is mostly static historic data, then I'd recommend bringing it into SQL Server. If there's still read/write activity happening, then you'll have to either settle for the linked server approach, or periodically refreshing the data in SQL Server via SSIS or some other ETL.

Related Solutions

Ms-access ODBC calls for linked table behaviour

The only way to get complete control over the query for ODBC is to create them as pass-through queries.

In that mode, Access will not touch them and the SQL will be passed verbatim to the server (so your queries will need to be written in that server's particular SQL syntax or you'll get errors).

In ODBC mode, Access will decompose queries that are bound to controls into batches so it never returns more results than it need to display (that is what you see: it will decompose queries based on the primary key to return specific records only).

So you have to be careful of what you wish: if you optimise your queries using pass-through, you will get less calls, but all the data will be returned.

On the other hand, if you leave Access do the querying for you, it may be chattier, but the net performance may be better since less data may be returned more quickly.

You need to test both and choose what works best depending on the exact query and how it is used.

Postgresql – limit access to postgresql 8.4 databases via ODBC on the server side

ODBC is a client tool and does not affect the way that Postgresql verifies access. I don't know if its any different for a postgresql server on windows, but in linux, there is a file called pg_hba.conf. This file is usually located in the data directory of the server install. In Linux, you typically find it in /var/lib/pgsql/data/.

This file allows you to define which ip addresses can access your postgresql server and how they may access it. Types of access include trust (no password), md5 (password), etc.

So for example, if your access users are on say 192.168.0.10 and you want them to use a password you would add this entry.

# TYPE  DATABASE    USER        IP-ADDRESS    IP-MASK             METHOD
host    all         access_user         192.168.0.10    255.255.255.255     md5

In postgresql you would then have to create the user and give him the ability to login.

1. psql -d nameofdatabase
2. CREATE ROLE access_user WITH LOGIN PASSWORD 'jw8s0F4';
3. GRANT SELECT ON nameoftable TO access_user;

Good luck

Best Answer

Related Solutions

Ms-access ODBC calls for linked table behaviour

Postgresql – limit access to postgresql 8.4 databases via ODBC on the server side

Related Question