Access 2010 shifting columns

General Questions
#1

I’m trying to stick to a single issue regarding Access 2010, so I’m starting a new thread for this one.

  1. Open Access 2010.
  2. Click External Data tab.
  3. Click on Text file.
  4. Browse for source of data.
  5. Select .csv file.
  6. Click OK on previous window.
  7. Ensure Delimited is selected. Click Advanced.
  8. Define each field as Text and click Finish.
  9. Do I want to save import steps? No. I don’t trust Access 2010. Click Close.
  10. In the Tables window on the left, double-click on the file to open it. Read the columns.
    Field1, Field2… Field7, Field10, Field8, Field9… Field18.

When I define fields as text, in step 8, above, I see that the fields are in order. When I open the table, I see that Field10 is out of order. This happens with some (but not all) other imports as well.

Why is Access 2010 moving stuff around, and how do I make it stop?

#2

I’ve heard of this bug, and the general recommended solution is to not have Access assign the primary key on import. This seems to be what mixes up the column order. You can create a PK after the import is complete.

#3

FWIW, I found a site that tells you how to move columns around. (Access does not have an ‘Insert’ feature.) I followed the instruction to shift Field10 to where it should be and saved it. But when I tried to export, Field10 was still misplaced in spite of how it looked in the table. The only solution, until someone can tell me how to make Access 2010 stop moving stuff around to begin with, is to delete the database and start again from a blank one.

#4

I only use Access to import .csv files and export them as fixed-position text. I don’t know anything about primary keys or how to turn them off, or if I need them at all.

#5

It’s a step in the import wizard - after defining the columns, the next screen gives you a choice of automatically adding a primary key, defining your own, or not using one. The default is to let Access do it, but you should change this to “No primary key.”

If all you’re doing is importing and exporting, then you don’t need one. A primary key is needed for real database actions like querying, joining to other tables, etc.

#6

It took me a while to find it, since I click Finish after defining the import fields as Text instead of clicking through all of the heretofore unnecessary screens. So I chose No Primary Key. When I imported, it said I had import errors. I usually get this if I forget to define something as text. Since I did define all fields as text (really, that should be the default!), I don’t know what the errors are. It only shows where they are.

And yeah, the only reason I use Access at all is because I have to have fixed-position text files and I know of no other way of converting Excel files to that format.

#7

For the errors, it will typically create a second table in Access with a name like MyTableName_ImportErrors, which will contain a list of the errors it encountered.

For fixed-width text files from Excel, if you have short rows (I think the limit is 240 characters per row), you can Save as Formatted Text (space delimited) (*.prn). For anything longer, your method of importing/exporting from Access is easiest.

If you have rudimentary VBA skills, it wouldn’t be too tough to write a macro to do this. Here’s an example I found with a quick search, but I haven’t looked at the code so I can’t comment on how well it works.

#8

Yes, there’s a table but it doesn’t have any useful (to me) information. Just field and position or something like that. Not actually what it’s objecting to.

Silly me! Trying to save a text file as .txt! :smack: Now that you mention it, I did try .prn years ago. Two problems: There’s no option for defining lengths. (For example, the first field is 12 characters, so I have my Excel column at 12 characters and my font at 9 pt. Courier New so that I can see at a glance whether the data fits into a specified length. Yes, I know column widths in Excel are meaningless. I set them for my own convenience.) The other problem is that exporting to .prn truncates the data. The file I have right now has 224 byte records. Since exporting to .prn stretches the records, the records are truncated.

Zero VBA skills. I could program in BASIC in the early-'80s, I’ve forgotten FORTRAN, COBOL, and SAS, and I can program in Easytrieve now.

#9

I used to use Access 2010 quite a bit, not so much anymore. It is, well, different.

What it isn’t is a spreadsheet program or a text editing program. It tries to be a database program, and does a pretty good job of that, but not a great one. Now, I am not a programmer or an IT guy. I’m an engineer and the only computer courses I had in college was Basic and Fortran (actually WATFIV, if that means anything to anyone, anymore) and had less than a year of that, combined.

I am not sure what you are trying to do, but I don’t think Access is the right tool. It might be, but if you are going to use it, you really need to learn it.

When I first started using Access, I was working on a migration from Dbase (I think? It was in the last millennium) as the company had decided to go with Microsoft for all it’s software needs. Anyway, I wasn’t very familiar with the Dbase program, so I had that working for me. An intern and I (the intern did most of the work) got the system up and running, then the intern was gone and I had to keep it going. Day-to-day operation was performed by a clerk, but I had to trouble-shoot and keep it running.

I was familiar with Excel and Word, and somewhat knowledgeable on their macros, so I thought it wouldn’t be that much trouble. It wasn’t until I had a technician set-up a separate computer for some testing equipment that used Access to store the data (completely, well, mostly, unrelated to the other application) that I gained a better understanding. He said something that really sank in. We were doing something with a table and he said “This isn’t a spreadsheet, you are actually in the database. The table is just how it is displaying the information.”

Again, I have no idea what you are trying to do, or how you are going about it, but if you are trying to make (format) the table to present the data in a particular way and then export it to preserve how you have formatted it, you are doing it wrong. If you want to export formatted data, say in a text file, than I think you have to create a “query” to pull the data you want, in the order you want, from a “table” and make a “report” formatting the data the way you want and write the report to a file. Again, this is assuming that is what you want to do. It is a lot more complicated than it needs to be because it doesn’t sound as if you really need a database program to do what you are trying to do.

#10

I get what Johnny L.A. is trying to do, and while using Access to create the fixed-width file seems like a bit of a hack, it is the correct hack and is generally the recommended approach. Although he doesn’t need the database capabilities of Access (which as you note, is the main purpose of Access), he does need export capabilities that aren’t built in to any other Office program. It makes sense to leverage those capabilities in Access.

The first column will have the error, like “Type conversion failure” if it tried to insert a string in an integer field. You can ping me if there are errors that don’t make sense and maybe I can help decipher them.

Yeah, it’s really limited and I didn’t have much hope it would work.

You could probably figure it out in an hour, starting with code I linked to. It’s essentially the BASIC you knew way back when. Figuring out how to add the code to a file and navigate the IDE will be your learning curve, if you want to go that route (and I totally understand if that’s not something you want to pursue; at least at the start, you’ll just be trading one headache for a different one).