Wrong format linking excel to Access

[mart10]

I am using Office 2003

I am linking an Excel tab to an access database. I have 2 fields ccy and account number formatted as General (but I have tried formatting as Text as well). When i link into access the format shows as Number which is wrong and will not work. What on earth am i doing erong? weird!

 

[kjv1611]

Sometimes going between Excel and Access can get rather hairy, quite honestly.

There are a couple of ways around it, though:
1. Create the table in advance, before the import into Access, and set the field formats. THEN import into that table. See if that works - this is the easiest for a one time quick fix.

If it's a table that you need to clear out each time, and repopulate, just create a delete query that deletes all records in the table. Then before you import the next time, run the delete query.

2. Export the Excel file to text, then import that into Access. On the first run, save your settings as an Import Specification, and then from then on, refer to that Spec when importing the same file (from text).

3. Use VBA: In Access VBA, create a database object and recordset object for your table (you will have to either create the table beforehand, or else include that in your code), and create Excel Application, Workbook, and Worksheet objects for the worksheet. Then loop through the worksheet, adding the necessary fields as records to your Access database. This will give you absolute full control over the data. For instance, if it still is acting up for some reason, you could adjust your code to force the values to go to the correct format. Not as easy setting up, but it might be the best long-term fix.

I've had one process in the past where I setup this sort of method:
The code uses docmd.transferspreadsheet to import the data from Excel into a pre-existing table. During the import, some items in some fields just absolutely refuse to go in correctly. So, I then have database, recordset and Excel objects go out and grab the correct data, and put it into the correct fields - when doing this piece, it works 100%. The reason I use both processes is that using just the 2nd would still be more resource intensive, and take more time. But if your files happen to be small, then I suppose that wouldn't really matter.

 

[mart10]

Great thanks for the advice, pass it on to Mr Gates!