Macro to Alter Table Field Names

[ricky]

Hi

I import data tables, that come in through another source (mainframes &
Spreadsheets), every week. However the fields names of the tables are all
coded and I was wondering if it possible to execute some sort of macro of
query script to change the field names of the table?

Kind regards

Ricky

 

[Nikos Yannacopoulos]

Ricky,

It's easy to do with some VBA code. For instance, to change the name of
the first field in table MyTable to Field1 (regardless what the current
field name is, you need this one line of code:

CurrentDb.TableDefs("MyTable").Fields(0).Name = "Field1"

(note that the field index is zero-based, so first field -> 0)

HTH,
Nikos

 

[ricky]

Do I have to qualify this code with any references?

Can't seem to get the code to work?

kind regards

Ricky

 

[Douglas J. Steele]

If you're using Access 2000 or 2002, it's possible you might have to add a
reference to DAO, as those two versions of Access don't have one by default.
With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it.

If that still doesn't work, post back the actual code with which you're
having problems.

 

[ricky]

Hi Douglas

Thanks for the tip. Will check my references.

Kind regards

Ricky

 

[Klatuu]

There is a way you can do this with no coding.
Create the tables with the field names you want to use.
Import the external files into temporary tables or to link to the external
files as if they were a table.
Create Append queries that will append from the linked or temporay tables
that copies the data from the external field names to the field names you
want to use.

 

[Nikos Yannacopoulos]

Klatuu,

Very good point, provided the external files have always the same field
names - which they probably do, but isn't very clear in the OP, so my
proposed solution was intended to also handle ever-changing original
field names.

Just to build on your suggestion, I would favor the linking option, in
avoiding bloat.

Regards,
Nikos

 

[Klatuu]

Yep, that could happen. In my experience, you can never depend on what is in
a spreadsheet you want to get data from. The great thing about Excel is a
user can do whatever they want. The worst thing about Exel is a user can do
whatever they want.

What I have been doing lately is to use Automation to edit the spreadsheet
for format before importing it. I check to see there are the right number of
columns and the column names are correct and fix them if they are not.

Not only has this reduced the headaches, but it has trained the users. They
know it has to be correct or it will get bounced back to them.

 

[Nikos Yannacopoulos]

Yep, that could happen. In my experience, you can never depend on what is in
a spreadsheet you want to get data from. The great thing about Excel is a
user can do whatever they want. The worst thing about Exel is a user can do
whatever they want.

So very true... adding on to it: it's even worse that a user can do what
they do *not* actually want, and because most users are not very
knoledgeable about Excel, they will (Murphy's law # 12654?).

Coincidentally, I had a similar discussion with my wife just last
night... she works for a third party logistics provider, and a big
customer of theirs (big multinational, big bucks, everybody knows them
around the globe!) sends them delivery data for shipments in Excel
sheets, with manual intervention from their staff... pasting from
several other Excel sheets, and the like. What is this world coming to?

What I have been doing lately is to use Automation to edit the spreadsheet
for format before importing it. I check to see there are the right number of
columns and the column names are correct and fix them if they are not.

Very wise.

...it has trained the users.

Hey, you're up for a Nobel prize!

 

[Klatuu]

Dave's Law: Murphy is an optimist

Very wise.

Not wise, just self defense. I got tired of "What is wrong with your
database? It keeps getting errors!"

My wife is the world's worst user. Only 3 things can happen:
1. Windows isn't working correctly.
2. Excel did something strange.
3. "What is wrong with your database? It keeps getting errors!"

And she works in IT.

 

[ricky]

LOL!!!

Very funny chaps.

Incidentally, I was wondering the remark about automation, I think I could
use something like that....could one of you please elaborate on this?

Kind regards

Ricky

 

[Nikos Yannacopoulos]

Ricky,

By "automation" I meant code (possibly involving queries) which imports
directly from (specific format) flat files (usually tab, comma or
semi-colon separated), may also involve some integrity checks in the
process (for which sometimes you may need to store the imported data in
a temporary table, to run the checks before the final appending to the
main tables)... likewise, code that will export data in specific file
formats as required by another app to import, usually flat files (as
above) or Excel files; between the two, I favor (delimited) text files,
and prefer to use a meaningless extension, or no extension, when I can,
so as to discourage the user to open them (which they tend to do with
Excel files, and then the problems start).

I could probably offer some more specific advice (and, certainly, so
would others, including Dave), if you cared to describe your situation.

HTH,
Nikos

 

[ricky]

Hello Nikos

Thank you for your reply, and kind offer. I would feel very cheeky in
asking you to do my work , however let me formulate some points and
perhaps you could outline some guidelines for, that would be great.

Kind regards

Ricky

 

[Nikos Yannacopoulos]

My pleasure to help if I can!

Hello Nikos

Thank you for your reply, and kind offer. I would feel very cheeky in
asking you to do my work , however let me formulate some points and
perhaps you could outline some guidelines for, that would be great.

Kind regards

Ricky

 

[Klatuu]

When Microsoft talkes about Automation, it is sort of the new word for COM,
but not exactly. It involved manipulating the other application using VBA in
Access. Sometimes, queries and/or tables can be involved.

You use methods like the GetObject and CreateObject to create an instance of
the other application. Once you have established the Application Object, you
use that app's Object Model to control it. For example, with Excel, you
first establish the application object. Then you either create a workbook
object or open an existing .xls file as a workbook object. The you use the
worksheet object to reference and manipulate data, formulas, and formatting
of range objects, cell objects, and other objects and properties of the Excel
Object model.

This includes everything you can do in Excel as if you had it open and were
creating it by hand. You can create charts and graphs, set print areas, set
margins, you name it.

It is a curve getting your head around it, but once you do, it is powerful
stuff. To get started, I picked up a book on Excel VBA, studied the Excel
Object Model using the VBA Object Browser, and by using a trick I read in
this group. That is, if you want to use VBA from Access to do something in
Excel that is new to you, open Excel, start recording a Macro, and when it is
done, open the Macro for editing (now you are in VBA), copy the code, and
paste it into your Access app. There will be some syntactical differences
you will have to adjust, but it is a gread starting point. Just for
starters, here is a sample from one of my apps that establishes the objects.

One word of caution. You have to be very precise in your object referencing
or you can confuse Access and you have to be sure that even if your app
errors out, you destroy the Application object. If you don't, it can leave
an instance of Excel running. You will not see it in Applications tab, but
in the Processes tab. The symptom is that if you try to open Excel and use
it, it will hang up.

Good Luck,

'Open Excel
On Error Resume Next ' Defer error trapping.
Me.txtStatus = "Opening Spreadsheet"
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
'Open the Workbook
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
Set xlSheet = xlBook.Worksheets("Actuals_res_export")
intLastRow = xlSheet.UsedRange.Rows.Count
If intLastRow = 0 Then
MsgBox "No Data to Import" & vbNewLine & "Spreadsheet may be open by
another user", _
vbExclamation + vbOKOnly, "Import Adjusted Actuals"
GoTo LoadAdjustedActuals_Exit
End If