Solved

Database problem - please help

Posted on 2002-06-08
14
162 Views
Last Modified: 2010-05-02
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
0
Comment
Question by:romka
  • 5
  • 5
  • 2
  • +2
14 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7064632
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
 
LVL 5

Expert Comment

by:Julian_K
ID: 7065117
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
 

Author Comment

by:romka
ID: 7065289
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7065324
What is the problem? If you don't open a connection exclusively, you won't have problems.

Can you show your code?
0
 

Author Comment

by:romka
ID: 7066371
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
 

Author Comment

by:romka
ID: 7066375
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
 
LVL 1

Expert Comment

by:lalithaw
ID: 7066485

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:romka
ID: 7068822
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7068835
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
 
LVL 1

Accepted Solution

by:
lalithaw earned 50 total points
ID: 7068927
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7128852
Any progress?
0
 

Author Comment

by:romka
ID: 7140846
not really - i've decided to open the recordsets in arrays - havent implemented it as yet though
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7348070
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
 
LVL 5

Expert Comment

by:Netminder
ID: 7373304
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now