Excel connections odbc dsn

I have a spreadsheet that has a number of odbc dsn connections to queries in access 2010. I have jan to dec with 5 queries per month. I have got to a new connection number 39 in total and excel says cannot open anny more databases. not sure how to get around this problem. I chose excel as its graphs are just so much better
PeterBaileyUkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Do you need to maintain live connections for all queries and all months?
0
PeterBaileyUkAuthor Commented:
the data keeps changing monthly hence why i did it that way
0
Rory ArchibaldCommented:
So the old data changes too?
I would probably suggest not using live queries but having code extract the data from the database and drop it into the worksheet(s) directly. That way you do not need to maintain any workbook connections. Would that work for you?
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Rory ArchibaldCommented:
Alternatively, do you really need a separate query for each month, rather than 5 query types, each of which returns all months? It's generally easier to have all similar data together and then report on it using Filters/Pivots etc. though obviously your particular situation may require it.
0
PeterBaileyUkAuthor Commented:
is there a way of grabbing the data without connections?
vba for example but without having multiple instances running in the background?

0
Rory ArchibaldCommented:
Yes, you can use ADO or DAO to get it. For example - ado:

Sub GetAccessData()
 Sample demonstrating how to return a recordset from an Access db
 requires a reference to the Microsoft ActiveX Data Objects Library.

   Dim cnn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset
   Dim strPathToDB As String
   Dim i As Long
   Dim wks               As Worksheet
   Dim lngNextNum As Long, lngRow As Long, lngCol As Long
   Dim varData


    output to activesheet
   Set wks = ActiveSheet

    Path to database
   strPathToDB = "h:\test\db1.accdb"

   Set cnn = New ADODB.Connection
    open connection to database
   With cnn
      .CursorLocation = adUseServer
      .ConnectionTimeout = 500
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
      .CommandTimeout = 500
   End With
    SQL query string - change to suit
   strQuery = "SELECT * FROM query_name;"
   ' Create New Recordset
      Set rst = New ADODB.Recordset
   
      ' open recordset using query string and connection
      With rst
         .CursorLocation = adUseServer
         .Open strQuery, cnn, adOpenStatic, adLockPessimistic, adCmdText
         ' check for records returned
         If Not (.EOF And .BOF) Then
            'Populate field names
            For i = 1 To .Fields.Count
               wks.Cells(1, i) = .Fields(i - 1).Name
            Next i
            ' Copy data from A2
            wks.Cells(2, 1).CopyFromRecordset rst
         End If
         .Close
      End With
   
      ' clean up
      Set rst = Nothing
   cnn.Close
   Set cnn = Nothing
End Sub

Open in new window

0
PeterBaileyUkAuthor Commented:
silly question but how do i insert a vba module in 2010 excel? cannot see for looking?
0
PeterBaileyUkAuthor Commented:
ok found it!
0
PeterBaileyUkAuthor Commented:
I wish to populate 1 cell as the query returns a total  my first cell is c5 how do i amend the code to reflect that?

 If Not (.EOF And .BOF) Then
            'Populate field names
            For i = 1 To .Fields.Count
               wks.Cells(1, i) = .Fields(i - 1).Name
            Next i
            ' Copy data from A2
            wks.Cells(2, 1).CopyFromRecordset rst
         End If
0
Rory ArchibaldCommented:
If you only get one value back then:
 If Not (.EOF And .BOF) Then
            
            wks.Cells(5, "C").Value =  rst(0)
         End If

Open in new window

0
PeterBaileyUkAuthor Commented:
how do i invoke the call?

do i make the cell = getAccessdata()
Sub GetAccessData()
 Sample demonstrating how to return a recordset from an Access db
 requires a reference to the Microsoft ActiveX Data Objects Library.

   Dim cnn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset
   Dim strPathToDB As String
   Dim i As Long
   Dim wks               As Worksheet
   Dim lngNextNum As Long, lngRow As Long, lngCol As Long
   Dim varData


    output to activesheet
   Set wks = "2010 Datafeed"

    Path to database
   strPathToDB = "n:\data\smmt\SMMTMatchStatsDB.accdb"

   Set cnn = New ADODB.Connection
    open connection to database
   With cnn
      .CursorLocation = adUseServer
      .ConnectionTimeout = 500
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
      .CommandTimeout = 500
   End With
    SQL query string - change to suit
   strQuery = "SELECT Count(QryFeb2010Raw.[New Code Book 1002].[Model Variant Code]) AS [CountOfNew Code Book 1002_Model Variant Code] FROM QryFeb2010Raw;"


   ' Create New Recordset
      Set rst = New ADODB.Recordset
   
      ' open recordset using query string and connection
      With rst
         .CursorLocation = adUseServer
         .Open strQuery, cnn, adOpenStatic, adLockPessimistic, adCmdText
         ' check for records returned
    If Not (.EOF And .BOF) Then
            
            wks.Cells(5, "C").Value = rst(0)
         End If
         .Close
      End With
   
      ' clean up
      Set rst = Nothing
   cnn.Close
   Set cnn = Nothing
End Sub

Open in new window

0
Rory ArchibaldCommented:
No - you just run that macro. BTW your line 14 should be:
   Set wks = Sheets("2010 Datafeed")

Open in new window

and I have just noticed that a lot of comment lines seem to have had their comment prefixes removed in my original post. The code should be:
Sub GetAccessData()
' Sample demonstrating how to return a recordset from an Access db
' requires a reference to the Microsoft ActiveX Data Objects Library.

   Dim cnn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset
   Dim strPathToDB As String
   Dim i As Long
   Dim wks               As Worksheet
   Dim lngNextNum As Long, lngRow As Long, lngCol As Long
   Dim varData


   ' output to activesheet
   Set wks = Sheets("2010 Datafeed")

   ' Path to database
   strPathToDB = "n:\data\smmt\SMMTMatchStatsDB.accdb"

   Set cnn = New ADODB.Connection
   ' open connection to database
   With cnn
      .CursorLocation = adUseServer
      .ConnectionTimeout = 500
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
      .CommandTimeout = 500
   End With
   ' SQL query string - change to suit
   strQuery = "SELECT Count(QryFeb2010Raw.[New Code Book 1002].[Model Variant Code]) AS [CountOfNew Code Book 1002_Model Variant Code] FROM QryFeb2010Raw;"


   ' Create New Recordset
      Set rst = New ADODB.Recordset
   
      ' open recordset using query string and connection
      With rst
         .CursorLocation = adUseServer
         .Open strQuery, cnn, adOpenStatic, adLockPessimistic, adCmdText
         ' check for records returned
    If Not (.EOF And .BOF) Then
            
            wks.Cells(5, "C").Value = rst(0)
         End If
         .Close
      End With
   
      ' clean up
      Set rst = Nothing
   cnn.Close
   Set cnn = Nothing
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
I selected run in the vba screen and it has a compile error  Dim cnn As ADODB.Connection user defined type not defined.
0
Rory ArchibaldCommented:
Per the comments in the code, you need to set a reference (Tools-References in the VB Editor) to the Microsoft ActiveX data objects 2.8 library (I am guessing the version you have - it will be 2.something )
0
PeterBaileyUkAuthor Commented:
Ok that worked now as I have to fill in other cells do i just repeat part of the code and maintain the same connection with different query?
0
Rory ArchibaldCommented:
Yes - just close the recordset, then open it again using another query and output that value. Then close the connection at the end when you have finished.
0
PeterBaileyUkAuthor Commented:
is it possible to refer to the query itself as opposed to an sql string? to save rework should the query change?
0
Rory ArchibaldCommented:
Yes - use:
strQuery = "[queryname]"

Open in new window

and change adcmdtext to adcmdtable in the line that opens the recordset.
0
PeterBaileyUkAuthor Commented:
very good solution thx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.