 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / January 2007"Parking" linked tables
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. Have you looked into Replication?  Signature--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 > Hello, > [quoted text clipped - 24 lines] > Thank you, > Marat. 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. > Have you looked into Replication? > [quoted text clipped - 32 lines] > > Thank you, > > Marat. 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. > Roger - > [quoted text clipped - 49 lines] > > > Thank you, > > > Marat. 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. > If replication will not do it for you, there is another way. > [quoted text clipped - 5 lines] > down, also push up updates to those tables and use them instead of the main > system tables. 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. > Klatuu, > [quoted text clipped - 18 lines] > > down, also push up updates to those tables and use them instead of the main > > system tables. > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ 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. >Klatuu, > [quoted text clipped - 18 lines] >> down, also push up updates to those tables and use them instead of the main >> system tables. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. 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. > How about using two back-end MDBs, the main one on the network and a > second on the local drive? [quoted text clipped - 36 lines] > > Please respond in the newgroup and not by email. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.