Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Database Frontend

  1. #1
    Join Date
    Jun 2003
    Location
    Bloomington, IN, USA
    Posts
    116

    Database Frontend

    Ok, I am winding down on my project, and I am workign on allowing multiple users to access the DB at the same time. From what I have read, the easiest/safest/geheraly best way to do this is to create a second front-end database that only contains your forms and maybe a few very localized lookup tables. You then link this 'Front End' to your 'Back End' database which is where all your information is.

    While that makes sense, I cant figure out how to do it to save my life. Ive been scowering my small but valuable library of Access books to try and figure it out, but have thus far been unsuscessful in finding anything to help me. Anyone got any hints or leads they could feed my way? I'd be most appreciative.

    Brandon
    -=only two thins are infinite, the universe, and human stupidity; and im not positive about the former=-

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    we put access frontend in a a network location and all link tables 's ODBCs are created on all the workstations (Network admin pushes the script along with login scripts)

    It has been running fine in multi user mode for almost 2 years.

    Now we are moving that project to .NET

  3. #3
    Join Date
    Jun 2003
    Location
    Bloomington, IN, USA
    Posts
    116
    *blink*blink*

    ...huh?...

    possibly a little more desctiptiveness on the process? or on a possible reference? I havent put my daily wash of zinc in yet.

    or maybe another way to go about it? Im perfectly open to that as well.

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    OK. Step by step. Here is the zinc and copper.

    0. Create database with only tables queries view (where data is going to reside) etc - Say DB3.mdb and copy that to a Network drive eg: \\fileserver\DB3.mdb

    1. Create Access database 1 where you have all the look up table (table where the data is notgoing to change) - Say DB1.mdb

    2. Create your access frontend database which has all forms and switchboards.- Say DB2. This has link table (System ODBC DSN) to the database DB1.mdb and also to the DB3.mdb

    3. OK. The above works fine for one machine. In order to copy Db1 and Db2 to all the client's machine you can write a batch file to copy it. In order to create ODBC DSN to all the client machine you could use somthing similar to the below code where pclist.txt will have all the client machine's names. Add the right key values and run it. it will push it to all the machines. (Ask your network admin or VB developer to enhance the code. It is rusty now)

    On Error resume next
    Const HKEY_CURRENT_USER = &H80000001
    Const HKEY_LOCAL_MACHINE = &H80000002
    LogFile="c:\Errorlog.txt"
    inputfile="c:\pclist.txt"
    set fsomain=createobject("scripting.filesystemobject")
    set Errorlog=fsomain.opentextfile(LogFile,8,true)
    set pcsinput=fsomain.opentextfile(inputfile)


    Do until pcsinput.AtEndOfLine
    strComputer = pcsinput.ReadLine
    Set objReg=GetObject("winmgmts:{impersonationLevel=imp ersonate}!\\" & _
    strComputer & "\root\default:StdRegProv")

    strKeyPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Windows"
    strEntryName = "xxx"
    strValue = "yyy"

    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strEntryName,strValu e

    objReg.GetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strEntryName,strValu e
    'Wscript.Echo "Current WSH Trust Policy Value: " & strValue

    Errorlog.WriteLine strComputer &vbtab& strValue
    loop
    Errorlog.close

    4. It all depends on where you want to keep the DB1, DB2 and Db3 and also depends on how you want to split the databases. We evolved from Once single access file to one access frontend and sql server database.
    Now we are evolving from that environment to .NET
    Last edited by MAK; 07-10-2003 at 07:47 PM.

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    In our case we keep the frontend on the network drive and all clients have ODBC DSN linked to sql server.

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  7. #7
    Join Date
    Jun 2003
    Location
    Bloomington, IN, USA
    Posts
    116
    here's the crap part,... i >AM< the VB develpoer! and the database developer, and my departments personal IT person.

    the even crappier part ... the amount of what i knew about VB before i started this prolly wouldnt fill up 2 pages if i wrote big, and what i knew about access MIGHT fill up 3 pages.

    Yes, I know this post has nothing to do with anything, its called venting in a controlled environment, so ban me. :P

    And It doesnt help that every day my boss tells me something new he wants me to add to this damn db, and he doesn't give me the time to accomplish the task from the day before, then he complains cus I'm not done!

    ok, I feel better, so I think I'll take my hysterics and go home and stop scaring the children.

    Brandon
    'the mentaly-clensed'

  8. #8
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Hahhaaha..

    I think you are well aware of this IT job Market now.

    So your Boss is always right no matter how mindless he is.

    Goodluck.
    Last edited by MAK; 07-11-2003 at 08:04 AM.

  9. #9
    Join Date
    Jun 2003
    Location
    Bloomington, IN, USA
    Posts
    116
    One thing I am not understanding is why, in my front end, I need two different databases? why do I have to put the lookup tables in a different DB than the forms/reports/ect... That just doesnt seem like an efficient use of space. Could you explain this to me if you don't mind? Simple-not-uber-technical version will work if your in a hurry.

    Thank You

    Brandon

  10. #10
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    If you are placing your frontend in a network location, and if your lookup file is huge then it takes long time to retrieve. so you can put the lookup file in the local client machine in a separate DB.

    As I said before , you can keep all DB1, DB2 and DB3 in the same place (as one DB) and location. It still works. speed in data retrieval and writing is slower on the network than on the local machine.

    I suggest you keep the frontend and lookup tables in on DB on all local machines and Data on the network (single location). In that way you can gain performance in graphics and lookup data.

    The reason I keep in two different places is in our case some users have more data in lookup and they maintain it. so whenever we have a new release in the frontend, we push only that database instead of changing the code in every machine.

  11. #11
    Join Date
    Jun 2003
    Location
    Bloomington, IN, USA
    Posts
    116
    Ahhhh.... I understand now. Ive been able to get about half way in this conversion, and the farther I get the happier I think I'm going to be that I had the forsight to save an un-tampered version on my hard drive.

    Well, wish me luck, time to start linking...

    Brandon

  12. #12
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    What is your approach now? and How many client machines you have to install based on that you can use or dont have to use my code.

    If it is a few machine, you can just create ODBC DSNs your self.

    More than 5... I will go with the code. But you have to cleanup the code cas' wrote long time back. very rusty.

  13. #13
    Join Date
    Jun 2003
    Location
    Bloomington, IN, USA
    Posts
    116
    I have all my actual data tables in the backend on our server, with a second front end with all the forms, reports, lookup tables, and queries in it.

  14. #14
    Join Date
    Jun 2003
    Location
    Bloomington, IN, USA
    Posts
    116
    it looks as if the recordsets in my front end are broken. it keeps hanging up and giving me errors on both defining the primary key of the recordest, and when i try to update the recordset afterwards. Any thoughts? ... Anyone for tennis?

  15. #15
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    2
    ALink21 Carl Tribble's Back End Relinker code
    TAUpdateFE Ted Avery's Front End Updater code
    Have a look at these two databases
    select useful files on left side menu
    http://www.colbyconsulting.com/UsefulFiles.htm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •