Problems accessing a split database

[tobydobo]

you are the bom diggity! That did it. I added an email form which needed the additional Ms office object libraries evidently.

[tobydobo]

Another strange combo box (different one) issue I just noticed with my split db. Unlike the other combo box mentioned previously, which just displays information when I make a selection, I have another one that autopopulates and saves to a table using the AfterUpdate event. For some reason now, after I split the db, the value in the combo box is blank after the info gets saved. I have confirmed the data is still getting saved, its just that the value selected from the drop down disappears after the selection is made. I still have a non-split version of the same db and I have verified that it works fine on that copy. Here is the AfterUpdate code im usuing for the combo box:

Private Sub Combo466_AfterUpdate()
Me![Supported Site(s)] = Me.Combo466.Column(1)
Me![Site Type] = Me.Combo466.Column(2)
Me![Site Address 1] = Me.Combo466.Column(3)
Me![Site Address 2] = Me.Combo466.Column(4)
Me![Site Address 3] = Me.Combo466.Column(5)
Me![MSO 1] = Me.Combo466.Column(6)
Me![MSO 2] = Me.Combo466.Column(7)
Me![MSO 3] = Me.Combo466.Column(8)
Me![SC Queues] = Me.Combo466.Column(9)
Me![Manager] = Me.Combo466.Column(10)
Me![Manager Phone] = Me.Combo466.Column(11)
Me![Manager Cell] = Me.Combo466.Column(12)
Me![Manager Email] = Me.Combo466.Column(13)
Me![Manager 2] = Me.Combo466.Column(14)
Me![Manager 2 ph] = Me.Combo466.Column(15)
Me![Manager 2 Email] = Me.Combo466.Column(16)
Me![Manager 3] = Me.Combo466.Column(17)
Me![Manager 3 ph] = Me.Combo466.Column(18)
Me![Manager 3 Email] = Me.Combo466.Column(19)
End Sub

[June7]

Don't know why the combobox goes blank. I am confused as to why you are saving all this data. It appears to be duplication of data.

[tobydobo]

Well thats my next challenge. Currently my contact list depends on 2 equally important criteria: Tech name and Site Name. My contact list is sorted by Site so that I see each tech assigned to each site....hence my delema of duplicate data. When I create a new tech record I also specify the site and all the other site specific data auto-populates into the tech's record....thus creating the duplicate data. Im trying to think of the best way to fix that and I have come up with one possible solution:

Turn the contact list into just a site list. When you open a site record, you will see the assigned techs on the subform that pops up. That way we will have to have one site record per site. My thoughts were to use a split subform, with site specific data at the top and a datasheet at the bottom with the tech's records. I would assume though that the tech's records would now need to come from a seperate table? And...that would bring me to my first question....

So if the subform is generated off one datasheet (main list), how would I get a split subform to display a datasheet from another table? AND....have it to where i can click on each tech record each pulling up a seperate subform off the parent subform maybe?

Hopefully im asking that right....and maybe this should be a new topic. Let me know. thanks.

[June7]

Not understanding the dilemma.

Can each tech have only one site? The contact list is only techs?

Why don't you save just the siteID into the Tech record?

[tobydobo]

Some techs work multiple sites....and the sites themselves have contact info associated. Each site will have multiple managers, site contacts, addresses, each with contact info. That information will stay specifiic to the site which is the reason why I had the form autopopulate. With my new idea though, the main contact list would be just the sites themselves....then as you open each site you will see the individual tech's assigned to that site. It seems the only way to do what I need to do is to create a site specific table and a tech specific table. Hope im not confusing you more with that explanation.

[June7]

Still somewhat confused. You have a table of contacts and a table of sites. Contacts have many sites and sites have many contacts - a many to many relationship, requires a junction table.

tblSitesContacts
ContactID
SiteID

If you need more help, provide the project for analysis.

[tobydobo]

I thought as much. I need to research the many to many relationship a little more though....can you point to a good tutorial?

[June7]

Check out the 'sticky' threads at http://forums.aspfree.com/microsoft-access-help-18/

[tobydobo]

Im thinking about going back to a non-split db again. My team is spread across the country and we're using a sharepoint for the backend. Its soooo slow now. BUT.....i think I can still incorporate your "revision update" code as a workaround for this, which will update everyone's local copy. If I update my table links to have all the regular tables stored locally, except for just the one table that stores the version information...and let that table be the only table stored on the sharepoint, that woudl work right? That way when they open their local copy, which would also connect to that one small table on the sharepoint, verify the version, the prompt to download a new revision everytime there is an update to the db. Is that correct?

[June7]

I have no experience with Sharepoint. Only read about it. Slow is one criticism have seen. Another had something to do with record lock conflicts I think.

What do you mean by 'regular tables stored locally' - on network server? How can everyone run an independent copy of data tables and data remain synchronized?

[tobydobo]

A sharepoint is can be mapped just like a normal network path but because we are all remote user's we just use it as a central repository.

But I meant I will unsplit the db, having all the main tables stored within the local copy of the db...and only have the revision table linked to the sharepoint. Before I split the db....we would just backup the db to a shared location and everyone would download the new copy. We just had to let the others know we were making an update so they wouldnt make an update at the same time and potentially overwirte the other's update.

There is only 3 of us that use it so that wasnt a big deal. The split db fixed that issue, but the degradation of performance is not worth it. So now we would have to go back to the old way and only allow 2 people to edit the db and notify each other when db is being updated. I was thinking about possibly using the revision update code to notify others when they open the db that there is a new update. But in an effort to keep from overwriting other updates, maybe we could also add some sort of 'beforeupdate' code that checks for a new revision before data is added as well...maybe? Then if no new updates found it will save the data. If it detects a new version, it will prompt the user to download a new copy again before updating the current session. I dunno, seems like it may work.

[tobydobo]

but....first things first. I want to see if I can get the revision code to work first.

I understand where the Form Load script goes, but where would I insert this?

Const OverwriteExisting = TRUE

Set objFSO = CreateObject("Scripting.FileSystemObject")

objFSO.CopyFile "\\Fritz\Admin\Materials Lab\Install\MaterialsDatabase.accdb", "c:\", OverwriteExisting
MsgBox "The Materials Lab Database Update Was Successful!"

[June7]

You do understand that code is not within the Access project? That is a VBscript. Create a VBscript file with simple text editor such as Notepad. It will save the file with txt extension. Change the extension to vbs.

In our setup the user copy of the frontend and the VB script are located at C:\ root on their workstation. This is because our IT securities won't allow the script to copy into other folders.

[tobydobo]

ok that makes sense. So what are your thoughts on my previous idea?