Exporting table. Data not transferring correctly.

[Wookiefoot]

So I've been given the honor at my work to take over a database that was built by a novice with little to no instruction. I'm attempting to salvage it before having to start over. The problem is complicated to explain but i'll try my best...

I have a linked excel table called (FIBER TRUNKS) that I'm using a make table query to create a table called (MasterTrunks) in access. I'm then exporting MasterTrunks using a macro.

Export macro:

Linked excel table:

As you can see in column A2, it starts with the number 15111, then A3 is 16111, etc... When I export MasterTrunks, the order is not the same as the linked file. Also, the column (Corning Part #) is exported correctly but the other 10 vendors such as (Hitachi Part #) are messing up.

All the columns that contain Part # are set as text data type in excel. They contain either the word FALSE or a part number


ex:

FALSE

FALSE

008Z81-31131-BL

The exported file is only displaying the Corning Part # column correctly (Falses & part #'s). All the other columns simply show a FALSE in every cell. I'm not understanding why the Corning column is exporting correctly but the other Part # columns are not showing the FALSE or part #'s, simply a FALSE in the cell. All the part # columns are set as text data type. Yet in the MasterTrunks (the table I'm creating using a query on the link table), the data type for Corning Part # stays as text but the other part # columns are changed to Yes/No type (in access).

[Micron]

Can't make out the images. This is a common issue when doing imports/exports. I believe the specifications are based on an analysis of what's in the first 8 rows of the spreadsheet, which doesn't always result in what you want. This They contain either the word FALSE or a part numberisn't clear if you're referring to the source or the result.

Why create and re-create this table? Create it once, ensure the data type is correct for all fields and append to this table. If need be, delete it all first before the append or update. This append can be done from the linked spreadsheet rather than doing an import over and over.

Some hacks involve prefacing all text data in the spreadsheet with an apostrophe (') to keep numbers as text. Maybe my suggestions are not what you need, but my understanding of the process isn't real clear. I confess, nor do I have a whole lot of experience with imports since most of my work experience was ODBC related, and I tended to use Automation when dealing with Excel. Thought I'd respond anyway since there have been a bunch of views with no replies.

[CJ_London]

the order is not the same as the linked file

That is a factor of databases, records are not stored in any particular order and in fact, if you look at the way it exports it might take some records from the top, then some form the bottom and work inwards towards the middle. Not sure why, probably something to do with efficient use of disk reads.

You need to specify an order - so export a query based on the table and sort in the order you require.

[Wookiefoot]

Thanks for the suggestion about the apostrophe. I tested adding ' to the beginning of the first ten cells in a few columns. Slowly but surely it fixed all of the issues.