?
Solved

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

Posted on 2007-10-19
3
Medium Priority
?
231 Views
Last Modified: 2013-12-26
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
0
Comment
Question by:samandrew
  • 2
3 Comments
 
LVL 11

Expert Comment

by:TreyH
ID: 20108325
Yes you can, as long as you keep the connection open. Depending upon your code, you may need one or more recordset objects.
0
 

Author Comment

by:samandrew
ID: 20109886
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
 
LVL 11

Accepted Solution

by:
TreyH earned 500 total points
ID: 20110049
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses

864 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