Hi everyone,
I am trying to change the field names for a table that is being
exported via Excel. Its a spreadsheet that our National Office sends
us but even after promise after promise they keep changing the field
names which makes it nearly impossible to run any pre-generated
queries etc.
The solution really is to get someone to check the field names before
importing the spreadsheet but I want to do it in code. I have written
a utility to display the table fields on a form and using a series of
combo boxes allows the users to select the correct names for the
fields. On a button on the form I detect the fields that are going to
change and send the values to two global variables (oldList and
newList) which are two lists telling what the old field name is and
one telling what to change it to. I then close the form (because I
don't want any objects referencing the originating table to be open)
and open a new dummy form with the following code on the onload event
handler:
Set td = db.TableDefs("g eocodeInput")
For i = 0 To UBound(oldListA rr)
td.Fields(oldLi stArr(i)).NAME = newListArr(i)
Next i
Set td = Nothing
db.close
I get an error that says the table is already being used by another
user or process but I have checked to make sure all database objects
that use the table are closed.
Any ideas? 3 3020
On 16 Feb 2004 18:51:26 -0800, Michael wrote: Hi everyone,
I am trying to change the field names for a table that is being exported via Excel. Its a spreadsheet that our National Office sends us but even after promise after promise they keep changing the field names which makes it nearly impossible to run any pre-generated queries etc.
The solution really is to get someone to check the field names before importing the spreadsheet but I want to do it in code. I have written a utility to display the table fields on a form and using a series of combo boxes allows the users to select the correct names for the fields. On a button on the form I detect the fields that are going to change and send the values to two global variables (oldList and newList) which are two lists telling what the old field name is and one telling what to change it to. I then close the form (because I don't want any objects referencing the originating table to be open) and open a new dummy form with the following code on the onload event handler:
Set td = db.TableDefs("g eocodeInput") For i = 0 To UBound(oldListA rr) td.Fields(oldLi stArr(i)).NAME = newListArr(i) Next i Set td = Nothing db.close
I get an error that says the table is already being used by another user or process but I have checked to make sure all database objects that use the table are closed.
Any ideas?
Are any relationships defined using this field?
--
Mike Storr www.veraccess.com
Thanks for your quick reply.
The table isn't involved in any relationships.
The problem seems to be stemming from the fact that I execute the code
from a form that has the source table as a recordsource even though I
have closed the form before running it. I solved the problem by passing
the user to a new form with a "click this button to complete" message on
it , where the code executes no problem.
This is really clunky though but seemingly necessary.
:)
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
If the execution of the new code comes from a form, then the form can't be
closed before that new code runs (unless the new code closes the form). For
instance if a button click event you do this
Docmd.Close .....
Set td = .....
Then the code must finish running before the form can actually destroy
itself. Even if you branch out to external sub or function from the button
event, execution must return to the form to be completed. You might try in
this external function, making the form invisible, changing it's
recordsource to something else (like "" to make it unbound), then perform
your actions on the TableDef. After changing the recordsource, the form
could be closed at any point.
Mike Storr www.veraccess.com
"Michael West" <we****@hotmail .com> wrote in message
news:40******** *************@n ews.frii.net... Thanks for your quick reply. The table isn't involved in any relationships.
The problem seems to be stemming from the fact that I execute the code from a form that has the source table as a recordsource even though I have closed the form before running it. I solved the problem by passing the user to a new form with a "click this button to complete" message on it , where the code executes no problem.
This is really clunky though but seemingly necessary. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: oom |
last post by:
I am a bit of a newbie when it comes to python, when working with
lists today I noticed some very odd behaviour, any suggestions
welcome:
Python 2.2.3 (#1, Nov 6 2003, 14:12:38)
on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> firstlist=
>>> secondlist=firstlist
>>> print (firstlist,secondlist)
|
by: Earl Anderson |
last post by:
I have imported an Excel worksheet into A97/WinXPH which had the new
employees names in one field ( in a Last Name,First Name
configuration). I wanted to split that one field ( ) into two
fields ( & ). The table I imported, ,
contains 61 records with a PK of . Using the Query Grid, I
constructed the following query (SQL View here) to split into the
two fields I wanted and place the results into a new table called :
SELECT...
|
by: Don Leverton |
last post by:
Hi Folks,
I have been given a CD with approx 130 .xls files (bean-counters!) that I
would like to import and merge to ONE table (tblTradeshow).
The XL files are *similarly*, but not identically structured, and the first
row does NOT contain field names.
Some (actually most) of the column names *are* the same in all of the
spreadsheets.
|
by: Marizel |
last post by:
I'm not sure there's an easy solution to this, but thought I'd ask. I
often find myself with a query which I'd like to reuse, but with a
different datasource. These datasources generally have identical
field names. The queries select a subset of the fields, so "Select *"
is not really an option.
Is there an easy way to change the source of a query, either in the
design grid or SQL display? I suppose I could copy the SQL into
WordPad...
|
by: Ralph |
last post by:
I'm trying to import a range of cells from an Excel spreadsheet into a
table in access. The first row contains column labels, but I cannot
use those as my field names, both because of their format (number,
space, text) and because I've got a whole bunch of similar tables and
I need to have consistant field names in these tables for some data
manipulation I'm doing in VBA.
I've used the following statement to bring the data in with the...
| |
by: David Gartrell |
last post by:
Hi i'm trying to import an Excel Spreadsheet into Access2000 but the data
types for two of the fields in my imported table are being identified
incorrectly. Is there a way of using some VB code in Access to change the
data types for the fields from text to a number field or an Integer field.
I'd be grateful for any advice you could give
Many thanks
David
|
by: Cecilia Bergengruen |
last post by:
2 questions:
1)I inserted a menu in an mdi window form. I right clicked on a menu
item and pressed edit names. I therefore edited all my menu item names.
Then I right clicked and pressed edit names again to return to the
normal view of the menu.
When I returned later on to muy menu names, they hadn´t been saved. How
do I use this utility (edit names), since the changes I edit don´t
prevail?
2) Is there a way to change the font and...
|
by: Brett Romero |
last post by:
I'd like to copy a object1 into object2 so object2 can be manipulated.
Object1 is coming form the middle layer into the UI layer. I'd like
to rename a field in Object2 from "somethingID" to just "ID" and do
this several times for a few middle layer objects. This will allow me
to create generic<> lists and reference the field ID against many
object2s that are similar to object1. The middle layer objects have ID
fields with different...
|
by: Arnold |
last post by:
Greetings Gurus,
In a mainform's header, I have a combobox named comboStudents. The
rowsource for this combobox is:
SELECT -999 As StudentID, "<Add New Student>" As FullName, "aaa" As
LastName, "x" As FirstName From qryStudents UNION SELECT StudentID,
FullName, LastName, FirstName FROM qryStudents
ORDER BY LastName;
|
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: 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: 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
| |