"Parking" linked tables

[Guest]

Hello,

My application extracts data from linked tables into local ones. Users take
their laptops and work disconnected on the road.

Some subform have controls (combo row source) bound to linked tables. If PC
is disconnected from LAN, every time form opens error pops up from the linked
control in the subform with "...Make sure the Path is spelled correctly."
with OK and Help buttons. Then form works fine - otherwise. I need this
corrected.

First I am thinking to try is to make the controls unbound and bind them in
sub-form's Current event, provided the datasource is present.

Or.

I also discovered that I could create an empty table in the same MDB file
(import structure only) and link the linked table to it in code when not on
the LAN.

Although doable, both seem like a brut-force and somewhat complicated
solutions. Also kind of clumsy.

Is there an elegant way to somehow "park" the linked tables when datasource
is not present?

Thank you,
Marat.

 

[Roger Carlson]

Have you looked into Replication?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Roger -

I am accessing many multiple similar mdb's and collecting needed data from
them into one "summary" mdb by linking to each in source mdb in sequence.
The summary mdb is not the same the sources mdb. It's a little
"datawarehouse"-like contraption.

There is no data synchronization going on since my users don't update the
MDB on laptops, only report from the data. The data is collected overnight
and the refreshed mdb with the reports is copied as local mdb to laptops when
users connect to the LAN.

Would replication help? I will look into it.

Thank you for your response.
Marat.

 

[Guest]

If replication will not do it for you, there is another way.

I am sure that when the user does connect to the main system, you have a way
of pulling his data into the main mdb(s).

Now, what I would suggest is you create local tables that carry the data the
user will need for combos, list boxes, etc. Then when you pull his data
down, also push up updates to those tables and use them instead of the main
system tables.

 

[Guest]

Klatuu,

I am familiar with the technique you are referring to. But here it's not
fitting the bill.

When connected to linked data sources, the combo rowsources are different,
depending on which MDB I am linked to. When there no MDB to link to (no LAN
connection), I want to have a way to suppress the useless msgbox about "path
spelled correctly".

Thank you,
Marat.

 

[Guest]

I understand that. Perhaps I wasn't clear enough. What you need to do it
test to see if the links to the external databases are valid in your startup
routine and if not, use your local tables.

This is not an easy task, it will take a lot of work, but that is the only
way I know of to resolve this problem.

 

[David W. Fenton]

When connected to linked data sources, the combo rowsources are
different, depending on which MDB I am linked to. When there no
MDB to link to (no LAN
connection), I want to have a way to suppress the useless msgbox
about "path
spelled correctly".

Then don't assign a rowsource to the combo box until you know you
can get to the linked table.

An easy way to test this would be something like this, and you'd
call it in the form's OnLoad event:

Public Sub SetComboBoxRowsource()
On Error GoTo errHandler

Dim rs As DAO.Recordset

Set rs = CurrentDB.OpenRecordset("[combo box linked table name]")
rs.Close
Set rs = Nothing
Me!cmbMyComboBox.Rowsource = [rowsource]

exitRoutine:
Exit Sub

errHandler:
Select Case Err.Number
Case # ' the number of the error returned when
' the linked table is inaccessible
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation,_
"Error in SetComboBoxRowsource()"
End Select
Resume exitRoutine
End Sub

And edit the form to have no rowsource defined for the combo box by
default.

 

[John Nurick]

How about using two back-end MDBs, the main one on the network and a
second on the local drive?

All data access in the front end would be done via linked tables, but
you'd have code to re-link the tables to one or the other back end
depending on circumstances.
http://www.mvps.org/access/tables/tbl0009.htm shows the sort of thing
that's involved.

The "local" back end could either be a simple copy of the main one or
contain a subset of the data, updated whenever the machine is connected
to the network.

 

[Guest]

John,

This is exactly what I ended up doing. The only inconvenience was that I had
to explain to the IT director in writing why I needed "an empty copy" on a
file server.


Thank you much for all the answers people!

Marat.