Database problem - please help

HI,

I need to have multiple connections to a database. Is that possible? Perhaps through ODBC???, or some easier way?
What I need to do is to have multiple connections to the database.

Possible? How?

Thank you
romkaAsked:
Who is Participating?
 
lalithawConnect With a Mentor Commented:
Normally inMSAccess when u do the updation then it will lock whole database.So if u want to do concuurrent transaction I mean updation then use adOpenKeySet and adLockOptimistic.In adLockOptimistic system will lock the database only when u call the update method.It will not lock the record as soon as u open the recordset.But dont forget to close the recordset after u finished the transaction.
If u r not doing updations then use the adOpenForwardOnly, adLockReadOnly.Then it will be ok.

Lalitha.

0
 
Éric MoreauSenior .Net ConsultantCommented:
what do you want to do.

you should not use ODBC if you can. this was an old method of accessind data that is now replaced with OLE DB (ado)
0
 
Julian_KCommented:
Hi, romka.

It is possible and very easy.
Just create them and use them:

Dim CNN as ADODB.Connection
Set CNN = new ADODB.Connection
CNN.Open strConnectionString
...
ect.

Just include a reference in your project to Microsoft ActiveX Data Objects 2.x (might be 2.4, 2.5, 2.6... ) depending on what you have installed as Service Packs and updates, software, ect.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
romkaAuthor Commented:
to Julian_K
yea - that's how i open it in the first place, but if another user open the same database - there's a problem...

so how else can i do it?
0
 
Éric MoreauSenior .Net ConsultantCommented:
What is the problem? If you don't open a connection exclusively, you won't have problems.

Can you show your code?
0
 
romkaAuthor Commented:
Here's the code that controls database operations :




'Database Functions
Public C_DB As ADODB.Connection   'declares a connection variable
Public C_RS As ADODB.Recordset    'declares a useable recordset
Public OpenTable As String        'Name of the currently open table

'Open's the database
'Which database is passed down
'through DBPath variable
Public Sub OpenDB(DBPath As String)
On Error GoTo ErrorHandler
Set C_DB = New ADODB.Connection   'makes sure we now open the new connection

'initialize the connection string
C_DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath
'open the database
C_DB.Open

Set C_RS = New ADODB.Recordset    'makes sure we now use the new recordset


ErrorHandler:
Select Case Err
    Case 0
    Case -2147217843
        MsgBox "The main database was not found", vbCritical
        End
    Case Else
        MsgBox "Error: " & Err & vbNewLine & Error
        'Clipboard.SetText Err
End Select
End Sub

'Closes the database so that
'no resources are wasted. This is
'because VB doesnt close all files
'when program quits
Public Sub CloseDB()
'Close and remove the recordset
 If C_RS.State <> adStateClosed Then
   C_RS.Close
 End If
 Set C_RS = Nothing
 
 'Close and remove the connection
 'to the database
 If C_DB.State <> adStateClosed Then
   C_DB.Close
 End If
 Set C_DB = Nothing
End Sub

Public Sub FillCodes()
ChooseTable ("Codes")
    For Counter = 1 To C_RS.RecordCount
        IDCode(Counter) = C_RS.Fields("Code")
        C_RS.MoveNext
    Next
End Sub

Public Sub FillClearances()
ChooseTable ("Workers")
    For Counter = 1 To C_RS.RecordCount
        IDClearance(Counter) = C_RS!Clearance
        If C_RS.AbsolutePosition = C_RS.RecordCount Then Exit Sub
        C_RS.MoveNext
    Next
End Sub

'Select a table to work on
Public Sub ChooseTable(Name As String)
Set C_RS = Nothing
Set C_RS = New ADODB.Recordset
C_RS.Open "Select * From " & Name, C_DB, adOpenStatic, adLockOptimistic
OpenTable = Name
End Sub

Public Function getValue(TableName, FieldName As String)
C_RS.Open "Select * From " & TableName, C_DB, adOpenStatic, adLockOptimistic
getValue = C_RS.Fields(FieldName)
End Function

Public Sub ChangePosition(Action As String)
On Error GoTo ErrorHandler
    With C_RS
Select Case Action
    Case "First"
            If .EditMode = True Then
            Else
                .MoveFirst
            End If
           
    Case "Previous"
        If .AbsolutePosition = 1 Then
            Else
                If .EditMode = True Then
                Else
                    If .EOF Or .BOF Then
                        MsgBox "eof: " & .EOF & ", bof: " & .BOF
                        .MoveFirst
                    Else
                        .MovePrevious
                    End If
                End If
        End If
       
    Case "Next"
        If .AbsolutePosition <> .RecordCount Then
            If .EditMode = True Then
            Else
                .MoveNext
            End If
        End If
       
    Case "Last"
        If .EditMode = True Then
            Else
                .MoveLast
        End If
       
    Case "Delete"
        If .EditMode = False Then
                .Delete
                .MoveNext
            If .EOF = True Then .MoveLast
        Else
                MsgBox "Must finish updating the record before deleting"
        End If
       
    Case "Add"
        If .EditMode = True Then
        Else
            .Update
            .AddNew
        End If
       

End Select
End With
ErrorHandler:
Select Case Err
    Case 0
   
    Case Else
        MsgBox "Error: " & Err & vbNewLine & Error
        'Clipboard.SetText Err
       
End Select
Exit Sub
End Sub
0
 
romkaAuthor Commented:
the problem is that i need to be at two places at the same time - well kind of.

the program is the server side with a database that it has access to. the client can browse and edit that information. the problem is that when two clients connect - the database needs to be able to have multiple connections to it

please help
0
 
lalithawCommented:

U dont need to put lock type as adLockOptimistic if u r using static cursors.Normally static cursors are using for client side recordsets(Disconnected recordsets).Use like this

C_RS.Open "Select * From " & Name, C_DB, adOpenForwardOnly, adLockReadOnly

This will work and this will not wasting ur resorces if u donot need to update the recordset.

Lalitha.



0
 
romkaAuthor Commented:
would that allow me to have multiple connections to the database? i mean to be at multiple recordset locations with the one database at the same time?
thanx
0
 
Éric MoreauSenior .Net ConsultantCommented:
therare no problem opening multiple recordset from a single database. the problems you can have (and you need to trap errors for that) are when users try to update the same record at the same time, when a user try to update a record that is already deleted, ...
0
 
Éric MoreauSenior .Net ConsultantCommented:
Any progress?
0
 
romkaAuthor Commented:
not really - i've decided to open the recordsets in arrays - havent implemented it as yet though
0
 
Éric MoreauSenior .Net ConsultantCommented:
This question appears to be abandoned. A question regarding it will be left in the CleanUp
area; if you have any comment about the question, please leave it here.

Unless there is objection or further activity, one of the moderators will be asked to accept the comment
of <emoreau>.

DO NOT ACCEPT THIS COMMENT AS AN ANSWER.
0
 
NetminderCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.