Linking Access Tables to SQL

[ojosVerdes]

I have an MS Access 2K database that has all its tables linked to SQL 7. I want to be able to refresh the links using a vb App. Has anyone done this before? is it possible?

Thanks in advance.


ojosVerdes

 

[mmilan]

No.

Forgive my asking, but you have VB, you have MSSQL - why would be interested in mucking around with Access?

I'm all pro MSSQL at the moment - just got back from a training course on it.

mmilan

 

[ojosVerdes]

I've been using MSACCESS 2K for quite a while as the backend and vb 6 as the front end. Recently the company I work for, merged with an other company, the other company brought MSSQL7. One of the programmer here sugested to move the data from MSAccess to MSSQL, I am all for it.

Now I have a list of about 40 reports that are MSAccess reports. These reports use linked tables, the database where the reports are used to be linked to the access master database[Now on MSSQL 7]. So now the report have linked table that link to MSSQL.

Every now and then the links brake and I need to re-fresh the links or relink the Access tables to the mssql tables.

I want to acomplish this using VB. I don't want to open access and refresh the links manually.

Hope this clarify things.

 

[mmilan]

It does, but I'm lost for a quick answer.

If Access is installed on the machine you intend to run with, you could perhaps use automation (ie an "Access" object) to do this, but I'm not familiar with that particular object & friends.

mmilan

 

[bjd4jc]

Have you considered using an Access Data project which would not use links but use use SQL to store the data and only Access to hold the reports/forms needed.