Hi All,
I created a database for a friend a few months ago which has been
working fine. However, he now wants a few changes implemented to accommodate
his growing needs. When I gave him the database, I split it in anticipation
of this so that I could give him a replacement fromtend without affecting
his existing data.
My problem is that one of the changes he has asked for requires one of the
tables altering. My plan was to run code the first time he opens the new
frontend which would add the required fields but I found that this cannot be
done as the tables are linked. Can anyone offer any idea on how I could
achieve this as getting to his location is somewhat of a problem!!!!!
TIA,
Mark 4 1424
Look up in the Macro area
deleteobject - Table
Transferdatabas e - link
That can do it from within the normal running app - however that does
not of itself allow changes to the table structure etc. but will unlink
it and allow you to link a different table in its place.
It would seem you need to get the new definition into the tables DB
then get the old data transferred from the old table into the new
table, then delete the old link and the new table link and recreate the
table link with the new BE table. Very last step not necessary if the
new table was created with a different link name (unlikely since you
would have to have changed EVERY query within the system.)
At some point, after everything seems to be working for a while, you
may want to re-establish a link to the old table and empty it since you
cannot really delete it from the FE (I believe).
>From your front end create and open a new access application. Open the
backend db with this. Run any code you want. Provided you don't have
something going that prevents the backend db from being opened
exclusively something like this should work.
Sub aircode()
Dim a As Access.Applicat ion
Set a = New Access.Applicat ion
With a
.OpenCurrentDat abase "northwind. mdb"
.DBEngine(0)(0) .Execute "ALTER TABLE Employees ADD Whatever
CHAR(255)"
.CloseCurrentDa tabase
.Quit
End With
Set a = Nothing
End Sub
Mark wrote: Hi All, I created a database for a friend a few months ago which has been working fine. However, he now wants a few changes implemented to accommodate his growing needs. When I gave him the database, I split it in anticipation of this so that I could give him a replacement fromtend without affecting his existing data.
My problem is that one of the changes he has asked for requires one of the tables altering. My plan was to run code the first time he opens the new frontend which would add the required fields but I found that this cannot be done as the tables are linked. Can anyone offer any idea on how I could achieve this as getting to his location is somewhat of a problem!!!!!
Simple. Your code should reference the backend database. You can determine
the backend's name/location from the link propertis of oen of the linked
tables. You then reference that database and it's table collection.
eg.
Instead of..
Set MyDb = CurrentDB()
use..
strBackendDBNam e = GetDataLocation ()
Set MyDb = OpenDatabase(st rBackendDBName)
Function GetDataLocation () As String
On Error Resume Next
Dim myDB As Database, tdef As TableDef, ConnectString As String
Set myDB = CurrentDb
Set tdef = myDB.TableDefs( "tblPeople" )
ConnectString = tdef.Connect
GetDataLocation = right(ConnectSt ring, Len(ConnectStri ng) - 10)
End Function
--
regards,
Br@dley
Thankyou all for your suggestions. Easy when you know how ;o)
"Br@dley" <do***********@ google.com> wrote in message
news:e1******** **@news-02.connect.com. au... Mark wrote: Hi All, I created a database for a friend a few months ago which has been working fine. However, he now wants a few changes implemented to accommodate his growing needs. When I gave him the database, I split it in anticipation of this so that I could give him a replacement fromtend without affecting his existing data.
My problem is that one of the changes he has asked for requires one of the tables altering. My plan was to run code the first time he opens the new frontend which would add the required fields but I found that this cannot be done as the tables are linked. Can anyone offer any idea on how I could achieve this as getting to his location is somewhat of a problem!!!!!
Simple. Your code should reference the backend database. You can determine the backend's name/location from the link propertis of oen of the linked tables. You then reference that database and it's table collection.
eg.
Instead of..
Set MyDb = CurrentDB()
use..
strBackendDBNam e = GetDataLocation () Set MyDb = OpenDatabase(st rBackendDBName)
Function GetDataLocation () As String On Error Resume Next Dim myDB As Database, tdef As TableDef, ConnectString As String Set myDB = CurrentDb Set tdef = myDB.TableDefs( "tblPeople" ) ConnectString = tdef.Connect GetDataLocation = right(ConnectSt ring, Len(ConnectStri ng) - 10) End Function -- regards,
Br@dley This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Laura |
last post by:
Here's the situation:
I'm trying to use an update query to copy data from one row to another.
Here is the situation:
I have 5 companies that are linked to each other. I need to show all 5
companies on a form. 3 of the companies have common employees. I have a
table that looks like this:
|
by: Lewis Veale |
last post by:
I have an Access 2000 front-end pointing at a SQL Server backend, with
around 80 linked tables and views. I frequently need to point the
front-end at different versions of the back-end, and achieve this by
relinking the tables and views to use a different DSN.
I use the Linked Table Manager > Select All > and tick the 'always
prompt for a new location' box, then click OK. It correctly pops up
the 'select data source' box, from which I...
|
by: Gary B via AccessMonster.com |
last post by:
Hi all,
Is there any way of checking if a file exists on a website?
I have an autoupdate routine that runs from a clients server, but it would
be useful if it could run over the internet. I'm sure I saw an article/post
on it a while back, but I can't find it anywhere.
Thanks
|
by: abefuzzleduser2 |
last post by:
we use Access 2000 with linked tables in SQL Server 2000. I have a
form and a
datasheet subform based on a table. This was not related to the parent
table before and I was changing nvarchars to varchars and removed
nulls. I noticed some Foreign keys were NULL? I copied data to new
table and deleted the null FK. I then changed POID to not null. POID
is PK in tblPO and FK in items. I have verified the SUBFORM is using
POID for parent...
|
by: Jim Devenish |
last post by:
I have a split front end/back end system. However I create a number of
local tables to carry out certain operations. There is a tendency for
the front end to bloat so I have set 'compact on close'
I think that I have read in some threads (althoug I cannot find them
now) that others place such tables in a local, linked database. I
could do this but I am interested to know what would be the advantages.
And disadvantages, if any.
Any...
| |
by: aaron.m.johnson |
last post by:
I have an application which contains an Access database with linked
tables that point to another database within the application. The
problem I have is that when the user installs the application, I need
to update the table links so that the paths are correct for the install
directory. Is there an easy way to accomplish this?
I'd really like to avoid duplicating the data in the linked tables, but
if updating the links is too much work,...
|
by: rdemyan via AccessMonster.com |
last post by:
My application is calculation intensive and the servers are agonizingly slow.
Administrators of my application only update the backends once a month (twice
a month max). So, my launching program allows the back-end file to be
downloaded to the user's PC. This will provide maximum speed for these
calculations/manipulations of data. Without this, just logging into the main
app connected to the server back-end file can take five minutes...
|
by: rlntemp-gng |
last post by:
I have two .mdbs,
MyTestApp.mdb- front end for forms and
MyTestData.mdb-backend for tables.
While the front end is copied to the user's desktop via a batch file,
the front end is linked to the backend currently. I did that
manually. The catch here is that the backend .mdb is used by more
than one application.
I am looking for a way via VBA in my front end app at startup (and the
|
by: afromanam |
last post by:
Whew..
OK, running access 2003 in win xpsp2 and access 2007 in win vista.
This is the question...
We have a db, split in front end (FE.mdb) and backend (BE.mdb)
FE has tons of linked tables from BE.
|
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,...
|
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: 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: 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: 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: 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...
| |