ado multiple connections????????????????

Hi

Can anyone tell me please the following.
I have a ado connection on my SSTab(which has 7 tabs) and using a sql statement I search a database and return values based on 7 days previous records.  What I need to know is

Can i use the same connection to run multiple sql queries and return different values from my database into an array of textbos over different tabs

If so could someone please show me an example.

Many Thanks
Andy
samandrewAsked:
Who is Participating?
 
TreyHConnect With a Mentor Commented:
Private Sub ResetDates()
     
     Dim i As Long
   Dim datSunday As Date
   datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
   For i = 0 To txtvbmonday.UBound
      txtvbmonday(i).Text = Format$(datSunday + i, "dd-mmm-yyyy")
      txtproductioncode(i).Text = Format$(5 + datSunday + i, "dd-mmm-yyyy")
      txtprecode(i).Text = Format$(6 + datSunday + i, "dd-mmm-yyyy")
   Next
   
   Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim mysql As String
   
    Const cMDB = "C:\Documents and Settings\andy\Desktop\Production\Planner.accdb"

    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\andy\Desktop\Production\Planner.accdb;Persist Security Info=False;"

      mysql = "#" & Format(datSunday, "yyyy-mm-dd") & "#"

    mysql _
        = " SELECT Atherstone, Chelmsford, Darlington, Middleton, Neston, Swindon" _
        & " FROM Actualorders " _
        & " WHERE Actualorders.[Date] Between" & mysql & "-7 And " & mysql

   
    With rs
       .LockType = 1
       .CursorType = 3
       .Open mysql, cn
 
       If .RecordCount > 0 Then
            Me("txtatherstone").Text = .Fields("Atherstone")
            Me("txtchelmsford").Text = .Fields("Chelmsford")
            Me("txtdarlington").Text = .Fields("Darlington")
            Me("txtmiddleton").Text = .Fields("Middleton")
            Me("txtneston").Text = .Fields("Neston")
            Me("txtswindon").Text = .Fields("swindon")
           
        End If
        .Close
    End With

    '************************************************************
    'You can simply create a different select statement and re-open the recordset here.
    'For example:

    mysql = "SELECT Atherstone, Chelmsford, Darlington, Middleton, Neston, Swindon" _
        & " FROM Actualorders WHERE Actualorders.[Date] = #" & Now() & "#"

    With rs
       .LockType = 1
       .CursorType = 3
       .Open mysql, cn
 
       If .RecordCount > 0 Then
            Me("txtTestBox").Text = .Fields("Atherstone")
           
        End If
        .Close
    End With
    '*************************************************************
   
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
   
End Sub
0
 
TreyHCommented:
Yes you can, as long as you keep the connection open. Depending upon your code, you may need one or more recordset objects.
0
 
samandrewAuthor Commented:
Hi This is my code can you please give an example.

Private Sub ResetDates()
     
     Dim i As Long
   Dim datSunday As Date
   datSunday = DateAdd("d", vbSunday - Weekday(Date), Date)
   For i = 0 To txtvbmonday.UBound
      txtvbmonday(i).Text = Format$(datSunday + i, "dd-mmm-yyyy")
      txtproductioncode(i).Text = Format$(5 + datSunday + i, "dd-mmm-yyyy")
      txtprecode(i).Text = Format$(6 + datSunday + i, "dd-mmm-yyyy")
   Next
   
   Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim mysql As String
   
    Const cMDB = "C:\Documents and Settings\andy\Desktop\Production\Planner.accdb"

    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\andy\Desktop\Production\Planner.accdb;Persist Security Info=False;"

      mysql = "#" & Format(datSunday, "yyyy-mm-dd") & "#"

    mysql _
        = " SELECT Atherstone, Chelmsford, Darlington, Middleton, Neston, Swindon" _
        & " FROM Actualorders " _
        & " WHERE Actualorders.[Date] Between" & mysql & "-7 And " & mysql

   
    With rs
       .LockType = 1
       .CursorType = 3
       .Open mysql, cn
 
       If .RecordCount > 0 Then
            Me("txtatherstone").Text = .Fields("Atherstone")
            Me("txtchelmsford").Text = .Fields("Chelmsford")
            Me("txtdarlington").Text = .Fields("Darlington")
            Me("txtmiddleton").Text = .Fields("Middleton")
            Me("txtneston").Text = .Fields("Neston")
            Me("txtswindon").Text = .Fields("swindon")
           
        End If
        .Close
    End With
   
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
   
End Sub

Many Thanks
Andy
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.