Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is possibly a bit of a stab in the dark, but I am hoping that there is a simple solution that I am missing:

    My knowledge is limited in this area, so I'll give as much background as I can.

    We use a browser based application which is kind of like a lookup table. We enter an account number into the browser field, submit, and a new screen is populated with more information linked to that account. I am assuming that behind the browser, there must be an access database. I have no idea where this is located, I assume that this is designed to be hidden.

    This is fine for our general useage, but one of my colleagues had far more extensive needs to use this on a regular basis on multiple accounts. The following excel macro was written by another colleague (whom is no longer with us), to automatically pull data back to excel on a loop. This was installed on one users machine.

    Looking at the code, it looks to me that the browser is bypassed and the data is being pulled directly from a database?

    Anyhow, I copied the code to my machine, thinking that I would also be able to use the macro, but it seems not, (see screenshot)

    Code:
    Sub AccountChain()
     
    Dim CellRC, CellValue, CellResult As String
    Dim RowNum, HighRow, HomeRC As Integer
    Dim oQT As QueryTable
    Dim sConn As String
     
    HighRow = ActiveWorkbook.ActiveSheet.Cells(1, 1).SpecialCells(xlLastCell).Row
     
    sConn = "ODBC;DSN=AccountChain;"
     
    For RowNum = 1 To HighRow Step 1
    	CellRC = "F" & RowNum
    	Range(CellRC).Select
    	If ActiveCell.Value = "" Then
    		Exit For
    	End If
    	   
    	CellResult = "E" & RowNum
    	
    	sSql = "SELECT B2kAccno FROM AccountChain WHERE Root in (SELECT Root FROM AccountChain WHERE Accno = '" & Right(ActiveCell.Value, 16) & "') and TransferDate = '99999999'"
    	
    	Set oQT = ActiveWorkbook.ActiveSheet.QueryTables.Add( _
    		Connection:=sConn, _
    		Destination:=Range(CellResult), _
    		Sql:=sSql)
    	
    	oQT.FieldNames = False
    	oQT.BackgroundQuery = False
    	oQT.AdjustColumnWidth = False
    	' oQT.MaintainConnection = True
    	oQT.RefreshStyle = xlOverwriteCells
    		
    	oQT.Refresh
    	
    Next RowNum
     
    CellRC = "E1"
    Range(CellRC).Select
     
    End Sub
    [attachment=84249ataSource.GIF]

    Can I find out (from my colleagues machine) where the data source is? If so, how, and what do I need to do to create the link in order to use the macro on my machine?
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Open the ODBC Data Sources control panel on your former colleague's PC, and look for the AccountChain data source. It could be in either of the User DSN, Machine DSN or File DSN tabs. When you find it, select it and click Configure to see details about it.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Aha, so there is hope, Thanks. one question, as I am in unfarmiliar territory.

    <Open the ODBC Data Sources control panel on your former colleague's PC,

    Is this the dialogue in my screenshot? On my PC, this opens automatically when i attempt to run the code, but how do I get to it on my colleagues?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    No, the dialog in your screen shot is a request to select a DSN.

    In Vista:
    - Select Start | Control Panel.
    - If you're using the regular categorized view (not the classic view), open "Administrative Tools".
    - Open "Data Sources (ODBC)".

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thankyou Hans.

    At work I am on XP, not vista, but I am sure I'll find it in the control panel. I'll take a look tomorrow and report back. Cheers.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In XP:
    - Select Start | Control Panel.
    - If you're using category view, click "Other Control Panel Options" in the task pane on the left hand side.
    - Open Data Sources (ODBC).

Posting Permissions

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