Import Excel Spreadsheet Into Access 2000 1

[smithph]

Access 2000 will be the death of me! I am changing an Access 2 database into an Access 2000 one and having great problems importing an Excel spreadsheet into a table in the database. The spreadsheet is eactly the same as the one used in the Access 2 version and in Access 2 it works like a dream. Access 2000 keeps saying that there is no F1 field in the table I'm importing to. I know there isn't. Neither is there an F1 field in the spreadsheet. The spreadsheet has no field names but the destination table has. The spreadsheet is fixed width but even if I import it as a comma deliminated text file it still fouls up. And Gatesey calls this progress!! What I would give to have him in a room for 20 minutes to explain what the average guy finds so dificult with his firms stuff. Anyway, can anyone help, please?

 

[KenReay]

Hi

Ywo questions for you:

Which command are you using to import the spreadsheet ?

Are you using a Import\Export Specification file ? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[smithph]

Hi Ken, Thanks for the reply. I am using a Macro that has a transfer spreadsheet amongst its routines. The Excel spreadsheet is correctly identified as an Excel 8-9 version and the 'has headings' box says No.
I can't find anywhere to set up an import/export specification file - doesn't seem to ask for one or give the option to enter one.

 

[leighchel]

Have tried doing a paste append? This way you shouldn't have anymore troubles with the headers and it thinking there is an extra field.

Michelle

 

[Xunil]

Depending on how your D-base is set up, you could add a transfer table into the D-base itself or set up an append query to pull the information from Excel using either the required worksheet or preferably named ranges. I've found it easier at times to get Access to talk to Excel using named ranges than some other methods.

You could also link the Excel sheet/named range to Access as a link table then run your query based on the link. This is normally bullet proof since the link ensures communications are at least open.

An Access transfer table is still handy though; it gives you an area to temporarily store your data and any amount of queries can be run on it to append, update, sort and so on.

Regarding the lack of the F1 field error message, this happens when you try and import from Excel into an existing table with named fields. The import wizard works beautifully just as long as you let it create a new table. The moment you try and pull info into an existing table it starts to come unglued - another point in favour of setting up a dedicated transfer table in Access.

If you give a little more detail a workable solution may be easier to find.

Regards

 

[smithph]

Hi, Xunil, Thanks for the info. I think I have managed to get the import to work now but I don't really know how. I changed the spreadsheet to have headings that matched the table and made sure the other bits of the Macro were correct. Your point about the import table is interesting but how do you know what its name is going to be? Without its name how do you get queries to run on it? One other thing. As well as importing the spreadsheet info I need to output the info after some bits are added etc., into a text file with a certain spec. The spec must exist in the Access2 database but doesn't exist in the Access 2000 database despite my having imported all the Access2 tables, queries etc., into the blank Access 2000 database. Do you happen to know where the spec exists? And where and how I should re-create it?

 

[Xunil]

The transfer table is a temporary dumping ground where data can be fooled with to either move from A to B or manipulate it betwen those points.

If you run the Import wizard (Get External Data) in Access and pull the information out of your spreadsheet, tell the Wizard to import to a new table; this works around the necessity of presetting your table with title fields and so on.

Once it has been imported (or you can also do this with a simple linked table - but be warned that a link table between Access and Excel will not have an autonumber field if you need one) you can do the following:

Query the information to sort or arrange to taste (the query can append to or update an existing destination table(s) in your D-base)

Run calculations on your data prior to targeting your output to destination table(s)

etc,.

You can set up an SQL query that will create a temporary transfer table, sort the information you are pulling out of Excel according to your needs and place the outputs in the destination of your choice. Since this table will either be created and then deleted on the fly when the query is called, or permanently set up but with no held information if it will be used regularly, it takes up next to no space in the D-base so you don't suffer bloat or a performance hit.

As you may have guessed, I like transfer tables. They are a perfect temporary processing area where you may choose to do some tricks or simply move information from one point to another without manipulating it mid-way.

Finally, regarding your text-file spec problem: I haven't the faintest clue. Without knowing any relevant details of the text-file output or its intended/actual/historical use, I can't even begin to guess. Is it comma or tab delimited (or even delimited at all), is it ordered or arranged information or memo notes, it is...

Get the picture ?

Sorry I can't wave a wand for you, but I'm flying blind here. I think you need to either offer more detail or show the relevant D-base/files to someone first hand.

Good luck in reaching a solution.

Regards