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=-

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

*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.

OK. Step by step. Here is the zinc and copper. :p

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

In our case we keep the frontend on the network drive and all clients have ODBC DSN linked to sql server.

found somthing useful for u

http://cwashington.netreach.net/depo...tType=vbscript

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'

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.

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

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.

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

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.

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.

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?

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

yeah, get ready to get a chuckle out of this. when I removed the rst.index = commands on the recordsets, the code worked fine. Everything gets deposited in the right place, and in the right order. It is a little slower, but then when sharing info between two things I was expecting a little bit of lag time. The only thing is this; I was under the impression that .index needed to be there for the recordset to work. oh well, guess I was wrong. Thanks for the input guys.

Brandon

Though it would appear that the rst.seek function is like wise not working, which is a rather large problem for me.
*shakes his fist at the Access gods*
is there another way to search a recordest besides the find and seek methods?

I have split a database as a temporary solution (enterprise is switching to Sharepoint) and need to undo the split. Do I simply break the link (if so, how do I do that), or is there another way to do it?

Any assistance would be greatly appreciated.