Microsoft Access - Help

[muddyfox470]

Hi

I am creating a massive database full of references and documents for the place i am working for.

I am using Microsoft Access to create all this and so far there are near 1700 entries.

Is there anyway of archiving every single word that is in the document, so that it is then possible to search for a single word or something to come up with a document (or list of documents)?

Anything that could point me in the right direction and would cause minimal effort would be great.

Cheers

Ian

PS. Are there any programs that scan documents for words, i.e. like google, but for a single document? It needs to be quick and easy to use, for those people who come back to the document to find the references they need!

PPS. I dont know whether this is in the right section

[Spunkey]

first off, Access wasnt designed to hold a great deal of data. ~2000 records is its limit before you start to hit major performance issues, and having to start compact/compact/repair the database every time you open it

Secondly, if all you want is a tool which you can use to search within files Agent Ransack does that for you - admittedly its not free, but its a lot easier and far less painful than creating a giant Access database. Running a string search through a x thousand record Access DB will give you nightmares, trust me

btw OS and Apps would probably be a better place to put this, I should think a nice mod would move it for you

[Dougal]

Its relatively easy to find stuff in a DB.

Just create an input form, grab the contents of the box when the command button is clicked, put it into an SQL statement based on the table and put the query output into a list box on another form.

[muddyfox470]

Its relatively easy to find stuff in a DB.

Just create an input form, grab the contents of the box when the command button is clicked, put it into an SQL statement based on the table and put the query output into a list box on another form.

Come again...

Lol if i understood any of that i guess i would find that very helpful, but i am not so much into the programming and software side of things where computers are concerned.

If you could guide me, i would be very appreciative!

Ian

PS. Please bear in mind that i would not like to re-enter all this data again! lol

[Spunkey]

ok if you really want to do it, and assuming the data to be fed in is in a plain format (ie txt or csv) heres the basic process of how you'd work it...

1. Create a form to handle the UI, in this you just need the user to tell you where the file to import is and a button to hit to actually do the importing.
2. When they submit, open the file for reading using the FileSystemObject. Grab the file 1 line at a time. First remove all punctuation (unless you want to keep them), and then split it into an array using space (" ") as the delimiter. This way, each word will become a single element in the array.
3. Loop through the array and for each element in it, create a query to insert the word into the database.
4. Repeat 3 until at the end of the file.

Seriously though, if this database is going to get much bigger you are going to need to run it on SQL server. Especially if you're going to have people searching through thousands of unindexed fields.

Hope this is of some help!

[muddyfox470]

Cheers rubbishrubbishrubbishrubbishrubbishey,

So is it best to save what i have already and convert it to, for instance a comma delimitted format (or whatever) and then feed it in as you mentioned?

UI? whats one of those - user interface?
How do you create one of these in / for access, i am guessing there has to be a guide somewhere, anyone able to find me a good one?

Cheers

Ian

[Spunkey]

sticking it into a text file would probably be easier and quicker for you. Comma delimited isnt necessary in this case.

yup UI = user interface

If you look in your access db there is a tab called Forms, if you New then Design View - it becomes VB! hurrah!
Don't know if you've had any experience with VB, if not this could be a tough challenge for your first try.

[muddyfox470]

VB = visual basic? slow down man

oh well if its tough, any idea of any simple guides to follow? - i can be quite lucky!

ian

[Dougal]

VB isn't the hardest thing to learn.

In access you are actually using VBA. The cut down version.

And the code compeletion is a godsend.

Normally when you create a text box is design view you get the text box (called Textxx) and a label (labelxx).

I leave the label as it is as you won't be referencing it. The text box however is easiest to rename txtYYYY (i.e. txtName txtDate and so on).

Create a command button (call it cmdSubmit for instance).

When you have created these (simplest one this) you can switch to the code vie, vie -> code.

So lets say you have a text box called txtName, the command button called cmdSubmit and a table called tblData. And yes I do name my stuff in this way, I also call my forms frm and reports rpt.

In code view you should see two drop down boxes next to each other. In the left hand box select cmdSubmit.

This automaticly produces a little bit of code for the OnClick() function which you will see appear.

Now in here you need to begin by checking that data has been input. Easiest way

If IsNull(txtName) Then
     MsgBox "Sorry Data has not been input"
     txtName.SetFocus
     End Sub
End If

Note that no Else is needed as we exit the function if it is true, the rest of the code will only occur if we IsNull returns false, i.e. there is something in the box.

Next we have two ways of doing this. I'll show you the query way now and if you want to I'll post the other, slightly more complex, but more flair, way of doing it.

Now we have to add a query into the database, call it qryLookup.

What we'll do is open the query, set the query to look for all records in which the Field "Name" is like (i.e. contains the term from txtName)

'note I am not 100% sure about this, I have not used VBA that long and have left my 'code at work

Dim strSQL as String
strSQL = "Select * from tblData WHERE Name Like " & txtName & ";"
CurrentDB.QueryDefs("qryLookup").SQL = strSQL

IIRC this takes the query and changes it.

Now create another form, frmResults, and create a largeish list box in it.

Set the listbox to have its lookup as a query, set that query to qryLookup.

Now back in the original form you have, at the bottom of the last bit of code we want to close the current form and open the form with the newly defined query.

DoCmd.Close acForm, Me.Name, acSaveNo
DoCmd.OpenForm "frmResults"

Then you should have the End Sub function.

As I say I am not perfect onm this, but that is one of the easiest methods. I personally prefer the other method which I'll write up for you on Monday.

[muddyfox470]

Ok thanks for that.

I will have to re-read it tho when i have enough time to see if i can get my head aorund it

Cheers

Ian