Search Access Database through Excel Userform

[sanits591]

Hi,

Given Data
I have an access database with 30 tables, sequencing from TableNameA,TableNameB, ...... TableNameZ....TableName1.....TableName4, with Columns A (having one field), and column B (hyeprlinked Text) in each Table.

Requirement

I need to have a userform in excel (as front end) with textbox as a search field for the entire Tables and column A, column B. When the text is entered in the textboox then list box on the userform must get the values and its related searches, like applicable for wild card characters also.

Any suggestion or help shall be appreciated in this regard.

Anticipatory Thanks!

 

[Norie]

Why not create the form in Access?

By the way, having 30 tables is not a good idea in Access.

It's going to make searching quite difficult, whether yo do it in Access or from Excel.

You should probably consider creating one table.

That shouldn't be too difficult if the tables have the same structure, which I think they do.

 

[sanits591]

Thanks! with this, i will just re-frame my requirement with a change in the available data, and the objective shall remain same.

New Data structure

An excel sheet having cells filled with hyperlinks from columns A to column E or so occupying rows upto 100 (this is more in case of original data, such that there are more than 1 mn cells with hyperlinks). File is attached for the reference.Sample file Database

Requirement

Now i want to have a search through excel userform in this database which shall do the same activity as was done in the previous exercise in the link http://www.mrexcel.com/forum/showthread.php?t=576483

Keeping one more step in mind, that web control which opens up must display a portion of the webpage (not by setting the height or width) but through webtable numbers (as specified in the VBA codes).

Options Available

1. We can prepare a database in another workbook and may link the userform from that database. The necessity of making different workbook is due to the size of the database.

2. We can make a database in the access (as suggested for database) and can pull out the information in our VBA excel userform.

Constraints

1. The use of Excel as front end is more convenient to Users who shall be using this application.

With the above considerations, i am trying to complete this as a project. Any other suggestions, or how to accomplish this task shall be appreciated.

Thanks!

 

[Norie]

That's lot of requirements.

The first thing I would suggest is breaking it down into easier to handle pieces.

One of those pieces would be to design the database, which shouldn't have 30 tables.

I'm not sure you even need hyperlinks, all you actually need for the webbrowser is a URL.

Not even sure you need a URL for every term/record.

Isn't there a list of some sort with the base URLs for all the sites you are going to use?

Oh, almost forgot - why wouldn't a form in Access be user friendly?

 

[sanits591]

In my opinion, the friendly name instead of URL is required which shall make the search faster, as the user shall be punching the friendly name in the textbox to search for the text.

URL is required, as every word is linked to URL and shall be giving results from that particular website.

This URL list is retrieved from the webquery, and base URL is same, but the last string of the URL varies a lot, which actually is not similar to the words in database.

1. I have done some other coding in excel for the Security purposes, which need to be done again for Access, if i use the form of MS access.

2. The other point is the Users of ours is more friendly with MS excel, and have not anytime dealt with MS access.

That is why the above requirement of Front end as excel has been set up.

Thanks!

 

[jkpieterse]

Sounds like all the users will do is work with your userform. If that is the case, then a form in Access is exactly the same regarding how to work with your application.

Whether or not the user knows how to use Access is irrelevant, all they should be seeing and using is the form, which requires only basic computer skillls.

Whether YOU are fluent in Access is an entirely different problem, as you will have to design the form and set up the database structure.

 

[sanits591]

Thanks for it!, but the inhibition is, most of the exercise related to other features have already been done or are in process with MS excel as an application for front end. The MS access shall have more user friendly features, but i need to have more conversant with it before evolving anything good out it.

Moreover, hands-on with MS access is on the very beginning stage for me, and shall take more time to accomplish this task, if the entire project is taken to MS access.

That is why, i am looking typically for Front end as MS excel, and back end can be MS excel or MS access depending upon the ease of handling the size of database.

So, requesting to enlighten and assist me on the above.

 

[Norie]

I really don't think you understand what I mean about the URLs.

Are you really considering having a URL code every term in every table?

In fact you say yourself that there are base URLs and all you need is to add to those to get the full URL.

As for the friendly names, simple a field for the friendly name and a field for the 'last string' of the URL.

Then when you want to get the full URL you take the base URL and concatenate the rest.


You also mention web queries for some reason.

Where do they come into it?

Lastly what security?

If you are trying to hide the actual data from the user it'll be far easier to do in Access than Excel.

 

[sanits591]

Thanks! Noorie, for responding to it. It could be stupidity or bad on my part. I shall try to elaborate the notations what i have meant or understood from the earlier post is:

Word = Simply a word list not linked to any URL
Base URL = "http://www.webster-dictionary.org/definition"
Variable String = e.g. "deep+temporal+vein"
Friendly Name = deep temporal vein

If we have a word list database, then it is not necessary that website can be accessed, as Word is not equal to Variable string (sometimes "hyphen", or "space" is used.

So suffixing the word with the base URL does not lead to access of the required webpage of the website. That is why, i thought of having the complete URL behind every word, or Word as a hyperlink to the required website.

In all cases it is not necessary that exact URL can be retrieved from the word without having the URL code (Base + String) behind the words in database. That is why the concatenate shall not be applicable as per my understanding.

The web queries were used to retrieve this Word List with hyperlinks from the Website, for which the sample file is enclosed in my first post on this subject. It was used earlier, and may not be required further for this.

As i have earlier mentioned, i have got hitch in using all the stuff in Access, as i consider myself as novice in the MS Access, and all other features like, :

1. Creation of Users and with the personal information for further contacting the user.
2. Security from opening the project and shall be opened on the PC for which it has been requested.
3. Opening the Project, if and only if the internet is connected.
4. Time bound features, i.e. getting the dates from Internet which validates the validity of the project for the user.
5. Internet mails connected to the project, which shall send the thanks mail every time on the use of the project or with some other connected features.

And some more.....

Now, probably i am little bit clear in explaining to the queries raised.

My apologies, once again to make you annoyed, as perceived.

Thanks!

 

[Norie]

I'm not annoyed and I don't know why you think that.

I still don't thing you understand what I mean though.

In Access you could have a field with the word (friendly name) and a field for the search terms you need to use for that word.

The user is only presented with the friendly names as it's all the user needs to see.

You can then use the words the user selects to get the actual search terms you need.

If you had a list of URLs, both base and full you could link it to the word table.

Then when you want to get The URL you get the search terms from the word table and the URL from the URLs table.

If there is nothing to add to the URL, eg search terms, you can just use it as it is.

If there is something add them to the base URL.