Unanswered: Is there a way to link excel to access
Hi everyone, I will like to ask if there is a way to link excel to access? I got a database already and also an excel worksheet. The problem is the fields in excel worksheet and the database are labelled in a different format as in field1 of the excel worksheet is the same as field5 of the access worksheet. This problem arise because the database and excel worksheet are done by different people and now I have to try to link them. Is there a way? Thanks
I know if the fields are the same then I am able to do so. But what about those that are not the same as in this case? Can someone please help me? Thanks in advance.
Is the data in the two programs that different? Both Excel and Access can handle the same type of formats, just a matter of what type data you actually have in each field you're trying to bring over. Are you like trying to bring over a Text field in Excel and import it into a Number field or something like that? Depending on your data, in Excel just convert the data type if compatible to the data in it. Or, once you import it into Access change the data type in your table. If you're trying to import into an existing table with data in it, myself I would import it into a new blank table, convert the data types and then Append that data into your existing table. But I sometimes do things the hard way. If none of the above helps any just hang on and someone with more know how should come along soon. Just hate to see someone hanging out too long with no response and try to give my best help that I can.
There are some similiar datas within the excel and the database. The data type for the data that are similiar are the same. How do you do the appending? i am new to access so hope you could show an example to me. Thanks.
take a look at the small sample I attached. When you open it, there are 2 tables. One is the main table...or the one that would already have data in it. The other could be the one you just imported. I named them something like TableToAppendTo (main table) and TableToPullFrom (imported table).
Now, look at the main table data and look at the imported table data. Whatever you place in the Imported table will be appended to the main table when you run the only query in the DB. Look at the query. You simply create a query with the QueryWizard (or from scratch) based on the ImportedTable. Place in it whatever fields you want to Append to the main table.
Now, while in QueryDesignView, click on the QUERY word at the top in the MenuBar.
Select AppendQuery and note how it places fields from the main table in it.
Here, if you have any type of restricting criteria place it in the Criteria area.
You're ready to Append now. Simply click the RUN button (!)in the MenuBar to activate the query. You will see a popup dialog box that tells you that you are about to append x amount of rows. Click YES and that's it.
Now look at your MainTable again. The other records Appended to it are at the bottom of the table.
That's it. Let me know if you need more help. Also, that is all in the Access online help as well.