Automatically importing from Excel

[redBo]

Hello all,

Hope this is the right section to post this question. I thank all for any assistance in advance.

• We basically have a supplier's access database that contains one table of prices.

• In addition, there also exists an excel file that contains applicable discounts against the supplier's items. There's a common key, the part number.

There's a need to unify these on an on-going basis as supplier sends updates to their database, and our finance department (who will not move away from an excel worksheet) update their records. What I'm thinking of doing is creating another database with two tables; one whose characteristics are same as the supplier's table and another with fields matching the excel file. And write a script or something that when you execute it, pulls data from (predetermined location) database and excel file to the two tables. Queries can then be constructed using the relationship between the tables.

How can this be achieved? Did a search and no sugar. Or is there a better way. The users are access novices and would like to minimise their "workload".

Cheers

 

[systemx]

Hi redBo,

Sorry if this reponse is a bit light on with specifics - I'm at work and not much time to write a reply!

Probably the best way to do this is through the use of a command button that firstly imports the data from a specified location to a temp table, then uses an append query to join the data to the main table and finally deletes the temp table.

I prefer to use a temp table just so I can ensure the data is 'clean' prior to joining it to my main table - others may have a better solution or different view.

An example of code I have used previously -

'Means the user will not be prompted
DoCmd.SetWarnings (False)

'Get the spreadsheet

DoCmd.TransferSpreadsheet acImport, 8, "tblTEMPTABLE", "FILEPATH/SPREADSHEET.XLS", True, ""

'Run my query to fix up the data
DoCmd.OpenQuery "qryFIXUPTEMPTABLE"

'Run an append query to join the data to the main table

DoCmd.OpenQuery "qryMYAPPENDQUERY"

'Delete the temporary table

DoCmd.DeleteObject acTable, "tblTEMPTABLE"

DoCmd.SetWarnings (True)

Hope this helps you get started.

Regards,

Rob

 

[Bat17]

if the XL file is in table form you should be able to link to it directly.

Peter

 

[redBo]

thanks for the responses guys. I am importing and putting in a temp table. I have two issues here.

I cannot modify the supplier's database as this is delivered as a fresh update every month. I know there will always be one table. Name of the table changes every month (ie. dataJan2007, dataFeb2007... and so on). At the moment I am using the Transfer method to import table with table name manually entered in. Is it possible to find the name of the in a database (ie in-built function oe otherwise) and import?

Secondly, when I am importing from excel, there's a column where first 100 or so entries are number, and a few here-and-there contain Letters. Somehow Access automatically assigns this field to be a Number, thus giving conversion errors for entries containing letters. how could I fix this?

Thanks

 

[GohDiamond]

1) Create a field (Field 2) in your "temporary" table in access and define the field type as text field
2) Use an update query to insert the alphanumeric data from Field 1 into Field 2.
3) Use the AlphaNumeric data (Field 2) as your related field.

You may have to do the same thing for other access tables that relate to that Field 2 to avoid type mismatch.

Personal Experience with this procedure:
I have a Student database for Federal Work Study programs. Some of our resource data lists StudentIDs as numerical and some REQUIRE that the same "numerical data" be represented as TEXT/AlphaNumeric. I find it most convenient to create two columns StudentIDN and StudentIDT, IDN for numeric only and IDT for TEXT/Alphanumeric. I can use the StudentData list to connect the dots between tables that use either one. And I use the update query to keep the AlphaNumeric field up to date with the numeric field. I have a macro that runs the whole procedure with 1 click.

As for monthly updates, I have created a linked Excel spreadsheet with a FIXED NAME and placed it in a fixed location so that the link is always availble in the database. I remove last months' data from the spreadsheet and replace it with this months data. This months data then becomes immediately available to Access. (Of course the original Excel workbook is preserved in a resources folder, if I need it later.) I use a combination of Delete query and Append Query (Also run by a macro) to replace the old data with the new data in the "temporary" table. Just be careful that you are preserving historical integrity if that is a requirement for you.

I hope this may spark some ideas to aid your process.

Goh