Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access into XL (Office 2k)

    Hello,

    I find the crosstabs in Access sort of not user-friendly. <img src=/S/tongue.gif border=0 alt=tongue width=15 height=15> I'd like to establish a connection to a user-level secured database, run SQL and import the data into specified workbook, where I have already pivot tables "waiting" for the updated data.

    I'd be grateful for suggestions how to make this in a most efficient way. I am (kind of) familiar with VBA and SQL, just need a hint.

    Thank you very much
    Martin
    Regards,
    Martin

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access into XL (Office 2k)

    AFAIK you can base a pivot table on external data (including a query) directly, no need for importing any data into a range. But I am unexperienced with this.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access into XL (Office 2k)

    Thank you very much for your reply.
    Yes, I tried this, however I am encountering problems when connecting to user-level secured database. (my system file is named MIS.mdw).
    And last but not least at all, I would like to learn how to build up the appropriate connection string, create a SQL on-the-fly (e.g. based on the cells in the worksheet) and import the data.
    Thanks again
    Regards,
    Martin

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access into XL (Office 2k)

    You should be receiving a prompt when you attempt to retrieve the data from Access - if not, then something is amiss with the manner in which you are attempting to connect to the database. The simplest method would be to use ADO - there is a well documented method for connecting to an Access database. Check the reference for ADO in <post#=336069>post 336069</post#>. There are also other ways to do what you suggest. One would be to export the data from Access to Excel, and then bring in the worksheet with the pivot tables. Another would be to use Automation to run the query and then actually write the contents to Excel. Finally, if you were using Access 2002, it has a pivot table feature that is comparable to that in Excel, so you could do your analysis work directly in Access. Hope this gives you some ideas and a nudge.
    Wendell

  5. #5
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access into XL (Office 2k)

    Thank you very much for your reply. I'll give it a try and dare to be back in case of troubles.
    I didn't know that one about Access2002: Does it allow e.g. multiple column headers? I can't believe that...
    Regards,
    Martin

  6. #6
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access into XL (Office 2k)

    Just read briefly the links - actually I am quite familiar with ADO, never used Access as Automation server though.
    I can't find out the proper way how to connect to the db that is user-level secured. Creating SQL and writing the recordset into XL shouldn't be a big problem then (I hope)
    Thanks again
    Regards,
    Martin

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access into XL (Office 2k)

    In 2002 ADO help the Open Method for an ADO Connection looks like:
    <font color=blue>Syntax

    connection.Open ConnectionString, UserID, Password, Options</font color=blue>

    I presume 2000 ADO help (found in the VBA editor) shows the same info, and has the details.
    Wendell

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access into XL (Office 2k)

    As I understand it, Office XP uses the Microsoft OFfice Web Components to allow you to view data as a pivot table, so Excel and Access are using the same component. If you want to read more about the fundamentals, see <!mskb=298764>Microsoft Knowledge Base Article 298764<!/mskb>.
    Wendell

  9. #9
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access into XL (Office 2k)

    Thank you for the answer.
    Unfortunately the help item "Connection Object (ADO)" in my Help woun't open, either I do not have the whole help file installed or something's wrong with it.

    I understand the connection syntax, but could you please give me an example of the connection string?
    Say, I have C:test.mdb, C:test.mdw, User: Martin, Password: King, Access2000

    Thank you so much
    Martin
    Regards,
    Martin

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access into XL (Office 2k)

    Sorry for the delay in responding - things got just a bit busy the last few days. Anyhow, this is an example from the ADO help file that shows both opening and closing a connection to a Microsoft Access (Jet) database in a couple of different scenarious:

    Connection Close Method, Table Type Property Example (VB)

    Setting the ActiveConnection property to Nothing should "close" the catalog. Associated collections will be empty. Any objects that were created from schema objects in the catalog will be orphaned. Any properties on those objects that have been cached will still be available, but attempting to read properties that require a call to the provider will fail.
    <font face="Georgia"><pre><font color=blue>
    Sub CloseConnectionByNothing()

    Dim cnn As New Connection
    Dim cat As New Catalog
    Dim tbl As Table

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= c:Program FilesMicrosoft Office" & _
    "OfficeSamplesNorthwind.mdb;"
    Set cat.ActiveConnection = cnn
    Set tbl = cat.Tables(0)
    Debug.Print tbl.Type ' Cache tbl.Type info
    Set cat.ActiveConnection = Nothing
    Debug.Print tbl.Type ' tbl is orphaned
    ' Previous line will succeed if this was cached
    Debug.Print tbl.Columns(0).DefinedSize
    ' Previous line will fail if this info has not been cached

    End Sub</pre>

    </font face=georgia></font color=blue>

    Closing a Connection object that was used to "open" the catalog should have the same effect as setting the ActiveConnection property to Nothing.
    <font face="Georgia"><pre><font color=blue>
    Sub CloseConnection()

    Dim cnn As New Connection
    Dim cat As New Catalog
    Dim tbl As Table

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source= c:Program FilesMicrosoft Office" & _
    "OfficeSamplesNorthwind.mdb;"
    Set cat.ActiveConnection = cnn
    Set tbl = cat.Tables(0)
    Debug.Print tbl.Type ' Cache tbl.Type info
    cnn.Close
    Debug.Print tbl.Type ' tbl is orphaned
    ' Previous line will succeed if this was cached
    Debug.Print tbl.Columns(0).DefinedSize
    ' Previous line will fail if this info has not been cached

    End Sub
    </pre>

    </font face=georgia></font color=blue>
    Wendell

  11. #11
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access into XL (Office 2k)

    Hello Wendell, thank you very much for your reply.
    In the meantime, I found a method how to connect to an user-level secured database, build a SQL on the fly and import the result into Excel. The SQL is a trivial one here. the real one will be a parameter SQL also built on-the-fly (probably). I'd be grateful if you could comment:
    <pre>Sub Import()
    Dim Cn As Object, Rs As Object, myCalls As String
    Dim mySql As String, dbFullname As String, myCnt As Long

    Set tgtSh = Workbooks("Frequency.xls").Sheets(4)
    mySql = "SELECT * FROM tblPersons;" 'Stack SQL string

    myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=CataAccess2000MISMIS.mdb;" & _
    "Jet OLEDB:System database=CataAccess2000MIS.mdw;" & _
    "User ID=King;Password=Martin;"

    Set Cn = CreateObject("ADODB.Connection")
    Cn.Open myConnString 'Create DB connection

    Set Rs = CreateObject("ADODB.Recordset")
    With Rs
    Set .ActiveConnection = Cn
    .Source = mySql 'Pass your SQL
    .Open , , 3, 3 '.Open , , adOpenStatic, adLockOptimistic
    myCnt = .RecordCount
    If myCnt > 0 Then
    .MoveLast: .MoveFirst
    'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
    3 fields in the sql pass
    tgtSh.Range(Cells(1, 1), Cells(myCnt, 3)).CopyFromRecordset Rs
    End If
    .Close
    End With
    Cn.Close
    Set Rs = Nothing: Set Cn = Nothing
    End Sub</pre>

    Regards,
    Martin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •