Access 2003 db to see a linked table, ONLY when an ODBC link is available?

[Cheesysocks]

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.

 

[Pat Hartman]

Welcome aboard
I would create a local table with a list of tables that I want to link to. When the software is running on a machine where the ODBC source is available, you can chose a menu option that will prompt for a server name, link the tables, do the synchronization, and then delete the links.

 

[Cheesysocks]

Welcome aboard
I would create a local table with a list of tables that I want to link to. When the software is running on a machine where the ODBC source is available, you can chose a menu option that will prompt for a server name, link the tables, do the synchronization, and then delete the links.

Thanks for responding Pat. 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.

 

[Pat Hartman]

This is not something that you are capable of doing. Synchronizing data is a complex coding task and is not something I can just whip up psuedo code for to get you started. Since you are using an old (no longer supported) version of Access, you might be able to use briefcase replication to sync the databases. That will also be complex but you won't have to write code to do it. Of course, you will probably also need to be using an outdated version of Windows as well. I'm pretty sure this feature no longer exists.

 

[wobrown]

I am looking into addressing a situation similar to the OP's.

My database (Access 2010) currently depends on several linked tables that connect to an Oracle server via ODBC using a system DSN. The database can only be used when it is connected to the network, but I'd like to be able to work offline.

What might be the best way/s to go about setting up the import of the Oracle tables to (non-linked) access tables, so that I can trigger refreshes in various ways (e.g., on open, on schedule/interval, on-demand)?

I've spent some time looking into this but would greatly appreciate some guidance. It seems like there are many ways to approach this - could it be as simple as running make table queries to convert my linked tables to 'local' tables (using VBA to check for a connection, set conditions and automation, etc)?

Thank you!

 

[Pat Hartman]

If you don't need to update the ODBC tables, importing them periodically works fine as long as the tables aren't too large. The simplest is make-table queries to copy the linked table into the local database. I think there is a bug with earlier Access versions such that this will just result in a second link. If that happens to you, you'll need to run delete and append queries instead.

If you have to update the linked tables, there is no good solution. In that case, you should investigate using Terminal Services or Citrix so the ODBC tables are always available as long as you have an internet connection. Although, Sharepoint might offer another option. I haven't tried it since I don't like Sharepoint but it might work for you.