[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Access tracking database ideas

Need to steal some brain power, since I am lacking.

Problem:  I need an Access 2000 database that allows me to track "Problems", "Changes made", and "Resolution" if any.  Most importantly I need the text to be searchable.

Example:  On Tuesday 4-19-2005, one of the technicians "JOHN" (technician) had a user come to him about an error (Problem) in the database.......so he change two settings in the fee matrix (changes made) and found out later that DID fix the problem (resolution).   This could also be an issue with a certain asset (computer or printer).

Your job:  I want ideas ON DESIGN.  This will hopefully turn out to be a good project for me to do some learning.  I'm basically trying to partially duplicate a package I used before called "TrackIt!", but not interested in something that robust.  MOST IMPORTANTLY I WANT A DATABASE THAT IS SEARCHABLE REGARDING ALL TEXT.  In other words, using the example above with John........:  ---3 weeks later an issue occurs with fee matrix code XXXXX.  John remembers he made some type of change but isn't sure, so he does a text search and finds all instances (entries) related to "xxxxx" and immediately knows what happened.--

Too broad?

Ideas must center around Access 2000.  Give me design ideas.  Are there issues with being able to search all text in fields?  Points for deep thought and detail.

Best regards,
ME


0
DataLoser
Asked:
DataLoser
  • 6
  • 4
  • 4
  • +1
2 Solutions
 
jkorzCommented:
Well, access doesn't natively support full text searching. I know sql server does though. If you are planning on making this a distributable access app, you are going to have to go with a real SQL server, which will cost ya. If you are going with a web solution though, you can get MSDE and install it on your webserver. MSDE is a free version of SQL server, the downside of it is that you can't connect to it from a remote computer (which rules out distributable app). Either way you are going to want to look into SQL server and it's full text search capability before you go any farther.
0
 
DataLoserAuthor Commented:

Access 2000 has no way to do full text searching on fields?  That could be a bummer.
0
 
DataLoserAuthor Commented:
No web solution needed.  This would be used by only two people on a local machine.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jkorzCommented:
well, if it is going to be on a single machine, you can use msde as the back end and access as the front end

just make an access data project (adp) instead of an mdb

trust me, it's way better using msde behind access than it is using standalone access anyways MSDE just has way more features and capabilities
0
 
mlemanCommented:
I have built a database which tracks which field contents changed.

I set that afterupdate on field x then create record based on time and date and simply say field x changed

the same thing would happen over and over on the same record, so after changing information in 6 fields, the resulting record would say field x, y, z etc have been changed.

the created record table was simply id(date and time) and then da6ta changed which was a memo field.

we never needed any other information except which field had been changed not what the actuall data was.
full text search is difficult but you could use keyword searches via *bob* example.

you could use the same sort of thing with sql, and actually log the changed data as well, using the inserted and deleted temp tables and develope your record around that and then use sqls text searching capabilites.
0
 
stevbeCommented:
here is code that searchs all Text fields in a database using Like * & YourKeyWordHere & * and populates a table (tblResult) that you could display in a listbox. You could probably modify it to also search memo fields.

Steve

Public Function FindStringInMDB(strFind As String)

Dim rstResults As DAO.Recordset
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim strCriteria As String
Dim intField As Integer

Set rstResults = CurrentDb.OpenRecordset("tblResult")
For Each tdf In DBEngine(0)(0).TableDefs
    'skip system tables
    If Left$(tdf.Name, 4) <> "MSys" Then
        Set rst = CurrentDb.OpenRecordset(tdf.Name, dbOpenDynaset)
        If Not (rst.BOF And rst.EOF) Then
            For intField = 0 To rst.Fields.Count - 1
                If rst.Fields(intField).Type = dbText Then
                    rst.MoveFirst
                    strCriteria = "[" & rst.Fields(intField).Name & "] Like ""*" & strFind & "*"""
                    rst.FindFirst strCriteria
                    If rst.NoMatch = False Then
                        rstResults.AddNew
                            rstResults.Fields("TableName").Value = tdf.Name
                            rstResults.Fields("FieldName").Value = rst.Fields(intField).Name
                            'not that this will likely buy you much ...
                            rstResults.Fields("SearchFind").Value = rst.Fields(intField).Value
                            rstResults.Fields("AbsPos").Value = rst.AbsolutePosition
                        rstResults.Update
                    End If
                End If
            Next
        End If
        rst.Close
        Set rst = Nothing
    End If
Next

End Function
0
 
DataLoserAuthor Commented:
jkorz -

    Is it possible to creat an Access database first to start the gathering of information, then convert into the MSDE/.adp design (after we figure that out) at a later date?.......or would this introduce a level of complexity too great to justify the extra step?

ME
0
 
jkorzCommented:
it sure is

access is made to upsize to sql (that way billy's pockets get fuller)

just go to the upsizing wizard under Tools-> Database Utilities when you are ready to make the move

even if you do start out with msde as the back end though, you will barely even notice any difference, everything operates just like it would in an mdb
0
 
stevbeCommented:
If you start with an Access back end make sure you go to the Tools --> Options --> Tables/Queries tab and check the SQL Server compatible syntax. This will help you write queries that will work for both Access and SQL Server (MDSE). I would also suggest that you do not use any Form.Control references in your query criterias and also try to avoid using Expressions in your queries as you will likely need to convert them to the equivalent TSQL functions when you convert.

Steve
0
 
stevbeCommented:
and don't use DAO for anything, use ADO.

Steve
0
 
DataLoserAuthor Commented:
stevbe & jkorz.....thanks for the input again.

stevbe & jkorz.....another couple questions::.  
                       jkorz...........You said "---- even if you do start out with msde as the back end though, you will barely even notice any difference, everything operates just like it would in an mdb". ------

1)      Does this mean that even though I'm relatively NEW to Access 2000 it might make sense to go the ".adp" route??
      *Oddly enough, SQL (version 7 is what we use at work) is also something I need to get more familiar with.

2)      Also, does your statement mean that starting a NEW database in Access 2000 with MDSE loaded on the PC will
        allow an ".adp" route and automatically start populating tables in the SQL database??
      *If you can, explain the mechanics of how this would work; meaning, what difference in steps with Access 2000 would I go through assuming this MDSE (sql 7.0 version?) was running on the same machine.  Also, any special steps to 'ready' the MDSE for the Access front end.

    THANKS again!!

