Delete a column in a macro (Ofc 97/sp2)

[cjon_hinkle]

I am trying to write a macro that will import information from a dBase III database into an access table and then remove a single column so that the data from that table can be re-formatted by doing an append query into an empty table. I don't seem to be able to select the column and delete it. Any suggestions?

Note: An append query that works by selecting all the columns except the one in question is messy, since there are about 40 columns in the dataset. (unless, of course there is a short way to select 39 of 40 columns for the append query)

TIA, CJon

[charlotte]

The short way is to highlight all the fields in the first table that you want to use and then drag them down onto the grid. On the other hand, you can simply delete the column from the table if you with--you don't need a macro for it.

Or is this something you're trying to automate? In that case, don't use a macro, use code. The macro simply isn't flexible enough to handle automating something like this.

[cjon_hinkle]

Charlotte wrote:

<hr>Or is this something you're trying to automate? In that case, don't use a macro, use code.<hr>

Yes, when I did the test, I deleted the column manually and went from there. However, that is not a solution.

I am trying to automate this function because it is part of a larger scheme that will be distributed to a group of users that are even less adept than am I. I started with the macro in the hopes I could simply record it, as I would in Word. No Joy.
I might be able to code it if I had any references, but this is a network install at work and we were provided exactly zip in terms of either printed or help desk support.
Thanks for your help.
CJon

[charlotte]

<hr>I might be able to code it if I had any references<hr>

Do you have the help files?

[DonnaT]

* (unless, of course there is a short way to select 39 of 40 columns for the append query) *

Apologies for my stepping in as I am inexperienced when it comes to code, but I'm trying to learn by peeking into these forums to see what you guys know.

I am confused as to why you could you not just create a "stored" query (don't know the correct terminology - one that is created using the GUI drag and drop instead of coded SQL) that appends only the columns you need and then include a DoCmd.OpenQuery "Query", acViewNormal in your code. That would circumvent the need to delete a column???

Again, apologies if this is a bit daft.

[cjon_hinkle]

Indeed, I was planning to create a macro that contained the query, so in that sense it would be "stored". I would be more than happy to code the entire process, if I knew how to write the code and had both the time and references. I envisioned this as a half day project (as it would have been in Excel) where I would work though the steps until I had them down and then I would record the macro and be done. Not to be. This project is very tangential to my real job.

It is not that I am completely incapable of writing code, just ignorant of Access. I could do this in dBase 3 in an hour, but that's no help.

To answer Charlotte's question, Yes, I have the help files, but without some clue about how to ask for help, I'm reduced to reading page after page that doesn't "help".

[charlotte]

The easiest method would be to create a query that contains only the columns you need and to save that query. That's what was meant by "stored". Then use that query instead of a table to append the records wherever you need them. If this is going to be a repetitive process, you'll want to make sure that the imported table is given the same name every time. That way, you can build a query based on that table name and it will work as long as you use the same table name. Then you can call the action query (either to do a maketable or to append records) from a macro or from code.