Get Access 2003 database to see a linked table, ONLY when an ODBC link is there?

[Fennroth]

Dear all.



Can you explain to me how I can get an Access 2003 database to see a linked table, ONLY when an ODBC link is available? The rest of the time the lack of such a link shouldn't get in the way or cause errors. And it has to be in plain English, I'm not very good at this stuff! Currently I have a linked db but if I change anything to unlink it and work locally I get all sorts of unexplained, general errors with no pointer even as to the module they're in to help.

So I intend to start again based on this premise: The idea is that my database should be portable, probably used from a USB stick. Occasionally (Once or twice a week) it should be plugged into a computer that has an ODBC link to a back end SQL database. In this circumstance, a button should become available that will allow a user to update the local table with a copy of the remote table. Once updated, all the calculating and displaying should work based on the content of the local table on any PC or laptop that is convenient. Only one or two computers have an ODBC link and they are usually in use by others. The database will be used mostly on a laptop in various mobile locations.

I'm sure there are a hundred and one reasons why doing this is considered wrong. But please, can we not concentrate on why I shouldn't do this and instead concentrate on how I can? Are there any articles or tutorials on this anywhere?

Many thanks.

[June7]

How could you work on db that is not linked to data?

You can require all users to use the same folder structure and pathing on each local machine.

Otherwise, if path to the data file is changed by moving the file or renaming folders, need to modify the frontend table links. This can be done manually with the Linked Table Manager or with VBA.

The update of local tables by download from SQL db could possibly use code to set the connection to SQL db and then INSERT and UPDATE sql actions. Would you just want to remove all records from the local table and then INSERT all again from the SQL db?

Is data input to the local copies supposed to feed back into the remote db?

[Fennroth]

Good morning June and thanks for responding.

How could you work on db that is not linked to data?

The db is used by a local council for graffiti analysis. Data from an external source is kept in a SQL server, but with our security system only two specific computers out of the hundered or so we have are ODBC linked. I could explain why this is so for ages, please accept that it is and that it won't be changing. However the data is USED wherever it is convenient. Therefore I will have a local copy of the SQL table.This local copy will enable the user to examine the graffiti, name a tagger, sort it by dates, locations etc and provide reports for identification and court evidence while not connected to the SQL server.

And, there is only one table to look at. Currently it has about 20,000 entries based on about 25 fields.

You can require all users to use the same folder structure and pathing on each local machine.

There is only ever one user at a time but it could be on one of several machines or even mobile at a police station or on a site.

Otherwise, if path to the data file is changed by moving the file or renaming folders, need to modify the frontend table links. This can be done manually with the Linked Table Manager or with VBA.

Not in my case, but thanks.

The update of local tables by download from SQL db could possibly use code to set the connection to SQL db and then INSERT and UPDATE sql actions. Would you just want to remove all records from the local table and then INSERT all again from the SQL db?

Is data input to the local copies supposed to feed back into the remote db?

Once the connection is established I would want to update the SQL table with any modifications that have been made to existing local db records. This will be the addition of text into various fields mostly. Then download any new records from the server. New records won't be made locally, just existing ones modified as fields are updated.

But how do I do that? :/ I am not a great Access guru, I have done a basic course which doesn't include any VB at all and I've picked up off the web my little VB knowledge. I'm reading up on the connection object, I think that's what I need.

Let's assume that the db is on a pen drive. Put the pen into a PC, double click the .mdb file, the control panel form opens. In the on_open event I need a handler that looks for an ODBC link. If it's there then I shall colour a button and set a label to say "Download available" or similar. If it's not then it stays grey and says UNavailable but the rest of the db must still be usable from it's internal tables. How do I get to examine the connection?

And I need to find out about syncronising, but that can wait!

Many thanks for any tips or pointers to
tutorials.

Mike.

[June7]

How do you want to make the connection to the SQL db - by VBA coded connection object or by Access table link?

Google: Access VBA check if connection active
Maybe this will help http://www.excelforum.com/excel-prog...is-active.html

Google: Access VBA check table link
Look at this one http://bytes.com/topic/access/answer...tables-startup

Once connection is established, use UPDATE query to edit the fields of the SQL db with new data from the local db. And an INSERT SELECT to add new records to the local copy from the SQL db.