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?
 
Rory ArchibaldConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
 
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
 
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
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.