I have developed a small database which is setup with the front end and
backend files.
There are 12 linked tables withe the datafile located on our server.
Now the boss wants to be able to take a laptop offsite and still have
access, (even if read only) to the main data file (to be able to add records
in 1 table would also be useful).
I would like to be able to get the user to press a command button which
copies the data file into a local directory and updates the link paths for
the tables. This also incudes the linked images I have for the forms
I can handle getting information that is added back into the main file, just
the auto linking (user friendly) of the tables has me.
Any help?, I have looked at the code suggested on Allen Browne's tip site
but I can't see where that is going or how to adapt it to my use.
Regards
Sam 3 3020
Thanks for you comments Allen
have come up with something that looks like it should work but haven't
tried it, is it possible to see if I am heading in the right direction?
Code as follows (in General module, name Module 1)
Function CheckConnect()
Dim db As Database, strnetpath As String, srtlocalpath As String, strsource
As String
Dim strfile As String
Dim i As Integer
Set db = DBEngine.Worksp aces(0).Databas es(0)
strfile = "V3 Data.mdb"
strlocalpath = "C:\My Documents\"
strnetpath = "X:\AA PUBLIC FILES\Database\ "
'check network path
If IsNull(Dir(strn etpath)) Then GoTo LocalPath
'else if db.path<> strnetpath then
For i = 0 To db.TableDefs.Co unt - 1
db.TableDefs(i) .Connect = ";Database= " + strnetpath + strfile
db.TableDefs(1) .RefreshLink
Next
End If
End
LocalPath:
MsgBox "Access has detected that the Network is Not Present" & Chr(13) &
"Press OK to Load Data From Local File", vbYesCancel
For i = 0 To db.TableDefs.Co unt - 1
db.TableDefs(i) .Connect = ";Database= " + strlocalpath + strfile
db.TableDefs(1) .RefreshLink
Next
End Function
Regards
Sam
"Allen Browne" <ab************ ***@bigpond.net .au> wrote in message
news:6j******** ***********@new s-server.bigpond. net.au... It's a matter of setting the Connect property of each TableDef,
remembering to RefreshLink.
On startup, you want to see if the data file is available. Use Dir(). If it's there, then loop through the TableDefs, and the non-system files that have a Connect property that's different, set it to the network path.
If the Dir on the network back end fails, you probably want to pop up a MsgBox() asking whether to use the local copy of the data. If the user agrees, then set the Connect property to the local data file. You may also want to modify the title bar of the application to show it's a local copy not the real data that is being updated. Use dbEngine(0)(0). Properties("App Title"), and Application.Ref reshTitleBar.
Presumably you will store the usual network path in a table so you know where to look again next startup.
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html
"Sam" <sa********@hot mail.com> wrote in message news:Ck******** ***********@new s-server.bigpond. net.au... I have developed a small database which is setup with the front end and backend files.
There are 12 linked tables withe the datafile located on our server.
Now the boss wants to be able to take a laptop offsite and still have access, (even if read only) to the main data file (to be able to add records in 1 table would also be useful).
I would like to be able to get the user to press a command button which copies the data file into a local directory and updates the link paths
for the tables. This also incudes the linked images I have for the forms
I can handle getting information that is added back into the main file, just the auto linking (user friendly) of the tables has me.
Any help?, I have looked at the code suggested on Allen Browne's tip
site but I can't see where that is going or how to adapt it to my use.
Regards
Sam
Yes, that's the general idea.
With each TableDef, check:
- it's name does not start with "MSYS" (the hidden system tables) or "~"
(deleted tables);
- it has a Connect property (i.e. you do not want to set it for locacl
tables).
There's a bit of debugging to do, e.g. you will need to use the MsgBox()
function (rather than statement) in the LocalPath code, and use an "i" (not
1) in the RefreshLink lines.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
"Sam" <sa********@hot mail.com> wrote in message
news:HR******** ***********@new s-server.bigpond. net.au... Thanks for you comments Allen
have come up with something that looks like it should work but haven't tried it, is it possible to see if I am heading in the right direction?
Code as follows (in General module, name Module 1)
Function CheckConnect() Dim db As Database, strnetpath As String, srtlocalpath As String,
strsource As String Dim strfile As String Dim i As Integer
Set db = DBEngine.Worksp aces(0).Databas es(0) strfile = "V3 Data.mdb" strlocalpath = "C:\My Documents\" strnetpath = "X:\AA PUBLIC FILES\Database\ "
'check network path If IsNull(Dir(strn etpath)) Then GoTo LocalPath 'else if db.path<> strnetpath then For i = 0 To db.TableDefs.Co unt - 1 db.TableDefs(i) .Connect = ";Database= " + strnetpath + strfile db.TableDefs(1) .RefreshLink Next End If End LocalPath: MsgBox "Access has detected that the Network is Not Present" & Chr(13) & "Press OK to Load Data From Local File", vbYesCancel
For i = 0 To db.TableDefs.Co unt - 1 db.TableDefs(i) .Connect = ";Database= " + strlocalpath + strfile db.TableDefs(1) .RefreshLink Next
End Function
Regards
Sam "Allen Browne" <ab************ ***@bigpond.net .au> wrote in message news:6j******** ***********@new s-server.bigpond. net.au... It's a matter of setting the Connect property of each TableDef, remembering to RefreshLink.
On startup, you want to see if the data file is available. Use Dir(). If it's there, then loop through the TableDefs, and the non-system files
that have a Connect property that's different, set it to the network path.
If the Dir on the network back end fails, you probably want to pop up a MsgBox() asking whether to use the local copy of the data. If the user agrees, then set the Connect property to the local data file. You may
also want to modify the title bar of the application to show it's a local
copy not the real data that is being updated. Use dbEngine(0)(0). Properties("App Title"), and Application.Ref reshTitleBar.
Presumably you will store the usual network path in a table so you know where to look again next startup.
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html
"Sam" <sa********@hot mail.com> wrote in message news:Ck******** ***********@new s-server.bigpond. net.au... I have developed a small database which is setup with the front end
and backend files.
There are 12 linked tables withe the datafile located on our server.
Now the boss wants to be able to take a laptop offsite and still have access, (even if read only) to the main data file (to be able to add records in 1 table would also be useful).
I would like to be able to get the user to press a command button
which copies the data file into a local directory and updates the link paths for the tables. This also incudes the linked images I have for the forms
I can handle getting information that is added back into the main
file, just the auto linking (user friendly) of the tables has me.
Any help?, I have looked at the code suggested on Allen Browne's tip site but I can't see where that is going or how to adapt it to my use.
Regards
Sam
1. Use error handling to recover.
If you don't know how to use error handling, see: http://allenbrowne.com/ser-23a.html
2. Make sure you have excluded the system, temp, and local tables. Assign
the value to a string variable, and Debug.Print it to check it's okay. For
example the path needs the trailing slash. (You might also try the ampersand
for concatenation.)
3. Names of tables
Dim tdf As DAO.TableDef
For each tdf In db.TableDefs
If Left(tdf.Name, 4) = "MSys" ...
4. ???
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
"Sam" <sa********@hot mail.com> wrote in message
news:Jz******** ***********@new s-server.bigpond. net.au... Good to know I am on the right track, however the code that I have has a
few errors 1. The line: If isnull(Dir(strn etpath)) Then GoTo LocalPath gives an error (when logged off the server): Device not available 2. The line : db.TableDefs(i) .Connect = ";Database= " + strlocalpath + strfile also gives an error "Invalid operation" 3. You mentioned the system and hidden tables starting with msys and ~,
I can't find a way to look at the start of a table name. I have no local tables, so the need to not reconnect those is not required. 4. The Line I have in as a statement is like it is as I wanted to show
I want to put the code there but havn't found how to write what I want it to do (yet) ( 'else if db.path<> strnetpath then)
Any help, I am trying to brute force my way though learning the code but
it seems like I have a long way to go!
Regards
Sam "Allen Browne" <ab************ ***@bigpond.net .au> wrote in message news:JR******** ***********@new s-server.bigpond. net.au... Yes, that's the general idea.
With each TableDef, check: - it's name does not start with "MSYS" (the hidden system tables) or "~" (deleted tables); - it has a Connect property (i.e. you do not want to set it for locacl tables).
There's a bit of debugging to do, e.g. you will need to use the MsgBox() function (rather than statement) in the LocalPath code, and use an "i" (not 1) in the RefreshLink lines.
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html
"Sam" <sa********@hot mail.com> wrote in message news:HR******** ***********@new s-server.bigpond. net.au... Thanks for you comments Allen
have come up with something that looks like it should work but
haven't tried it, is it possible to see if I am heading in the right
direction? Code as follows (in General module, name Module 1)
Function CheckConnect() Dim db As Database, strnetpath As String, srtlocalpath As String, strsource As String Dim strfile As String Dim i As Integer
Set db = DBEngine.Worksp aces(0).Databas es(0) strfile = "V3 Data.mdb" strlocalpath = "C:\My Documents\" strnetpath = "X:\AA PUBLIC FILES\Database\ "
'check network path If IsNull(Dir(strn etpath)) Then GoTo LocalPath 'else if db.path<> strnetpath then For i = 0 To db.TableDefs.Co unt - 1 db.TableDefs(i) .Connect = ";Database= " + strnetpath + strfile db.TableDefs(1) .RefreshLink Next End If End LocalPath: MsgBox "Access has detected that the Network is Not Present" & Chr(13)
& "Press OK to Load Data From Local File", vbYesCancel
For i = 0 To db.TableDefs.Co unt - 1 db.TableDefs(i) .Connect = ";Database= " + strlocalpath +
strfile db.TableDefs(1) .RefreshLink Next
End Function
Regards
Sam "Allen Browne" <ab************ ***@bigpond.net .au> wrote in message news:6j******** ***********@new s-server.bigpond. net.au... > It's a matter of setting the Connect property of each TableDef, remembering > to RefreshLink. > > On startup, you want to see if the data file is available. Use
Dir(). If > it's there, then loop through the TableDefs, and the non-system
files that > have a Connect property that's different, set it to the network
path. > > If the Dir on the network back end fails, you probably want to pop
up a > MsgBox() asking whether to use the local copy of the data. If the
user > agrees, then set the Connect property to the local data file. You
may also > want to modify the title bar of the application to show it's a local copy > not the real data that is being updated. Use > dbEngine(0)(0). Properties("App Title"), and Application.Ref reshTitleBar. > > Presumably you will store the usual network path in a table so you know > where to look again next startup. > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia. > Tips for Access users - http://allenbrowne.com/tips.html > > "Sam" <sa********@hot mail.com> wrote in message > news:Ck******** ***********@new s-server.bigpond. net.au... > > I have developed a small database which is setup with the front
end and > > backend files. > > > > There are 12 linked tables withe the datafile located on our
server. > > > > Now the boss wants to be able to take a laptop offsite and still
have > > access, (even if read only) to the main data file (to be able to
add > records > > in 1 table would also be useful). > > > > I would like to be able to get the user to press a command button which > > copies the data file into a local directory and updates the link paths for > > the tables. This also incudes the linked images I have for the
forms > > > > I can handle getting information that is added back into the main file, > just > > the auto linking (user friendly) of the tables has me. > > > > Any help?, I have looked at the code suggested on Allen Browne's
tip site > > but I can't see where that is going or how to adapt it to my use. > > > > Regards > > > > Sam
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Larry L |
last post by:
Access is noted for bloating a database when you add and delete records
frequently. I have always had mine set to compact on close, and that works
great. Now after everyone's advice I split my database, so the data is in
a second (back-end) database with all the tables linked. However, now when
I close the database, it compacts the front end, since that's what's open,
and the back-end grows.
I now have to manually open and close the...
|
by: DD |
last post by:
I have the following code
and my question is
Do i use a Autoexec to fire this ?
As: fGetLinkedTables("ODBC")
Or how do i ?? do i replace the above as
fGetLinkedTables("test_be")
Or ???
|
by: raydelex |
last post by:
I have a split database (Access 2003).
Now I need to port the database to the end-users' computer for a
demonstration. When I try to start up the database on the other
computer, it tells me that it cannot find the back-end database.
How do I tell the the "front-end" database where to locate the
"bank-end" DB that contains the actual tables on a computer other than
the one on which it was developed?
|
by: nydiroth |
last post by:
Our servers went down for over 6 hours yesterday and I was asked if
there was a way to store the database on the server and a local station
at the same time.
My datbase is split and the tables are linked. Would there be a way to
modify the table links to direct them to two seperate backends? Could
there also be a non-linked table stored in the frontend alongside the
table links?
Our endpoint would be to have a mirror on the network...
|
by: QBCM |
last post by:
We split our Access database as suggested with a front end application
on local machines and back end tables on a data server. We need to
add a field to one of the tables but it looks as if splitting the
database prevents us from making changes to the structure of the
tables. Is there any way around this problem?
Many thanks
| |
by: carriolan |
last post by:
Hi
I have managed to secure a split database. Both frontend and backend
share a common workgroup, common security groups and common users and
permissions, but as in all good stories there is a catch.
All works well for the newAdmin user, but not so for the newUser. In
the AutoExec macro I have a call on some VBA to reconnect the frontend
and backend databases. It does not reconnect for the newUser. My
conclusion is that the security...
|
by: najimou |
last post by:
Hi everyone
I will be having a split database, running on 2 computers via mapped
drive.
computer "A" will have one front end and the back end located in c:
\mydatabse
2 tables have links to many images stored as c:\mydatabse\images
\images.jpg
so computer A has no problem accessing the picture
|
by: Stan |
last post by:
I am working on a database in ACCESS 2003. This is a simple DB with
only one table. I have successfully split the database into
database.mdb and database_be.mdb. When I copy the two files to a
clients' computer and try to run database.mdb I receive the following
error message "database_be.mdb is not a valid path".
database_be.mdb runs OK. I have tried the utility Tools < Database
Utilities < Linked Table Manager and Compact and...
|
by: elgin |
last post by:
I have a split Access 2003 database. I have signed the database with a
Code Signing Certificate from Small Business Server. This works fine
and users can have Access macro security on high or medium and do not
get prompted at startup.
The problem comes because there are two of us modifying the code.
Whenever either one of us changes the code, one of us must resign both
the front and back end of the database. If we forget to sign both, we...
|
by: John Google |
last post by:
Hi,
I've been asked to amend an Access 2002 DB that has been used by users
using the MDE version. I have, what seems to be the original MDB
version containing all the front end stuff but, by now, the tables are
way out of date.
They want me to make some changes to the database and keep their
current data..
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |