Link Excel file to MS Access and generate rep

[Priceless]

Hello,



I have an Excel file with 1000 lines of Information. I would like to link this file to MS Access and have one windows wherein it will ask user to enter Product ID/ or Product Type/ or Supplier Name. If the information is found it should be displayed with the complete Row details from Excel file.

Can someone guide me how to build the table, query and report as I am a beginner in MS Access.

Thanks and regards,

[thhui]

Using Excel as backend is just the opposite of normal usage of access as back end database.

I think it was a bad idea as Excel data could not be updated by access normally (Revised: not read-only, in fact you can somehow change and add the excel data from msaccess. However, excel is not a multi-users software. So only one user can change it and no one else could open the excel when access is opening it. Besides, you cannot delete data from access.)
and the performance will not be good either.

[Priceless]

Thanks. I fully agree with you. I need to do this as I dont want to share the excel file to 8-10 users which contains confidential and price related issues. Using access the users can search and get the relevant information only instead of the complete xls file.

Hope you can help.

[thhui]

Then link this excel file to the access table.
Just do what you can do in the form wizard out of this excel table.

[Priceless]

I can only say this forum has bunch of jokers as experts! one of the simplest solution i needed from Access and the so-called experts are beating round the bush.

[RuralGuy]

I can only say this forum has bunch of jokers as experts! one of the simplest solution i needed from Access and the so-called experts are beating round the bush.

That comment was unnecessary!

[Priceless]

Please see the question i have asked and the quality of response i received. You would then agree with my comments.

[RuralGuy]

I did that before I made my post. Snide comments are simply not welcome on this Forum. All of the "experts" are volunteers and do the best they can. You do not have to take *any* of the advice but accept it in the spirit with which it is given.

[thhui]

A few websites could be recommneded for beginners of access.
You then know what you can and cannot do on Access.

http://www.baycongroup.com/access2007/index.html
http://www.quackit.com/microsoft_acc...ate_a_form.cfm

It seems that the question really be related to "Access" and "Forms" instead of "import and export".

[thhui]

There are many ways to accomplish the result.
But it is too troublesome to explain in details.

Just give the final product for you to test yourself.
test.xls(test.zip) and test.mdb

I am stunted on the not read-only status of excel source file.
In fact, you can add rows and edit the excel data from access (but deletion of data is forbidden) under the excel link data source.

In the example given, I changed the excel file to read-only status manually.

Put them both in c:\
The required form is FinalForm.
You just fill in the 3 testboxes correctly, then the following sub-form table will display the result.

For example,
just fill 1, 10, 100 in the 3 textboxes. You'll get the desired result in the sub-form.

PS: Access file in 2002 Format.

[Priceless]

Thanks for your efforts. When I ran the .mdb file it opened with a form as u mentioned but when i opened the form the serach button isnt working. i am uploading a SS of how it looks on my screen.
http://wikisend.com/download/428468/...-14_155955.png

[Priceless]

I have uploaded a demo sheet with actual column heading and
data.
http://wikisend.com/download/485396/Demo sheet.xlsx

The user will search for either EMS no. or Customer Part No. OR Product Type
or Supplier Name. and if any is found then display the rest of info.

sorry for bothering u.

[thhui]

Further refinement for your request.

test2.xls(test2.zip)
test2.mdb(test2mdb.zip)

Put them both again in c:\
The required form is Final2Form.
You just fill in the 4 textboxes correctly (changed to Either Or condition), then the following sub-form table will display the result.

This time, you must click the Search Button for the underlying query to refresh the result in sub-form.

[Priceless]

It works like a charm. I tried importing my data (ard 1350 rows) and tried running the form it gives me the following error:
http://wikisend.com/download/611634/...-20_171719.png
If i try with 20-30 rows of data it works fine.
Can you advice what is the problem.

[thhui]

The excel file might contain some data whose column data type is not compatible with the Access (inferred) field type setting.

This is really a problem in using any excel file as backend.
For example, a string column cannot mix with any numbers in it. (You must format any numbers as text label first)

I had scanned your excel file and found that you have some data wrongly interpreted as numbers while they are in fact the string column.

Therefore, pls scan all the errors first. All numbersin the string column should be given a ' on the left so that it is identifed as label or string type.