Allowing Multiple people

[manofsteel00]

I'm developing an application that interfaces with a database to allow multiple users to select multiple items from an Access database using ADO DB. However, whenever there are multiple users I get a "Run-time Error 339: A File is missing or is invalid". This error happens whenever a form loads and the code for that for is:

'*FORM_LOAD()******************************************************************
'NAME: Form_Load()
'DESC: Initially loads the listbox (lstFirstScreen) with the available data
'      from the database
Private Sub Form_Load()
    cmdSelect.Enabled = False
    lblTopTabs.Caption = "PICKSL" & "     " _
                       & " DFC" & "           " _
                       & "PROD" & "        " _
                       & "DESC"
    
    cmdPrint.Visible = False
    cmdPrint.Enabled = False
    
    Set connection = New ADODB.connection
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= G:\Finance\Inventory Control\CycleCount\DB\cycleCount.mdb"
    connection.Open
    
    Set newConnection = New ADODB.connection
    newConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= G:\Finance\Inventory Control\CycleCount\DB\cycleCount.mdb"
    newConnection.Open
    
    Set recordSet = New ADODB.recordSet
    Set newRecordSet = New ADODB.recordSet
      
    Dim inAlterTable As Boolean
    Dim selectStatement As String
    Dim newSelectStatement As String
    
    selectStatement = "SELECT DISTINCT PICKSL,DFP,PROD,DESC FROM [MAIN]"
    newSelectStatement = "SELECT * FROM [ALTER] WHERE TYPEOFCHANGE='SELECTED'"
        
    recordSet.Open selectStatement, connection, adOpenKeyset, adLockPessimistic, adCmdText
    newRecordSet.Open newSelectStatement, newConnection, adOpenKeyset, adLockPessimistic, adCmdText
    
    recordSet.MoveFirst
            
    Do Until recordSet.EOF
        If (newRecordSet.EOF <> True) Then
            newRecordSet.MoveFirst
        End If
        inAlterTable = False
        Do Until newRecordSet.EOF
            If (recordSet.Fields("PICKSL") = newRecordSet.Fields("PICKSL") And _
                recordSet.Fields("DFP") = newRecordSet.Fields("DFP") And _
                recordSet.Fields("PROD") = newRecordSet.Fields("PROD") And _
                recordSet.Fields("DESC") = newRecordSet.Fields("DESC")) Then
                inAlterTable = True
            End If
            DoEvents
            newRecordSet.MoveNext
        Loop
 
        If (inAlterTable = False) Then
            lstFirstScreen.AddItem (recordSet.Fields("PICKSL") & vbTab _
                & recordSet.Fields("DFP") & vbTab _
                & recordSet.Fields("PROD") & vbTab _
                & recordSet.Fields("Desc"))
        End If
        DoEvents
               
        recordSet.MoveNext
    Loop
    
    recordSet.Close
    connection.Close
    Set recordSet = Nothing
    Set connection = Nothing
    
    If (lstFirstScreen.ListCount > 0) Then
        cmdSelect.Enabled = True
    End If
    
End Sub
'*ENDOF*FORM_LOAD()************************************************************

I do have a pessimistic lock on the database; could that be what is causing it. And if so I can I keep users from getting the same data at the same time? Any help you be greatly appreciated.

[Hack]

A couple of questions:

whenever there are multiple users I get a "Run-time Error 339: A File is missing or is invalid".

Does this mean that you do not get this error if only one person is using it? Two? Three? Have you noticed any consistency between the number of users and the occurrance of this error?

Do you know on what line of code this error is being generated from?

[si_the_geek]

You only need one connection, so remove one of those, and use the same for both recordsets. (it may be causing the problem)

Next up, why is there no "Exit Do" after the "inAlterTable = True" line? (you currently loop when there is no need!). You should then run "newRecordSet.MoveFirst" every time.

Also, you do not need two recordsets for this - just use one with SQL that joins the tables appropriately, eg:

SELECT DISTINCT m.PICKSL, m.DFP, m.PROD, m.DESC 
FROM [MAIN] m 
LEFT JOIN [ALTER] a
ON (a.PICKSL = m.PICKSL AND a.DFP = m.DFP AND a.PROD = m.PROD AND a.DESC = m.DESC)
WHERE (a.TYPEOFCHANGE <> 'SELECTED') 
OR (a.PICKSL Is Null)

edit: oops, this should be the SQL I think:

SELECT DISTINCT m.PICKSL, m.DFP, m.PROD, m.DESC 
FROM [MAIN] m 
WHERE Not Exists(
  SELECT * 
  FROM [ALTER] a 
  WHERE a.PICKSL = m.PICKSL 
  AND a.DFP = m.DFP 
  AND a.PROD = m.PROD 
  AND a.DESC = m.DESC
  )

[manofsteel00]

I only get the error when two or more people are accessing the database.

[Hack]

I only get the error when two or more people are accessing the database.

Did you make the changes suggested by si_the_geek?

[manofsteel00]

Made the changes, but haven't been able to test it just yet. One quick question though: Would the SQL statement be the same if I wanted to connect to a table in another database or would I need to create another connect? Thank you in advance.

[si_the_geek]

It depends on what the DBMS's for each of the databases are (Access/SQL Server/etc).

If they are both the same, there is usually a way (there are ways I know of for Access and SQL Server).

[manofsteel00]

All the databases I connect to are Access 2002, so would I need to create two connections to update data from one database to another database?

[si_the_geek]

Here's how to reference tables from other databases (with or without database passwords):
http://www.vbforums.com/showthread.php?t=382366

You can use the same syntax as shown there to join a table in one database to a table in another.

[manofsteel00]

Is there a way to check to see if a database is locked? I think my problem is that the database is locked.

[dee-u]

A database would only be locked if it was opened exclusively, and if such is the case then you wouldn't even be able to connect to it is someone else has opened it exclusively, base on your connectionstring you are not opening it exclusively hence it is not being locked...