If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Rename Linked Table (If Needed)
I'm working on a database that will be used to track the processing of
soldiers for our state for upcoming mobilizations. I've gotten alot of great ideas and tips from these boards which have kept this DB extremely functional and lightweight. Now I need another inspiration. This DB will be used as a FE/BE app about 70% of the time. The remaining 30% it will have to be used as a standalone due to the inevitability of no network connectivity. No matter which way it's used, I'm looking at anywhere from 2-10 concurrent users. I have a single linked table which will be the key to this project (tblSRP). I also have a local table (tblSRP_Temp). In the event of standalone mode, I need a way to: 1 - test for network connectivity (recordset.count or Dir(Path)...? 2 - If no network, then rename these tables to each other's name (flip-flop) so the user is using a local 'tblSRP'. Along this thinking, a hidden form would reset their names on DB close. This is so my dialog box/browse/select db code will import the 'tblSRP_Temp' then append it to the 'tblSRP' housed in the backend. I'm wanting to keep this transparent to the user while keeping things easy and compact. So, in a nutshell: - on the network, do nothing - not on network, rename 'tblSRP' to 'tblSRP_Temp"...rename 'tblSRP_Temp' to 'tblSRP' - on hidden form close, ensure the linked table is named 'tblSRP' and the other is name 'tblSRP_Temp'. Thanks for the great tips and ideas...hope you can help with this one. If there's another way I can tackle this, I'm open to any/all ideas. |
#2
|
|||
|
|||
Rename Linked Table (If Needed)
On Wed, 29 Oct 2008 09:51:02 -0700, David Mulholland
wrote: I'm working on a database that will be used to track the processing of soldiers for our state for upcoming mobilizations. I've gotten alot of great ideas and tips from these boards which have kept this DB extremely functional and lightweight. Now I need another inspiration. This DB will be used as a FE/BE app about 70% of the time. The remaining 30% it will have to be used as a standalone due to the inevitability of no network connectivity. No matter which way it's used, I'm looking at anywhere from 2-10 concurrent users. I'd use it as a FE/BE at ALL times. The backend can be on the same computer as the frontend (in fact that's how I do almost all my development work). There would then be no need to mess around with the structure - just relink to the current location of the backend, whether that's on the network or just in a folder on the local computer. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Rename Linked Table (If Needed)
I do use FE/BEs with all my other databases. It's just this one case i'm
trying to anticipate for. In the event of no network connectivity, my multiple users won't be able to use a BE anyway. Even if we could get our IT department to create an ad hoc network for these users, it's just begging for trouble. I'm just wanting to do this not oanly for ease of use, but also as a knowledge exercise. "John W. Vinson" wrote: On Wed, 29 Oct 2008 09:51:02 -0700, David Mulholland wrote: I'm working on a database that will be used to track the processing of soldiers for our state for upcoming mobilizations. I've gotten alot of great ideas and tips from these boards which have kept this DB extremely functional and lightweight. Now I need another inspiration. This DB will be used as a FE/BE app about 70% of the time. The remaining 30% it will have to be used as a standalone due to the inevitability of no network connectivity. No matter which way it's used, I'm looking at anywhere from 2-10 concurrent users. I'd use it as a FE/BE at ALL times. The backend can be on the same computer as the frontend (in fact that's how I do almost all my development work). There would then be no need to mess around with the structure - just relink to the current location of the backend, whether that's on the network or just in a folder on the local computer. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Rename Linked Table (If Needed)
I'd suggest a different approach which avoids having to rename the tables at
all. Wherever in the application you currently reference the table, instead reference a query, qrySRP: SELECT * FROM tblSRP; Then at start-up amend the query's SQL property if a recordset object cannot be established on the basis of the linked table: Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim qdf As DAO.QueryDef Set dbs = CurrentDb Set qdf = dbs.QueryDefs("qrySRP") On Error Resume Next Set rst = dbs.OpenRecordset("tblSRP") If Err.Number = 0 Then qdf.SQL = "SELECT * FROM tblSRP DoCmd.SetWarnings False DoCmd.OpenQuery "qryAppendToSRP" DoCmd.SetWarnings True Else qdf.SQL = "SELECT * FROM tblSRP_Temp" End If The qryAppendToSRP query would append any outstanding rows from tblSRP_Temp to tblSRP once its established that access to that table in the back end is available via the linked tabledef object. By calling the OpenQuery method to do this and setting warnings temporarily off only rows which do not violate any key or index constraints will be appended. The SQL from this query can therefore remain constant as: INSERT INTO tblSRP (column list) SELECT column list FROM tblSRP_Temp; inserting the column list as appropriate. On close-down there would be no need to amend the SQL property back to selecting rows from tblSRP as the query's SQL property is updated at start-up before any other action is taken. The above should work whether the tblSRP_Temp is in the front end or a linked table on the local machine as John suggests. I'd concur with his view that the latter is preferable. Ken Sheridan Stafford, England "David Mulholland" wrote: I'm working on a database that will be used to track the processing of soldiers for our state for upcoming mobilizations. I've gotten alot of great ideas and tips from these boards which have kept this DB extremely functional and lightweight. Now I need another inspiration. This DB will be used as a FE/BE app about 70% of the time. The remaining 30% it will have to be used as a standalone due to the inevitability of no network connectivity. No matter which way it's used, I'm looking at anywhere from 2-10 concurrent users. I have a single linked table which will be the key to this project (tblSRP). I also have a local table (tblSRP_Temp). In the event of standalone mode, I need a way to: 1 - test for network connectivity (recordset.count or Dir(Path)...? 2 - If no network, then rename these tables to each other's name (flip-flop) so the user is using a local 'tblSRP'. Along this thinking, a hidden form would reset their names on DB close. This is so my dialog box/browse/select db code will import the 'tblSRP_Temp' then append it to the 'tblSRP' housed in the backend. I'm wanting to keep this transparent to the user while keeping things easy and compact. So, in a nutshell: - on the network, do nothing - not on network, rename 'tblSRP' to 'tblSRP_Temp"...rename 'tblSRP_Temp' to 'tblSRP' - on hidden form close, ensure the linked table is named 'tblSRP' and the other is name 'tblSRP_Temp'. Thanks for the great tips and ideas...hope you can help with this one. If there's another way I can tackle this, I'm open to any/all ideas. |
#5
|
|||
|
|||
Rename Linked Table (If Needed)
After testing this out, it looks like it's doing what I need it to do. Great
idea. Nice, clean and elegant. I doubt I would've thought about dynamically changing the base SQL statement. I knew there was a reason I frequent these boards. THANKS!! "Ken Sheridan" wrote: I'd suggest a different approach which avoids having to rename the tables at all. Wherever in the application you currently reference the table, instead reference a query, qrySRP: SELECT * FROM tblSRP; Then at start-up amend the query's SQL property if a recordset object cannot be established on the basis of the linked table: Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim qdf As DAO.QueryDef Set dbs = CurrentDb Set qdf = dbs.QueryDefs("qrySRP") On Error Resume Next Set rst = dbs.OpenRecordset("tblSRP") If Err.Number = 0 Then qdf.SQL = "SELECT * FROM tblSRP DoCmd.SetWarnings False DoCmd.OpenQuery "qryAppendToSRP" DoCmd.SetWarnings True Else qdf.SQL = "SELECT * FROM tblSRP_Temp" End If The qryAppendToSRP query would append any outstanding rows from tblSRP_Temp to tblSRP once its established that access to that table in the back end is available via the linked tabledef object. By calling the OpenQuery method to do this and setting warnings temporarily off only rows which do not violate any key or index constraints will be appended. The SQL from this query can therefore remain constant as: INSERT INTO tblSRP (column list) SELECT column list FROM tblSRP_Temp; inserting the column list as appropriate. On close-down there would be no need to amend the SQL property back to selecting rows from tblSRP as the query's SQL property is updated at start-up before any other action is taken. The above should work whether the tblSRP_Temp is in the front end or a linked table on the local machine as John suggests. I'd concur with his view that the latter is preferable. Ken Sheridan Stafford, England "David Mulholland" wrote: I'm working on a database that will be used to track the processing of soldiers for our state for upcoming mobilizations. I've gotten alot of great ideas and tips from these boards which have kept this DB extremely functional and lightweight. Now I need another inspiration. This DB will be used as a FE/BE app about 70% of the time. The remaining 30% it will have to be used as a standalone due to the inevitability of no network connectivity. No matter which way it's used, I'm looking at anywhere from 2-10 concurrent users. I have a single linked table which will be the key to this project (tblSRP). I also have a local table (tblSRP_Temp). In the event of standalone mode, I need a way to: 1 - test for network connectivity (recordset.count or Dir(Path)...? 2 - If no network, then rename these tables to each other's name (flip-flop) so the user is using a local 'tblSRP'. Along this thinking, a hidden form would reset their names on DB close. This is so my dialog box/browse/select db code will import the 'tblSRP_Temp' then append it to the 'tblSRP' housed in the backend. I'm wanting to keep this transparent to the user while keeping things easy and compact. So, in a nutshell: - on the network, do nothing - not on network, rename 'tblSRP' to 'tblSRP_Temp"...rename 'tblSRP_Temp' to 'tblSRP' - on hidden form close, ensure the linked table is named 'tblSRP' and the other is name 'tblSRP_Temp'. Thanks for the great tips and ideas...hope you can help with this one. If there's another way I can tackle this, I'm open to any/all ideas. |
#6
|
|||
|
|||
Rename Linked Table (If Needed)
On Wed, 29 Oct 2008 11:07:02 -0700, David Mulholland
wrote: I do use FE/BEs with all my other databases. It's just this one case i'm trying to anticipate for. In the event of no network connectivity, my multiple users won't be able to use a BE anyway. You are mistaken. You *can* use a FE/BE architecture without a network. The user will *not* need network connectivity. A single user, on a single laptop, with no ethernet cable and with wi-fi turned off, CAN use a FE/BE database - just have the FE and the BE on the same computer, even in the same folder. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Rename Linked Table (If Needed)
True...I wasn't quite clear in my last post. I should have added that since
I'm looking at having up to 10 disconnected users...if each had FE/BE on their comps, the workload to ensure to sync their data to the 'master' would be a pain. This due to users who would input their data, then for whatever reason, be relased and fail to get that data synced. Been there, done that. Juggling this seemed more trouble than it's worth. I tried Ken Sheridan's approach and it's doing what I need it to do. I appreciate your time and experience. "John W. Vinson" wrote: On Wed, 29 Oct 2008 11:07:02 -0700, David Mulholland wrote: I do use FE/BEs with all my other databases. It's just this one case i'm trying to anticipate for. In the event of no network connectivity, my multiple users won't be able to use a BE anyway. You are mistaken. You *can* use a FE/BE architecture without a network. The user will *not* need network connectivity. A single user, on a single laptop, with no ethernet cable and with wi-fi turned off, CAN use a FE/BE database - just have the FE and the BE on the same computer, even in the same folder. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|