473,885 Members | 2,468 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Linking and disconnecting from backend databases

cmd
I use a utility database and the following code to link from an
original backend to a temporary backend, in order to replace the
original with a newer version:

Dim dbs As Database
Dim tdf As TableDef
Dim Tdfs As TableDefs
Dim Pathname As String
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs

'LINKING TO THE ORIGINAL BACKEND
Pathname = "C:\Access97\fp sdata.mdb"
For Each tdf In Tdfs
On Error Resume Next
If tdf.SourceTable Name <> "" Then
tdf.Connect = ";DATABASE= " & Pathname
tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table

'LINKING TO A TEMPORARY BACKEND:

Pathname = "C:\Transfer\fp sdata.mdb"
For Each tdf In Tdfs
On Error Resume Next
If tdf.SourceTable Name <> "" Then
tdf.Connect = ";DATABASE= " & Pathname
tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table

'ATTEMPTING TO REPLACE THE FIRST BACKEND WITH ANOTHER VERSION:

Filecopy "C:\NewVersion\ fpsdata.mdb, "C:\Access97\fp sdata.mdb"

-- THE FILECOPY ACTION FAILS TO REPLACE C:\Access97\fps data.mdb. THE
PROBLEM IS THAT THE UTILITY DATABASE I'M IN EVIDENTLY DOES NOT FULLY
RELEASE OR CLOSE C:\ACCESS97\FPS DATA.MDB, EVEN AFTER LINKING TO THE
SECOND DATABASE.

DO I NEED TO SET 'dbs' AND 'Tdfs' TO NOTHING SOMEWHERE ALONG THE WAY
--AND THEN RE-SET THEM PRIOR TO DOING ANOTHER TABLE-LINK?

THANK YOU.
MARK.

Nov 13 '05 #1
2 3482

<cm*@mountain.n et> schreef in bericht news:11******** *************@o 13g2000cwo.goog legroups.com...
I use a utility database and the following code to link from an
original backend to a temporary backend, in order to replace the
original with a newer version:

<snip code >

Hi Mark,
IMO it's a bit unusual to replace the backend. Mostly we replace the Frontend don't we ??
If you replace the backend, well why would you do so ? You will lose all new data ??
Is this some kind of restore procedure ??

==>> IMO there is NO need to link to another db to replace the file.
To replace the backend this is what I think you need:
You need to make sure there is no 'active' connection; no open recordsets,
And also: No open form with bound controls !

I just tested:
I Opened one of the Access 97 applications I have: (fe-be).
My main menu opens (unbound)
From this menu I click on a button with code like:

Private Sub Button_Click()
FileCopy "C:\ArraCom\Kla vier\kla_be.bak ", "C:\ArraCom\Kla vier\kla_be.mdb "
End Sub

This works and replaces the backend.
If I also have opened one of the bound forms then: No go (error: access refused)
If I close the bound form again: Code works OK

--
Hope this helps
Arno R
Nov 13 '05 #2
cmd
Thanks Arno,
This involves updating a user's home computer with data from the
office. The "parent" records for the backend database are based on
client-id numbers (autonumber), with a lot of different types of
"child" records associated with each client-id number. The user does
not add "parent" records, but does create and edit "child" records on
their home computer.

The task is to allow the user to retain their edited/created "child"
records on their home computer, while updating their backend in order
to receive any new "parent" records added at the main office.

Therefore, I was using the utility database to first link to the user's
backend database to retrieve any edited/created "child" records of
theirs. After replacing their backend database with the newer office
copy, the retrieved "child" records are appended, based on matching
client-id numbers. (Actually, any MATCHING "child" records are adjusted
for edits; then any unmatching "child" records are appended).

I've re-thought the approach, however; now I simply open the utility
database and before refreshing any links, use FileCopy to create a
Temp.mdb of their existing backend database. Then I can simply replace
their database with the office version, refresh links to the Temp.mdb
and retrieve (and later append) the user's "child" records from that
database. This avoids refreshing links to the "real" backend databases,
which interferes with the copy process.

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
2117
by: Squirrel | last post by:
I posted a question a couple of days ago about running different versions of Access for the frontend versus the backend databases. Got some good advice but now I've received this information: "We are currently running AppleShare IP 6.3 and this allows machines running Windows to connect via IP." So the first choice of server is a Mac. The client is willing to buy a Windows machine if necessary but would like to use an existing server...
0
2251
by: gasturbtec | last post by:
please help im new at access programming and i just got this project dropped in my lap because the old programmer quit. i've been doing ok so far but now i need to add code to an existing database that is used to connect to other databases and generate reports. below is sample code of how the database does the linking i hope i give you enough info to help me but if not let me know and i will give more. Sub txtShipDataFileSub() Dim...
2
2728
by: jayjay | last post by:
In the database I have, its split into front end screens and reports and backend tables for data. Of course, this means the linked tables are linked using the linked table wizard to the network drive mapping the server is on. since this database will be used by multiple people, it then means that each user must have the same drive mapped to the same drive letter. The linked table manager doesn't recognize UNC encoding. The problem...
16
2693
by: John | last post by:
Hi All, I have two backend databases that area link to a frontend database where information is entered, retrieved and deleted. The information accessed from the frontend database is coming from both databases. Both backend databases are at least 225MB each, therefore accessing data is slow and running queries are slow and frustrating, this causes the databases to crash at times. At the beginning I though it was a network connection, so...
3
1905
by: Mark | last post by:
Hi All, Firstly, I am aware from reading previous posts that if someone is determined enough, they will crack an Access Application. However, this problem seems basic but I can't find a workaround. I have a frontend/backend database at work which contains some sensitive information. I have used Access security along with my own and have disabled the shift key at start-up. I thought this was quite secure but I have found that someone...
1
4021
by: Daveyk0 | last post by:
Hello there, I have a front end database that I have recently made very many changes to to allow off-line use. I keep copies of the databases on my hard drive and link to them rather than the live databases on the network. Is there a way, via code, when I get back in-house from being on the road to click a button, and select the backends I want to link to? I would want to delete all the current links and link to the "live"
10
4486
by: Jennifer Carr | last post by:
I have an Access 2003 application that is split into two .mdb files for the interfaces and data. This app is distributed on a CD to be used on computers that have no internet access. When someone needs an updated dataset the backend .mdb is refreshed from a load of Oracle tables and sent on CD so that the user only needs to replace a single file. The front end links to the tables in the back end. The problem is that the amount of data...
2
1885
by: watto | last post by:
I have a large database application with user data in a backend and forms, code etc in a frontend. It includes a facility for migrating data from an earlier backend to the current backend by linking to the second backend and moving data between corresponding tables. Problem is that Access invariably crashes during this process. I have taken the migrate forms and code into a separate database and observed that the memory used by...
7
3073
by: Salad | last post by:
I am converting an application from A97 to A2003. I have 2 tables created by another application as a Foxpro.dbf. The table has no index. The connect string in A97 is FoxPro 2.0;HDR=NO;IMEX=2;DATABASE=C:\Test It's really easy to connect to those tables in A97. I'm having difficulties in A2003. I'm trying to follow the instructions in http://support.microsoft.com/kb/824264/.
0
9956
marktang
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...
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11172
Oralloy
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...
1
10871
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,...
0
9592
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5808
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4627
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
2
4235
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3245
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.