0
 
stevbeCommented:
Access has all the design tools you need for adp, including making the tables / stored procs etc. in MDSE/SQL Server (which are both the same SQL engine and file format). As long as MSDE is installed and running on the PC Access is good to go.

Steve
0
 
DataLoserAuthor Commented:
stevbe & jkorz.....another couple questions::.  
                       jkorz...........You said "---- even if you do start out with msde as the back end though, you will barely even notice any difference, everything operates just like it would in an mdb". ------

1)      Does this mean that even though I'm relatively NEW to Access 2000 it might make sense to go the ".adp" route??
      *Oddly enough, SQL (version 7 is what we use at work) is also something I need to get more familiar with.

2)      Also, does your statement mean that starting a NEW database in Access 2000 with MDSE loaded on the PC will
        allow an ".adp" route and automatically start populating tables in the SQL database??
      *If you can, explain the mechanics of how this would work; meaning, what difference in steps with Access 2000 would I go through assuming this MDSE (sql 7.0 version?) was running on the same machine.  Also, any special steps to 'ready' the MDSE for the Access front end.

    THANKS again!!
0
 
jkorzCommented:
1) yes, access's database part (as opposed to it's front end part which is forms, reports, macros and modules) gets it's structure from SQL. There are many features that SQL has however which access does not. You might want to talk to the department that maintains your sql servers and ask them if they will put your database on their test server. (that way you can do your database and learn sql server 7 at the same time)

2) Yes! When you create a new .adp in access it will give you a wizard which will let you either use an existing sql database or create a new one. As long as you have access to an sql server (or msde) you will be able to connect it to access. Once you have the adp set up (which is quick) you can use the adp just like you would an mdb with a couple differences. The adp file contains everything but the objects in tables and queries, those are on the sql server. In your queries tab you can now make stored procedures and user defined functions (which you can't in access). You can also make triggers on tables (which will perform an action every time data is inserted, updated or deleted from tables) VERY POWERFUL FEATURE!!!. You can create a lot of stored procedures and udf's in query builder, but for the more advanced ones and for triggers, you will need to read up on transact sql (t-sql). T-SQL is the programming language of SQL server and is similar to vb although it is very procedure oriented.
0
 
DataLoserAuthor Commented:

"stevbe" and "jkorz"  THANKS!!

Really appreciate your attention to this question and your expertise.

Best regards,
ME
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now