Hello,
I've got a Split database, where there a lot of back-end databases. There is one Backend-database for each customer. And there is also only one back-end linked at a time.
Now I want to update all of these back-end databases for the release of a new front-end version.
when I try this: - DoCmd.TransferDatabase acLink, "Microsoft Access", Text1.Value, acTable, "Analyses", "Analyses"
-
CurrentDb().Execute "ALTER TABLE analyses ADD Column Caption1 MEMO;"
I got an error "cannot execute data definition statements on linked data sources".
Is there an easy solution for this?
I guess copying the whole table from BE to FE, alter it and copy it again to BE is possible, but that doesn't look very efficient.
thanks,
Stijn
4 2988 patjones 931
Recognized Expert Contributor
Hi Stijnele -
Where are you trying to run this code? In the front end or back end? And in what module?
In a situation like this, if there are any modifications that you need to make to table design, you can simply open the back end file and work with the tables in table design view. Since it seems like you're just trying to add a column, I don't think this should be too hard.
The other thing I would note is that if you have a separate back end for each customer, you might want to look at how your data is structured. It would generally be considered pretty inefficient to have a separate file for each customer...espe cially if you have a lot of customers!
Pat
Hi Pat
Actually I don't know how many back-end databases exist. I guess about 30.
The customers are seperated because they are used on our laptops and it's a must that they can be copied easily in case someone takes over a customer from a collegue.
I'm also working on a front-end for the customers so they can generate the reports on their own.
There is a table in the Back-end database that records the version. The Front-end reads the version and when it sees it's an old version it should add a few columns to the database.
Adding columns manually is not really an option because of the quantity of back-end databases and I'm planning to use this method more in the future for further developing the application.
Stijn
patjones 931
Recognized Expert Contributor
Stijn -
So, the laptops are taken off-site by employees when they go to work with a customer?
Do all 30 of the back ends contain the same type of data (in other words the same tables with the same columns, etc.)? If so, I would say that the easiest way to solve this problem is to combine them into one back end with properly normalized data.
If it's designed correctly it shouldn't be a problem when a customer's account transfers hands internally...be cause you'll have a customer ID or some other way to uniquely identify a particular customer and all their transactions, as well as an employee ID for the person in your organization who handles that customer. When the account changes hands, you'll simply do an update to the employee ID to reflect the change.
Let me know what you think. I'm just trying to understand your situation a little better...
Pat
NeoPa 32,579
Recognized Expert Moderator MVP
To my surprise, I found there was no option within the syntax of ALTER TABLE to specify an alternative database. This seems to imply only the database that contains the code (FE) can be changed by such code. An unfortunate limitation it seems.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: jayson_13 |
last post by:
Hi,
I need to implement a counter and i face problem of locking so hope
that u guys can help me.
I try to do test like this :
1st connection
SELECT * FROM nextkey WHERE tblname = 'PLCN' FOR Update;
(when i execute this statement and i guess that this will lock the
|
by: Caroline |
last post by:
I need to update a table by copying a column from another table
(having the same
structure, but on another database), from the record having the same
primary key.
1 - What is the correct query?
2 - I tried copying them record by record, but the datatype is ntext,
(it displays <long
|
by: Jan van Veldhuizen |
last post by:
The UPDATE table FROM syntax is not supported by Oracle.
I am looking for a syntax that is understood by both Oracle and SqlServer.
Example:
Table1:
id name city city_id
1 john newyork null
|
by: mo |
last post by:
I need to bring the ssn's into UniqueSups (supervisors) from
tblNonNormalized. My inherited DB is not normalized and I find it
extremely irritating due to the workarounds needed.
I created tblUniqueSups by doing a select Distinct Supervisor Name.
Now I need to bring in the SSNs of the Unique Sups but I can't quite
get it.
I tried:
UPDATE UniqueSups LEFT JOIN tblNonNormalized ON UniqueSups.NAME =
tblNonNormalized.SupervisorName SET...
|
by: serge |
last post by:
I have a scenario where two tables are in a One-to-Many relationship
and I need to move the data from the Many table to the One table so
that it becomes a One-to-One relationship.
I need to salvage the records from the many table and without going
into detail, one of the reasons I can't do the opposite as
there are records in the ONE table that I need to keep even if they
don't have any child records in the MANY table.
Below I created...
| |
by: Earl Teigrob |
last post by:
I am considering writing a Class that Selects, Adds, Updates and Deletes
Nodes in an XML File but do not what to reinvent the wheel. (See XML file
below)
That data format would emulate records in a Database Table with a Primary
Key for each Record (see xml sample below) and a flat file structure. I
would use a class to manipulate this type of data structure extensivly if I
had one.
Does anyone know of such an animal?
|
by: Maxi |
last post by:
I have 162 tables in my database. Names of the Tables are 1, 2, ......
so on till 162. Every table has only one field (field name = Expr2) of
type NUMBER (DOUBLE) with 352716 records in each table.
I have one more table with 163 fields. Name of the table is "Verify"
which has 701 records. First field (field name="Main" of data type
"BYTE") of Verify has serial numbers from 55 to 755. Names of the rest
of the fields are 1, 2, 3, ..... so...
|
by: Maxi |
last post by:
There is a lotto system which picks 21 numbers every day out of 80
numbers.
I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21)
Here is the structure and sample data:
"Date","P1","P2","P3","P4","P5","P6","P7","P8","P9","P10","P11","P12","P13","P14","P15","P16","P17","P18","P19","P20","P21"
1/1/2005,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
1/2/2005,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
|
by: =?Utf-8?B?QmFidU1hbg==?= |
last post by:
Hi,
I have a GridView and a SqlDataSource controls on a page. The SqlDataSource
object uses stored procedures to do the CRUD operations. The DataSource has
three columns one of which - "Modified" of type DateTime - is hidden since it
should not be edited by a user. The system handles the update for this
column. So, I have hidden (Visible=false) this column on the grid. In order
to access the value in this field, I have created a...
|
by: JKing |
last post by:
I'm using an update query to update a single field in a table. Table1 is part of my normalized table structure. Table2 is used solely as an import table for raw data which I sort and summarize through queries and VBA routines.
Table I'm updating looks like this
Table1
Code Name
1 Gym
2 Math
3 English
Table I'm using for update looks like this
|
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: 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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: 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...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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...
| |