Problems accessing a split database

[tobydobo]

Also.... I've created the table with just the version number and just called the table "version". So my question is, is there anything I need to put in the code to have it look at that table? I cant see anything in the code that refers to a table currently. Is the lable on the default form somehow linked to the table maybe?

[June7]

Not quite following.

You want to make the db not split?

Each user would have their own local copy of everything, including tables? I don't understand how this allows simultaneous users to edit data. The data must be centrally located.

Be aware, vbs extension makes the script an executable. Can't double-click on it to open for editing, must right click and OpenWith or open Notepad first then have Notepad open the file.

[tobydobo]

Yea until i can find a faster network share solution im going to have to unsplit it....just too slow. And yes i understand about the vbscript now. Can u also answer my last question about how the code knows to look at the revision table you create?

[June7]

The code is in one of the links on the thread I referenced back in post 9. Here it is http://forums.aspfree.com/microsoft-...ue-323364.html

It uses a RecordSet in VBA. I have since changed my code to use DLookup instead.

Label on form is not linked, labels can't be linked to table. I have to manually change the label and the value in table whenever I want to serve up a revision. The user's copy runs the procedure when the project opens and compares that copy's label to the value in the backend table. If different then update is copied.

So the version table must be in a split centrally located backend. How this can be made to work for users scattered around country is beyond me. My setup is designed to work with all users on local network.

[tobydobo]

I think Im almost there, however, Im still struggling with how the code knows to look at that specific table I created. I have made the changes to the db, linked the table, copied the vbscript to project path location but gets an: user-defined type not defined error. Do I need to add a different reference for this particular code? You said you use Recordset to determine the table. Do I need to update that part of the code with the name of the table maybe?

[June7]

If you use recordset do need reference. I think 'Microsoft ActiveX Data Objects 2.8 Library' is the one. DLookup won't require that.

Yes, must use your table name.

[tobydobo]

ah ok....can you point to the places I need to update? Im assuming this is the line that needs to be edited:

'Select record from Update if user's version number in Label398 matches
rsUpdateCheck.Open "SELECT * FROM Updates WHERE Version='" & Me.lblVersion.Caption & "' ;", cn, adOpenStatic, adLockPessimistic

[June7]

Updates is the name of my table that stores the version number. It is a table with one field and one record.

I double-checked my current code. Forgot I had changed it so doesn't even use DLookup. The default open form is bound to the Updates table and a textbox is bound to the field. Label is still there. The Form_Load event code is:

Dim Shell
If Me.tbxVersion <> Me.lblVersion.Caption Then
    If VBA.Environ("UserName") <> "I put my userid here so that the program won't attempt to replace my master copy" Then
        'Check for updates to the program on start up
        'If values don't match then there is a later version
        Set Shell = CreateObject("WScript.Shell")
        Shell.Run CurrentProject.Path & "\Update.vbs"
        Dim WAIT As Double
        WAIT = Timer
        While Timer < WAIT + 3
            DoEvents
        Wend
        Set Shell = Nothing
        Application.Quit
    End If
Else
    Me.tbxVersion.Visible = False
    UserLogin
End If

[tobydobo]

Ok I think im almost there except Im still using the previous code. I have bound my default form to the new table and a text box to the field. I got past the other errors but now it saying I need to modify something on this line:

rsUpdateCheck.Open "SELECT * FROM Version WHERE Version='" & Me.Label1.Caption & "' ;", cn, adOpenStatic, adLockPessimistic

[June7]

What is the exact error message?

Is the field also named Version? If it is a number datatype, remove the apostophe delimiters from the WHERE clause:
WHERE Version = " & Me.Label1.Caption & ";"

Otherwise, don't see problem with syntax.

I think you will have less trouble if you switch to my revised code.

[tobydobo]

I got it working woohoo! Thanks June7!!

[tobydobo]

Hello again,

I am now at a point where I want to explore the Many-to-Many relationship you suggested. I have created a seperate junction table to link TECHID from my contacts list and SITEID from Sites list. Now I suppose I need to go through my contact list and somehow link the techs to their appropriate site i guess? Still green in this area, so any other links or advise you can give would greatly be appreciated.

[June7]

Sounds like time for a new thread if you are having issue with this. I don't remember what I suggested.

[tobydobo]

Thanks. I opened a new one here: https://www.accessforums.net/access/...html#post91204

Feel free to take a look.

[tobydobo]

Hello again....back on the split database thing. I have everything working finally but notice that if 2 or more people use it at the same time, the db gets an hourglass for quite a while before you can do anything. It especially happens when someone has a record open or is editing and I have verified that it happens to all users at the same time. The BE is stored on a network share and each user has their own copy of the FE. Anything I can do to speed up the performance ?