AbsolutePosition with Update Query 1

[dschomburg]

What I would like to do in either via vba or in an update query is to update [Field1] in a table called tmp_PCIM7METUB with it's AbsolutePosition in the table. I have no idea how to incorporate this into vba. Any suggestions on the code is greatly apprciated.

Thanks in advance,
Dave

 

[alr0]

Hi Dave,

This can be a little unnerving so strap yourself in for this.

You should be thinking of your data being displayable in any form that suits the current purpose.

Alphabetical by last name for some uses, perhaps chronological by last sale date for others. You can use primary keys and indexes to locate and group records. In essence, your records will have any location that you give them.

What is it exactly that you need to accomplish here. There is almost always a way get what is required but tracking the absolute position of a record is probably not it.

alr

 

[dschomburg]

alr0,

Thanks for the reply. The reasoning behind this is due to the data being imported. I am importing a text file coming from a formated canned report of a mainframe. So it doesn't play nicely with Access. The report has a reoccuring instance of values in the only constant field from month to month. It's a list of car manufactuers and a number of cars and at the bottom the same car manufactuers are listed again with the value of those cars. Whoever designed the report wasn't planning ahead! So in order to build a nice and tidey table I have to give each row a unique ID - and since I can not save the AUTONUMBER option as part of an import spec. I need to use absoluteposition (which will never change due to it being a canned report) - Does this clearify my issue at all?
If you have any suggestions I could certainly use them. I almost have the code but not quite there yet.

Thanks again,
Dave

 

[alr0]

Hi Dave,

It sounds like a special situation, I too import a lot of text files from mainframes and see some very strange things.

I am not certain that I understand the nature of the data. Is it the same each month except for the number and value of cars?

If so, then a storage method that includes in each record a field for the month it came in would allow you to put all the data in one table and extract data for monthly, quarterly, annual, etc. reports.

Are there two different types of records for volume and value?

I do not have a good picture yet of the data or the report you are trying to produce. Perhaps two or three sample records (of each type) with a description of the report you need would help.

alr

 

[dschomburg]

Yes the only thing that changes are the values. Sort of like this:

MAN. NumOfCars Value
FORD 1000 $100,000
SATURN 500 $75,000


MAN. NumOfCars Value
FORD 32 $50,000
SATURN 8 $80,000

So that there is a top part of the report and a bottom. They have different meanings but the value in the field "MAN." is repeated twice in the report. There will always be the same number of records each month. Just the value will change. It would be so easy if Access would just save the "Let Access choose a Primary Key" as part of the import specs.

 

[alr0]

Dave,

Though I am still not clear on why you need a primary key for the reporting, I have never seen access fail to add a primary key on import when requested. I would try another machine and see if there is a problem with your set-up.

What is the difference between the top and bottom parts of the report?

Is the report you refer to what you are importing or trying to output?

Are you trying to duplicate what you are importing?

Why are there duplicates and can they be distinguished in some way?

Can all data go in one table or must there be a table for each import of data?

What do you want your report to look like?

Sorry to have so many questions but this is important to finding a workable solution.

alr

 

[dschomburg]

I thank you for your assistance. The option of choosing a primary key is not available with the TransferText method in conjunction with saved import specifications.

I do have a work around for this automated process now. I simply export the table to a spreadsheet and use Access to initiate a macro in Excel that just numbers the rows. Then I import the spreadsheet (again) and there ya go - fake auto numbers.

Thanks for all your help,
Dave

 

[alr0]

Hi Dave,

In the last few days, I started using the FileSystemObjects to import text files. It is fairly easy to use and gives you complete control over the imports in code. This is necessary as we are importing 50-200 files per day that must be logged. If you have further problems, this might be an ideal solution. Let me know if things do not go smoothly.

Best of luck,

alr

 

[JonFer]

Can't you import to a table with an Autonumber field already there? If you create a "template" table with all of your fields but no records and copy it each time you import, the Autonumber will always start at 1 for the new table.

 

[alr0]

Good point, I think that would work.